From: "Lane Van Ingen" <lvaningen@esncc.com>
To: "Magnus Hagander" <mha@sollentuna.net>,
	<pgsql-sql@postgresql.org>
Subject: Re: How to Log SELECT Statements Having Errors
Date: Tue, 15 Nov 2005 09:53:29 -0500
Message-ID: <EKEMKEFLOMKDDLIALABIIEAPCFAA.lvaningen@esncc.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
In-Reply-To: <6BCB9D8A16AC4241919521715F4D8BCE6C7BC6@algol.sollentuna.se>
Importance: Normal

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@sollentuna.net]
Sent: Tuesday, November 15, 2005 3:53 AM
To: Lane Van Ingen; pgsql-sql@postgresql.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