Thread: PG Log

Arvind Singh wrote:
> I have queries regarding columns in Postgres CSV Log.
>
> Following is a sample Logline
>
------------------------------------------------------------------------
> 2012-03-28 19:25:47.968
IST,"postgres","stock_apals",2388,"localhost:1898",4f731863.954,6,"SET",
2012-
> 03-28 19:25:47 IST,2/0,0,LOG,00000,"QUERY STATISTICS","! system usage
stats:
> ! 0.047000 elapsed 0.000000 user 0.000000 system sec
> ! [0.078125 user 0.031250 sys total]",,,,,"Select * from stock_apals"
> ,,"ShowUsage, .\src\backend\tcop\postgres.c:4305",""
>
------------------------------------------------------------------------
>
> I am aware of all the data segments except the following
>
------------------------------------------------------------------------
> "! system usage stats:
> ! 0.047000 elapsed 0.000000 user 0.000000 system sec
> ! [0.078125 user 0.031250 sys total]",
>
------------------------------------------------------------------------
>
> What do the number mean, it seems to appear only with a Logline for
SQL statements

These lines are emitted if you set log_statement_stats to "on".

They contain execution statistics for the query. The "user" and "sys"
times are acquired by the getrusage(2) or times(2) system call,
depending
on your operating system (on Windows, GetProcessTimes is used).
You can look at the man page for your system for details.

The values you see are:
wall time, CPU user time and kernel CPU time it took to execute the
query.
The values in brackets are the values accumulated for this database
session.

Yours,
Laurenz Albe

Arvind Singh wrote:
> Query 1
> -----------
> do we have a standard list of following Log Codes
> - Command_tag ex. IDLE, SELECT ..

See the source code for your version:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/
tcop/utility.c;h=ec36644a492ab69d5306b52294daab0599f332fe;hb=8522403c5cd
2351a1292b868a85aeec0aab5f2b3
Look for the function CreateCommandTag, it contains all the strings.

> - error_severity , ex. FATAL, LOG ..

See
http://www.postgresql.org/docs/9.0/static/runtime-config-logging.html#RU
NTIME-CONFIG-SEVERITY-LEVELS

> - sql_state_code , ex. 00000, 08P01 ..

See
http://www.postgresql.org/docs/9.0/static/errcodes-appendix.html#ERRCODE
S-TABLE


> Query 2
> ----------
>
> I have my CSV Log with lot of occurances of a certain Log select
statement.
>
------------------------------------------------------------------------
-----------------------------
> 2012-03-28 19:25:48.015
IST,"postgres","stock_apals",2388,"localhost:1898",4f731863.954,7,"idle"
,2012-
> 03-28 19:25:47 IST,2/98,0,LOG,00000,"statement: SELECT typname, oid
FROM pg_type WHERE typname IN
> ('oidvector', '_oidvector', 'unknown', '_unknown', 'refcursor',
'_refcursor', 'char', '_char',
> 'bpchar', '_bpchar', 'varchar', '_varchar', 'text', '_text', 'name',
'_name', 'bytea', '_bytea',
> 'bit', '_bit', 'bool', '_bool', 'int2', '_int2', 'int4', '_int4',
'int8', '_int8', 'oid', '_oid',
> 'float4', '_float4', 'float8', '_float8', 'numeric', '_numeric',
'inet', '_inet', 'money', '_money',
> 'point', '_point', 'lseg', '_lseg', 'path', '_path', 'box', '_box',
'circle', '_circle', 'polygon',
> '_polygon', 'uuid', '_uuid', 'xml', '_xml', 'interval', '_interval',
'date', '_date', 'time', '_time',
> 'timetz', '_timetz', 'timestamp', '_timestamp', 'abstime', '_abstime',
'timestamptz',
> '_timestamptz')",,,,,,,,"exec_simple_query,
.\src\backend\tcop\postgres.c:900",""
>
------------------------------------------------------------------------
-----------------------------
>
> Is is a performance concern. ?
> is there anything that i can do to keep this statement from recurring.

It will probably cause a sequential scan, but the table is not very big
unless
you have a lot of table or type definitions.

PostgreSQL does not issue such a query as far as I know.
You should figure out what in your application stack causes that.

If the query is issued once per session start, that shouldn't be a
problem
unless you open and close sessions all the time (in which case your
design ist probably bad and you should consider a connection pool).

Yours,
Laurenz Albe