Thread: Help in Parsing PG log usings CSV format

On Thu, 2012-03-22 at 09:32 +0000, Arvind Singh wrote:
> Help needed in parsing PostgreSQL CSV Log
> Hello friends,
> I am working an a section of application which needs to Parse CSV Logs generated by PostgreSql server.
> - The Logs are stored C:\Program Files\PostgreSQL\9.0\data\pg_log
> - The Server version in 9.0.4
> - The application is developed in C Sharp
>     * The basic utility after Parse the Log is to show contents in a DataGridView.
>     * There are other filter options like to view log contents for a particular range of Time for a Day.
>
> **However the main problem that is, the Log format is not readable**
>
>
> A Sample Log data line
> > 2012-03-21 11:59:20.640 IST,"postgres","stock_apals",3276,"localhost:1639",4f697540.ccc,10,"idle",2012-03-21
11:59:20IST,2/163,0,LOG,00000,"statement: SELECT version()",,,,,,,,"exec_simple_query,
.\src\backend\tcop\postgres.c:900",""
>
> As you can see the columns in the Log are comma separated , But however individual values  are not Quote Enclosed.
>
> For instance the 1st,4rth,6th .. columns
>
>
Thats fairly standard. A good CSV parser only requires a field to be
quoted if it contains commas or quotes. In the latter case the internal
quotes should be doubled, i.e the three fields in the following:

  unquoted field,"contains commas, etc.","Fred said ""Cool!""."

should be handled correctly by a decent CSV parser.


Martin

Arvind Singh wrote:
> Help needed in parsing PostgreSQL CSV Log

[...]

> **However the main problem that is, the Log format is not readable**

> A Sample Log data line
> > 2012-03-21 11:59:20.640
>
IST,"postgres","stock_apals",3276,"localhost:1639",4f697540.ccc,10,"idle
",2012-03-21 11:59:20
> IST,2/163,0,LOG,00000,"statement: SELECT
version()",,,,,,,,"exec_simple_query,
> .\src\backend\tcop\postgres.c:900",""
>
> As you can see the columns in the Log are comma separated , But
however individual values  are not
> Quote Enclosed.
>
> For instance the 1st,4rth,6th .. columns
>
> **Is there a setting in PostgreSQL configuration to place quotes
around all columns in a Logfili
>
> I just want to update the columns so that all are within Quotes
>
> what happens wrong is when it reaches the column where sql statement
is place. it also has commas set
> for table columns. The log line is a mix bunch of quote-enclosed and
non-quote-enclosed column. is
> there is a configuration or utility to convert the non-quoted column
to quoted column

The columns that are not quoted are guaranteed not to contain a comma.
So it shouldn't be a problem to parse them.

In fact, it is quite easy. As an example, see here:
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgreplay/pgreplay/parse.c?re
v=1.14&content-type=text/x-cvsweb-markup
In the function parse_csvlog_entry, after the comment
"read next line after start timestamp from log file"
you can find code that parses such a line.

The code is in C, so it should be easy to port it to C#, which
is essentially Java, which has C-like syntax.

Yours,
Laurenz Albe

Arvind Singh wrote:
> i have sorted out the problem on
> " The columns that are not quoted are guaranteed not to contain a
comma. "

Good.

> But i have another query, the structure of PG Log CSV as mentioned in
manual and as below has 24
> columns
> http://www.postgresql.org/docs/9.1/static/runtime-config-logging.html
> -------------------------------------------
>
>
>         log_time timestamp,
>           user_name text,
>           database_name text,
>           process_id integer,
>           connection_from text,
>           session_id text,
>           session_line_num bigint,
>           command_tag text,
>           session_start_time timestamp,
>           virtual_transaction_id text,
>           transaction_id bigint,
>           error_severity text,
>           sql_state_code text,
>           message text,
>           detail text,
>           hint text,
>           internal_query text,
>           internal_query_pos integer,
>           context text,
>           query text,
>           query_pos integer,
>           location text,
>           application_name text,
>           PRIMARY KEY (session_id, session_line_num)


Actually, that's only 23 columns. The last line is a constraint.

> However every Log line added contains only 22 columns, a sample log
line is given below
> -------------------------------------------
>
>     2012-03-22 15:29:53.546
>
IST,"postgres","stock_apals",2396,"localhost:2766",4f6af819.95c,9,"SELEC
T",2012-03-22 15:29:53
> IST,3/0,0,LOG,00000,"QUERY STATISTICS","! system usage stats:
>     ! 0.015000 elapsed 0.000000 user 0.015625 system sec
>     ! [0.078125 user 0.062500 sys total]",,,,,"SELECT
SUBSTR(itemname, 1, 15) as Product,
>     avg(rate) as Avgrate
>     FROM   issue_stock WHERE extract(year from issue_stock.edate) =
'2011'
>     GROUP BY itemname
>     order by itemname",,"ShowUsage,
.\src\backend\tcop\postgres.c:4305",""
>
>
> -------------------------------------------
>
> > the last column of the Log is not the Primarykey ?
> > the last column as of yet is unknown because at all occurances it is
a empty quoted column ?
> > the column numbers dont match with the generated log
>
> Is this is a different Log format , can you guide us to a page where
the column specifications can be
> matched.

You must be using PostgreSQL 8.4 or earlier.
The last column, "application_name", was added in 9.0.
Look up the documentation for your version and you'll find it.

Yours,
Laurenz Albe

On 2012-03-22, Martin Gregorie <martin@gregorie.org> wrote:

> Thats fairly standard. A good CSV parser only requires a field to be
> quoted if it contains commas or quotes.

quotes,commas, or linebreaks

copy ( values (2,'comma, etc'),(3,'and "quote".'),(1,'line
break') ) to stdout with csv;

--
⚂⚃ 100% natural