Thread: Statements with syntax errors are not logged
When setting log_statement = 'all', statements that fail parsing are not logged. For example: LOG: connection received: host=[local] LOG: connection authorized: user=peter database=peter LOG: statement: select * from pg_class; LOG: duration: 19.084 ms ### here a log entry is missing ERROR: syntax error at or near "foo" at character 1 ### The following shows that post-parser errors are handled correctly. LOG: statement: select * from pg_class where reltype = true; ERROR: operator does not exist: oid = boolean at character 38 HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. Is that intentional? (This is in 8.1 and 8.2 at least.) (Yes, I realize there is log_min_error_statement, but that is an orthogonal feature.) -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes:
> When setting log_statement = 'all', statements that fail parsing are not
> logged.
> Is that intentional?
The 'mod' and 'ddl' settings obviously can't be handled until after
basic parsing. We could create a completely separate code path for
'all' but I'm not sure I see the point. To some extent the logging
settings are only meant to capture successfully executed statements
(eg, you don't get duration for a failed command, either) --- which
should be OK since failed ones won't change the database state.
As you say, log_min_error_statement is orthogonal.
regards, tom lane
Tom Lane wrote: > The 'mod' and 'ddl' settings obviously can't be handled until after > basic parsing. We could create a completely separate code path for > 'all' but I'm not sure I see the point. The users are evidently expecting that "log all statements" means to log all statements issued by the client. These implementation details are not obvious to a user. > To some extent the logging > settings are only meant to capture successfully executed statements Then it should be changed to log *only* successfully executed statements and explicitly documented as such. > (eg, you don't get duration for a failed command, either) --- which > should be OK since failed ones won't change the database state. I don't think tracking changes to the database state is the only motivation for wanting to see all statements. -- Peter Eisentraut http://developer.postgresql.org/~petere/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, Oct 19, 2006 at 04:28:17PM +0200, Peter Eisentraut wrote: > When setting log_statement = 'all', statements that fail parsing are not > logged. For example: [...] HA! This one has bitten me just today :-) The problem was a faulty client sending garbage and PostgreSQL complaining (rightfully) about bad UTF-8. But I quicly understood the problem and managed to debug otherwise (the raised eyebrows didn't last for long ;-) So I don't know whether it's worth to add complexity for this. Regards - -- tomás -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFFN5dUBcgs9XrR2kYRAqITAJ4sRSqDS0vkn56UeODJlLEZWgugiwCfY8vO H70VJ3XRbl9PME1s8HRefHA= =JcT2 -----END PGP SIGNATURE-----
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane wrote:
>> To some extent the logging
>> settings are only meant to capture successfully executed statements
> Then it should be changed to log *only* successfully executed statements
> and explicitly documented as such.
Well, maybe we should do that. It'd be relatively easy to handle the
ALL case before parsing and the other cases after in the "simple query"
code path, but I don't see any nice way to make it work in the
parse/bind/execute code path. As the code stands in HEAD, we don't
normally log parse/bind messages at all, just execute messages, because
there's no way to avoid serious redundancy/log-bloat/confusion otherwise.
Another attractive aspect is that we could merge duration logging with
statement reporting rather than having two separate log messages come
out in some cases.
The other thing we'd want to do is try a bit harder to ensure that
debug_query_string is always valid, so that one could be certain that
the combination of log_min_error_statement and log_statement = ALL
tells all. bind/execute messages don't currently bother to set
debug_query_string properly, but I think that could be fixed easily.
Comments?
regards, tom lane
I wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>> Then it should be changed to log *only* successfully executed statements
>> and explicitly documented as such.
> Well, maybe we should do that.
I fooled around with doing that, and while it's a simple code change,
I realized that it's got a fairly serious drawback: if you get an error
in a parameterized query, there's no way at all to find out via logging
what the parameters were that it failed on. That seems to negate one
of the main uses of the parameter-value-logging code that we put so much
work into in this cycle.
So I'm inclined to leave the behavior as-is. The documentation for
log_statement already says
Note: Statements that generate syntax errors are not logged. Setlog_min_error_statement to error to log such
statements.
We could improve the wording here, perhaps, but ultimately this is a
documentation issue.
regards, tom lane
On Thu, Oct 19, 2006 at 06:32:08PM -0400, Tom Lane wrote: > So I'm inclined to leave the behavior as-is. The documentation for > log_statement already says > > Note: Statements that generate syntax errors are not logged. Set > log_min_error_statement to error to log such statements. > > We could improve the wording here, perhaps, but ultimately this is a > documentation issue. It'd be good to put a note to that effect in postgresql.conf as well. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Am Freitag, 20. Oktober 2006 00:32 schrieb Tom Lane: > So I'm inclined to leave the behavior as-is. The documentation for > log_statement already says > > Note: Statements that generate syntax errors are not logged. Set > log_min_error_statement to error to log such statements. Oh, I missed that. Let's leave it as is. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On 10/19/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > When setting log_statement = 'all', statements that fail parsing are not > > logged. > > Is that intentional? > > The 'mod' and 'ddl' settings obviously can't be handled until after > basic parsing. We could create a completely separate code path for > 'all' but I'm not sure I see the point. Hello, Sometimes it can be very useful to be able to see even the incorrect commands: for example when the incorrect query is generated by an application or library that you haven't written yourself . A few days ago I was experimenting with Lazarus (an object-pascal based IDE similar to Delphi) and I was getting some unexpected syntax errors. In order to debug the problem i had to hack the sources of the postgres unit and add some writeln's right before the PQexec calls . It would have been much more convenient to see the bad queries in the logs ... -- Adrian Maier
Am Freitag, 20. Oktober 2006 09:13 schrieb Adrian Maier: > It would have been much more convenient to see the bad queries in > the logs ... I think you are missing the point of this discussion. All the erroneous queries are logged. The question is merely under what configuration. -- Peter Eisentraut http://developer.postgresql.org/~petere/