pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to Log SELECT Statements Having Errors
4+ messages / 3 participants
[nested] [flat]
* How to Log SELECT Statements Having Errors
@ 2005-11-15 01:17 Lane Van Ingen <lvaningen@esncc.com>
0 siblings, 1 reply; 4+ messages in thread
From: Lane Van Ingen @ 2005-11-15 01:17 UTC (permalink / raw)
To: pgsql-sql
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?
(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.
Can someone advise on this ?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to Log SELECT Statements Having Errors
@ 2005-11-15 08:46 Richard Huxton <dev@archonet.com>
parent: Lane Van Ingen <lvaningen@esncc.com>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2005-11-15 08:46 UTC (permalink / raw)
To: Lane Van Ingen <lvaningen@esncc.com>; +Cc: pgsql-sql
Lane Van Ingen wrote:
> 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.
I don't know that you can only log SELECT statements that cause errors.
It should be simple enough to log all statements that cause errors though:
log_min_messages = error
log_min_error_statement = error
> Noticed the following:
> (1) The PostgreSQL manual says that config parm 'log_statement' does
> not appear to handle SELECT statements; true?
The "all" setting should.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to Log SELECT Statements Having Errors
@ 2005-11-15 08:52 Magnus Hagander <mha@sollentuna.net>
0 siblings, 1 reply; 4+ messages in thread
From: Magnus Hagander @ 2005-11-15 08:52 UTC (permalink / raw)
To: Lane Van Ingen <lvaningen@esncc.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to Log SELECT Statements Having Errors
@ 2005-11-15 14:53 Lane Van Ingen <lvaningen@esncc.com>
parent: Magnus Hagander <mha@sollentuna.net>
0 siblings, 0 replies; 4+ messages in thread
From: Lane Van Ingen @ 2005-11-15 14:53 UTC (permalink / raw)
To: Magnus Hagander <mha@sollentuna.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-11-15 14:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-15 01:17 How to Log SELECT Statements Having Errors Lane Van Ingen <lvaningen@esncc.com>
2005-11-15 08:46 ` Richard Huxton <dev@archonet.com>
2005-11-15 08:52 Re: How to Log SELECT Statements Having Errors Magnus Hagander <mha@sollentuna.net>
2005-11-15 14:53 ` Lane Van Ingen <lvaningen@esncc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox