Re: BUG #6300: duplicate key value violates unique constraint

On Fri, Nov 18, 2011 at 3:52 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
>> Then let me re-phrase it:
>>
>> I think it's a bug to log with level ERROR absolute valid SQL
>> statement. You have error code returned to the client to handle it. If
>> I want to log statements then I can turned that on.  This is client
>> appication debuggging and to error handling.
>
> The SQL command may be syntactically correct, but breaking a UNIQUE
> constraint is an error condition and therefore it's handled like an error
> condition - the user receives an exception and a message with ERROR level
> is written to the log.

While this is obviously not a bug, the need to distinguish "scary"
error messages from "non-scary" error messages is one that we've
discussed before and still haven't found a good solution to. We
report this as an ERROR:

duplicate key value violates unique constraint "%s"

We also report this as an ERROR:

xlog flush request %X/%X is not satisfied --- flushed only to %X/%X

There is no sort of systematic labeling of error messages in the log
to enable the DBA to figure out that the first error message is likely
nothing more serious than an integrity constraint doing its bit to
preserve data integrity, while the second is likely a sign of
impending disaster. And not just figure it out, but filter out the
stuff that's actually worth worrying about and alert on it. If the
first error message shows up in the log of a server I'm administering,
IDC. If the second one shows up, I want to be woken up in the middle
of the night immediately, and for that matter let's page the back-up
on call while we're at it. Right now, the best option is probably to
use something like tail_n_mail which, IIRC, has lots of hardcoded
error strings in it to help separate the wheat from the chaff, but
that's just a workaround for our failure to classify things properly.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company