Re: [PATCH] Filter error log statements by sqlstate

Oskari Saarenmaa <os(at)ohmu(dot)fi> writes:
> 17.01.2014 00:13, Tom Lane kirjoitti:
>> I find it hard to follow exactly what the use-case for this is; could
>> you make a defense of why we should carry a feature like this?

> I usually run PG with log_min_messages = warning and
> log_min_error_statement = error to log any unexpected errors. But as I
> have a lot of check constraints in my database as well as a lot of
> plpgsql and plpython code which raises exceptions on invalid user input
> I also get tons of logs for statements causing "expected" errors which I
> have to try to filter out with other tools.

But if the goal is to reduce clutter in your log, wouldn't you wish
to suppress the *entire* log entry for "expected" errors, not just the
SQL-statement field? Certainly all the previous discussion about this
type of feature (and there has been plenty) has presumed that you'd want
to suppress whole entries.

>> In any case, I'm finding it hard to believe that filtering by individual
>> SQLSTATEs is a practical API.

> I don't know about others, but filtering by individual SQLSTATE is
> exactly what I need - I don't want to suppress all plpgsql errors or
> constraint violations, but I may want to suppress plpgsql RAISE
> EXCEPTION and CHECK violations.

Meh. Again, there's been lots of prior discussion, and I think there's
consensus that a filtering API based solely on a list of SQLSTATEs
wouldn't be widely adequate. The most recent discussion I can find
about this is in this thread:

http://www.postgresql.org/message-id/flat/20131205204512(dot)GD6777(at)eldon(dot)alvh(dot)no-ip(dot)org#20131205204512(dot)GD6777@eldon.alvh.no-ip.org

That thread references an older one

http://www.postgresql.org/message-id/flat/19791(dot)1335902957(at)sss(dot)pgh(dot)pa(dot)us#19791(dot)1335902957@sss.pgh.pa.us

and I'm pretty sure that there are several others you could find with
a bit of digging. The more ambitious proposals required decorating
ereport calls with a new kind of severity labeling, reflecting how
likely it'd be that DBAs would want to read about the particular
error in their logs. That's be a lot of work though, and would require
us to make a lot of value judgements that might be wrong. The main
alternative that was discussed was to filter on the basis of SQLSTATE
classes, and maybe relocate a few specific ERRCODEs to different classes
if it seemed that they were a lot unlike other things in their class.

It hasn't really been proven that SQLSTATE-class filtering would work
conveniently, but AFAICS the only way to prove or disprove that is for
somebody to code it up and use it in production.

What I'd suggest is that you revise this patch so that it can handle
filtering on the basis of either individual SQLSTATEs or whole classes,
the former being able to override the latter. With a bit of wholesale
notation invention, an example could be

log_sqlstates = 'P0,!P0001,!42,42P05'

which would mean "log all messages in class P0, except don't log P0001;
don't log anything in class 42, except always log 42P05; for everything
else, log according to log_min_messages".

If you don't like that notation, feel free to propose another. I did
not like the one you had to start with, though, because it looked like
it was actually changing the error severity level, not just the log or
don't log decision.

BTW, I'd suggest that this filtering only happen for messages < PANIC
level; it's pretty hard to believe that anybody would ever want to
suppress a PANIC report.

Another thought here is that if you're willing to have the filter
only able to *prevent* logging, and not to let it *cause* logging
of messages that would otherwise be suppressed by log_min_messages,
it could be implemented as a loadable module using the emit_log_hook.
An experimental feature, which is what this really is, is always a lot
easier sell in that format since anybody who finds it useless needn't
pay the overhead (which I'm still concerned about ...). But I'm not
sure how important it might be to be able to upgrade a message's log
priority, so maybe that approach would be significantly less usable.

regards, tom lane