Re: How to Log SELECT Statements Having Errors

Many thanks for your helpful suggestions ....

To answer your question about 'log_statement' not appearing to handle SELECT
statements: could be a documentation omission. I got that from version 8.0.1
documentation PDF, p 268, section 16.4.6.3 where 'log_statement(string)' is
discussed:

http://www.postgresql.org/docs/8.0/static/runtime-config.html#RUNTIME-CONFIG
-LOGGING

-----Original Message-----
From: Magnus Hagander [mailto:mha(at)sollentuna(dot)net]
Sent: Tuesday, November 15, 2005 3:53 AM
To: Lane Van Ingen; pgsql-sql(at)postgresql(dot)org
Subject: RE: [SQL] How to Log SELECT Statements Having Errors

> We are putting a new application on PostgreSQL 8.0.1 (Windows
> 2003); we are coming off of MySQL, and are having a hard time
> finding all of the offending SQL calls.
>
> It would help a great deal if I could log the content of any
> SQL calls that fail. Am only interested in SELECT statements.
>
> Noticed the following:
> (1) The PostgreSQL manual says that config parm 'log_statement' does
> not appear to handle SELECT statements; true?

No. Where did you get that from, perhaps the manual needs to be clearer?

The place to look is:
http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html#RU
NTIME-CONFIG-LOGGING-WHAT

set log_statement to 'all' and it will log everything, including
SELECTs.

But this will include statements that succeed as well, not just those
who fails.

> (2) Noticed that config parm 'log_min_error_statement' might
> do it, but
> not sure what each of the DEBUG* and other parameters
> will buy me vs ERROR. I want any statement issued by a client
> that cannot be executed due to an SQL error of any kind.

Yes, if you want to log only queries that fail, log_min_error_statement
is the correct switch to use.

If you set it to ERROR, you will get a log of every statement that
causes an ERROR or FATAL.
If you set it to WARNING, you will get a log of every statement that
acuses WARNING, ERROR or FATAL.
etc etc for the other values.

In this case, you'll want ERROR or possibly WARNING.

//Magnus