Re: Parsing PG log usings CSV format

[Arvind Singh <arvindps@xxxxxxxxxxx>]

Thank you sir,
 
i have sorted out the problem on
" The columns that are not quoted are guaranteed not to contain a comma. "
 
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)

-------------------------------------------

 
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,"SELECT",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.
 
Yrs truly
arvind pal singh
 

> Subject: RE: Help in Parsing PG log usings CSV format
> Date: Thu, 22 Mar 2012 16:47:11 +0100
> From: laurenz.albe@xxxxxxxxxx
> To: arvindps@xxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx
>
> 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