Re: Parsing PG log usings CSV format

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