Thread: 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? (2) Noticed that config parm 'log_min_error_statement' might do it, but not sure what each of the DEBUG* and other parameterswill 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 ?
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
> 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
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