Remote Log Reading in PostgreSQL 8.4 and 9.0.

I was going through the one of the important feature (SQL/MED) which is coming feature of PostgreSQL 9.1.

This feature enables user to access any external file, using SQL, from PostgreSQL Terminal.

Magnus hagander's has also blogged about this feature and he has shown "How to access the PostgreSQL logfile using SQL/Med".

After going through his blog, I thought to do same with PostgreSQL 8.4/9.0 using plperl program.

Following are the steps, which can be use to access the postgreSQL csv log file:
1. Change the log_destination parameter in postgresql.conf file of PG Instance Directory, to create csvlog of postgreSQL log. As given below.

log_destination = 'stderr,csvlog' 

2. Reload the changes in PostgreSQL, using following command:

postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

3. Connect to PostgreSQL database using psql command and Create following data type

CREATE type pg_log_type as ( log_time   timestamp(3) with time zone ,
 user_name   text ,
 database_name   text ,
 process_id   integer ,
 connection_from   text ,
 session_id   text ,
 session_line_num   text ,
 command_tag   text ,
 session_start_time   timestamp with time zone ,
 virtual_transaction_id   text ,
 transaction_id   text ,
 error_severity   text ,
 sql_state_code   text ,
 message   text ,
 detail   text ,
 hint   text ,
 internal_query   text ,
 internal_query_pos   text ,
 context   text ,
 query   text ,
 query_pos   text ,
 location   text ,
 extra   text
 );

4. Now, Create following plperl function to read the pg_log files:

CREATE OR REPLACE FUNCTION read_pg_log(text) returns setof pg_log_type as    
$$    
## Written by Vibhor to Read external file 
use strict;
use warnings;
use CSV;
my $file = $_[0];
my $badfile = ">>"."$_[0]".".bad";
my $csvdata = "";
my $numq = 0;
my $dquote = "\"";
open (CSV, "<", $file);
while () {
       my    $pos = -1;
    while (($pos = index($_, $dquote, $pos)) > -1) {
        $pos++; # step over the double quote
        $numq++;
    };

 $csvdata = $csvdata . $_; # linefeed preserved
            if ( ! ($numq % 2) ) {
           my @line = CSVsplit($csvdata);
           return_next({log_time => $line[0] , user_name => $line[1] , database_name => $line[2] , process_id => $line[3] , connection_from => $line[4] , session_id => $line[5] , session_line_num => $line[6] , command_tag => $line[7] , session_start_time => $line[8] , virtual_transaction_id => $line[9] , transaction_id => $line[10] , error_severity => $line[11] , sql_state_code => $line[12], message => $line[13], detail => $line[14], hint => $line[15], internal_query => $line[16] , internal_query_pos => $line[17] , context => $line[18], query => $line[19] , query_pos => $line[20] , location => $line[21] , extra => $line[22] });
 # reset trans-loop iterator variables
        $csvdata = "";
        $numq = 0;
    };
    }
close CSV; 
return undef;    
$$ language plperlu; 

Please note: Before using the above function, please make sure, you have CSV Module installed in perl.

Following are some snapshots of using the function:

postgres=# select log_time, log_time-lag(log_time,1) OVER () from read_pg_log('/Library/PostgresPlus/9.0SS/data/pg_log/postgresql-2011-05-23_221143.csv') limit 10 offset 10;
           log_time            |   ?column?   
-------------------------------+--------------
 2011-05-24 01:42:29.974+05:30 | 00:00:05.006
 2011-05-24 01:42:34.982+05:30 | 00:00:05.008
 2011-05-24 01:42:39.989+05:30 | 00:00:05.007
 2011-05-24 01:42:44.995+05:30 | 00:00:05.006
 2011-05-24 01:42:50.113+05:30 | 00:00:05.118
 2011-05-24 01:42:50.145+05:30 | 00:00:00.032
 2011-05-24 01:42:55.152+05:30 | 00:00:05.007
 2011-05-24 01:43:00.16+05:30  | 00:00:05.008
 2011-05-24 01:43:05.168+05:30 | 00:00:05.008
 2011-05-24 01:43:10.175+05:30 | 00:00:05.007
(10 rows)

To view all the error logged, following command can be use:

postgres=# select error_severity,message from read_pg_log('/Library/PostgresPlus/9.0SS/data/pg_log/postgresql-2011-05-23_221143.csv') where error_severity like '%ERROR%' limit 5
postgres-# ;
 error_severity |                message                 
----------------+----------------------------------------
 ERROR          | syntax error at or near "("
 ERROR          | syntax error at line 12, near "line ["+
                | syntax error at line 35, near "}      +
                | else"                                 +
                | syntax error at line 38, near "}      +
                | }"                                    +
                | syntax error at line 42, near ";      +
                |  }"
 ERROR          | syntax error at line 12, near "line ["+
                | syntax error at line 35, near "}      +
                | else"                                 +
                | syntax error at line 38, near "}      +
                | }"                                    +
                | syntax error at line 42, near ";      +
                |  }"
 ERROR          | syntax error at line 12, near "line[" +
                | syntax error at line 13, near "}      +
                | else"                                 +
                | syntax error at line 16, near "}      +
                | }"                                    +
                | syntax error at line 20, near ";      +
                |  }"
 ERROR          | syntax error at line 12, near "line[" +
                | syntax error at line 13, near "}      +
                | else"                                 +
                | syntax error at line 16, near "}      +
                | }"                                    +
                | syntax error at line 20, near ";      +
                |  }"