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

The following bug has been logged online:

Bug reference: 6300
Logged by: Tigran Mkrtchyan
Email address: tigran(dot)mkrtchyan(at)desy(dot)de
PostgreSQL version: 9.0.4
Operating system: Linux x86_64
Description: duplicate key value violates unique constraint
Details:

Hi,

our application uses PostgreSQL as a backend and heavily
relays on transactions. We never check constrains in our application as DB
does is better than we can an in an atomic way. This works very-very good!
Nevertheless the logfiles are full of 'duplicate key value violates unique
constraint'. I have two questions:

1. why it logged with ERROR level as it clients application behavior.

2. is there a way to suppress it or could you add a way to suppress it.

Thanks a lot,
Tigran.

On 18 Listopad 2011, 17:42, Tigran Mkrtchyan wrote:
>
> The following bug has been logged online:
>
> Bug reference: 6300
> Logged by: Tigran Mkrtchyan
> Email address: tigran(dot)mkrtchyan(at)desy(dot)de
> PostgreSQL version: 9.0.4
> Operating system: Linux x86_64
> Description: duplicate key value violates unique constraint
> Details:
>
> Hi,
>
> our application uses PostgreSQL as a backend and heavily
> relays on transactions. We never check constrains in our application as DB
> does is better than we can an in an atomic way. This works very-very good!
> Nevertheless the logfiles are full of 'duplicate key value violates unique
> constraint'. I have two questions:

If it's a question, then why have you submitted it as a bug? This is not a
bug, it's correct and expected behavior - please, repost the questions to
the appropriate place, e.g. pgsql-general mailing list.

Tomas

>
> 1. why it logged with ERROR level as it clients application behavior.
>
> 2. is there a way to suppress it or could you add a way to suppress it.
>
>
> Thanks a lot,
> Tigran.
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

On Fri, Nov 18, 2011 at 6:52 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
> On 18 Listopad 2011, 17:42, Tigran Mkrtchyan wrote:
>>
>> The following bug has been logged online:
>>
>> Bug reference:      6300
>> Logged by:          Tigran Mkrtchyan
>> Email address:      tigran(dot)mkrtchyan(at)desy(dot)de
>> PostgreSQL version: 9.0.4
>> Operating system:   Linux x86_64
>> Description:        duplicate key value violates unique constraint
>> Details:
>>
>> Hi,
>>
>> our application uses PostgreSQL as a backend and heavily
>> relays on transactions. We never check constrains in our application as DB
>> does is better than we can an in an atomic way. This works very-very good!
>> Nevertheless the logfiles are full of 'duplicate key value violates unique
>> constraint'. I have two questions:
>
> If it's a question, then why have you submitted it as a bug?

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.

Regards,
Tigran.

>This is not a
> bug, it's correct and expected behavior - please, repost the questions to
> the appropriate place, e.g. pgsql-general mailing list.
>
> Tomas
>
>>
>> 1. why it logged with ERROR level as it clients application behavior.
>>
>> 2. is there a way to suppress it or could you add a way to suppress it.
>>
>>
>> Thanks a lot,
>>    Tigran.
>>
>> --
>> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-bugs
>>
>
>
>

On 18 Listopad 2011, 20:38, Tigran Mkrtchyan wrote:
> On Fri, Nov 18, 2011 at 6:52 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
>> On 18 Listopad 2011, 17:42, Tigran Mkrtchyan wrote:
>>>
>>> The following bug has been logged online:
>>>
>>> Bug reference:      6300
>>> Logged by:          Tigran Mkrtchyan
>>> Email address:      tigran(dot)mkrtchyan(at)desy(dot)de
>>> PostgreSQL version: 9.0.4
>>> Operating system:   Linux x86_64
>>> Description:        duplicate key value violates unique constraint
>>> Details:
>>>
>>> Hi,
>>>
>>> our application uses PostgreSQL as a backend and heavily
>>> relays on transactions. We never check constrains in our application as
>>> DB
>>> does is better than we can an in an atomic way. This works very-very
>>> good!
>>> Nevertheless the logfiles are full of 'duplicate key value violates
>>> unique
>>> constraint'. I have two questions:
>>
>> If it's a question, then why have you submitted it as a bug?
>
> 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.

If you don't want to see those messages in the log, there are ways to do
that correctly - e.g. by using database sequences. I personally think that
an application that generates significant amount of "duplicate values"
during normal operation is somehow broken.

Anyway this definitely is not a bug, so if you want to discuss it further
(and how to fix you application), use pgsql-general mailing list.

Tomas

On Fri, Nov 18, 2011 at 9:52 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
> On 18 Listopad 2011, 20:38, Tigran Mkrtchyan wrote:
>> On Fri, Nov 18, 2011 at 6:52 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
>>> On 18 Listopad 2011, 17:42, Tigran Mkrtchyan wrote:
>>>>
>>>> The following bug has been logged online:
>>>>
>>>> Bug reference:      6300
>>>> Logged by:          Tigran Mkrtchyan
>>>> Email address:      tigran(dot)mkrtchyan(at)desy(dot)de
>>>> PostgreSQL version: 9.0.4
>>>> Operating system:   Linux x86_64
>>>> Description:        duplicate key value violates unique constraint
>>>> Details:
>>>>
>>>> Hi,
>>>>
>>>> our application uses PostgreSQL as a backend and heavily
>>>> relays on transactions. We never check constrains in our application as
>>>> DB
>>>> does is better than we can an in an atomic way. This works very-very
>>>> good!
>>>> Nevertheless the logfiles are full of 'duplicate key value violates
>>>> unique
>>>> constraint'. I have two questions:
>>>
>>> If it's a question, then why have you submitted it as a bug?
>>
>> 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.
>
> If you don't want to see those messages in the log, there are ways to do
> that correctly - e.g. by using database sequences. I personally think that
> an application that generates significant amount of "duplicate values"
> during normal operation is somehow broken.
>
> Anyway this definitely is not a bug, so if you want to discuss it further
> (and how to fix you application), use pgsql-general mailing list.

Still not convinced. Our application implements a distributed file
system where name space metadata is stored in DB. It's normal that two
or mode users trying to create a file with a same name in the same
directory. The "duplicate values" are converted into 'file exists'
error. I don't think you will be happy of /var/log/messages will be
full of error messages when a user tries to create an existing file.
Well, of course I can check myself the file exists, but why I need DB
transactions if my application have to take care about that.

Tigran.

>
> Tomas
>
>

On Fri, Nov 18, 2011 at 14:31, Tigran Mkrtchyan
<tigran(dot)mkrtchyan(at)desy(dot)de> wrote:
>
> On Fri, Nov 18, 2011 at 9:52 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:

> > Anyway this definitely is not a bug,
>
> Still not convinced.

You are unlikely to convince anyone here otherwise... Its works that
way by design.

> [...] I don't think you will be happy of /var/log/messages will be
> full of error messages when a user tries to create an existing file.
> Well, of course I can check myself the file exists, but why I need DB
> transactions if my application have to take care about that.

If you don't want them logged you could 'catch' them by creating a
wrapper function in plpsql, something like:
http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

"Tomas Vondra" <tv(at)fuzzy(dot)cz> writes:
> On 18 Listopad 2011, 20:38, Tigran Mkrtchyan wrote:
>> I think it's a bug to log with level ERROR absolute valid SQL
>> statement.

But it's *not* valid ... it failed.

> If you don't want to see those messages in the log, there are ways to do
> that correctly -

Has anybody suggested raising the log_min_messages setting?

regards, tom lane

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

On 23 November 2011 02:49, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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.

+1

I suggested that there be an INTERNAL_ERROR severity level before on
this list, in response to an opaque internal error that was raised in
the planner due to a bug in master (it was a simple elog() call that
raised the error), and the idea was not well received. Tom said that
"Well, the SQLSTATE for this sort of thing is already
ERRCODE_INTERNAL_ERROR". A quick search of that shows that it only
appears in the following places:

src/pl/plpgsql/src/plerrcodes.h
876: "internal_error", ERRCODE_INTERNAL_ERROR

src/backend/access/gist/gistsplit.c
374: (errcode(ERRCODE_INTERNAL_ERROR),

src/backend/access/nbtree/nbtinsert.c
455: (errcode(ERRCODE_INTERNAL_ERROR),

src/backend/utils/misc/guc.c
6369: (errcode(ERRCODE_INTERNAL_ERROR),

src/backend/utils/adt/xml.c
259: xml_ereport_by_code(WARNING, ERRCODE_INTERNAL_ERROR,
3787: xml_ereport(xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR,
3816: xml_ereport(xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR,
3828: xml_ereport(xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR,

src/backend/utils/adt/ri_triggers.c
3501: (errcode(ERRCODE_INTERNAL_ERROR),

src/backend/utils/mb/conversion_procs/utf8_and_win/utf8_and_win.c
125: (errcode(ERRCODE_INTERNAL_ERROR),
152: (errcode(ERRCODE_INTERNAL_ERROR),

src/backend/utils/mb/conversion_procs/utf8_and_iso8859/utf8_and_iso8859.c
135: (errcode(ERRCODE_INTERNAL_ERROR),
162: (errcode(ERRCODE_INTERNAL_ERROR),

src/backend/utils/error/elog.c
353: edata->sqlerrcode = ERRCODE_INTERNAL_ERROR;
612: edata->sqlerrcode = ERRCODE_INTERNAL_ERROR;
648: edata->sqlerrcode = ERRCODE_INTERNAL_ERROR;

src/backend/utils/errcodes.h
322:#define ERRCODE_INTERNAL_ERROR MAKE_SQLSTATE('X','X','0','0','0')

src/backend/postmaster/pgstat.c
493: (errcode(ERRCODE_INTERNAL_ERROR),

src/include/utils/elog.h
93: * ERRCODE_INTERNAL_ERROR if elevel is ERROR or more, ERRCODE_WARNING

contrib/sepgsql/uavc.c
184: (errcode(ERRCODE_INTERNAL_ERROR),
521: (errcode(ERRCODE_INTERNAL_ERROR),

contrib/sepgsql/hooks.c
94: (errcode(ERRCODE_INTERNAL_ERROR),
426: (errcode(ERRCODE_INTERNAL_ERROR),

contrib/sepgsql/selinux.c
768: (errcode(ERRCODE_INTERNAL_ERROR),
854: (errcode(ERRCODE_INTERNAL_ERROR),

contrib/sepgsql/label.c
84: (errcode(ERRCODE_INTERNAL_ERROR),
194: (errcode(ERRCODE_INTERNAL_ERROR),
234: (errcode(ERRCODE_INTERNAL_ERROR),
464: (errcode(ERRCODE_INTERNAL_ERROR),
526: (errcode(ERRCODE_INTERNAL_ERROR),

A new severity level is called for. In addition, if we had a new
severity level, the footprint wouldn't be too bad on all those legacy
elog() calls as compared to using errcode(ERRCODE_INTERNAL_ERROR).

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

On Wed, Nov 23, 2011 at 7:19 AM, Peter Geoghegan <peter(at)2ndquadrant(dot)com> wrote:
> On 23 November 2011 02:49, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> 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.
>
> +1
>
> I suggested that there be an INTERNAL_ERROR severity level before on
> this list, in response to an opaque internal error that was raised in
> the planner due to a bug in master (it was a simple elog() call that
> raised the error), and the idea was not well received. Tom said that
> "Well, the SQLSTATE for this sort of thing is already
> ERRCODE_INTERNAL_ERROR". A quick search of that shows that it only
> appears in the following places:

I mostly agree, but I don't think "internal error" is aiming at quite
the right target. For one thing, one big cause of concern is when the
database observes difficulty accessing the underlying storage. Those
errors are not "internal" to the database at all - they are coming
from the operating system. IME, users often don't realize what those
messages mean. So even aside from the difficulty of systematic log
filtering, it's easy for an inexperienced DBA to read a message
complaining about inability to write a block or flush XLOG and think
"oh, what a buggy piece of software PostgreSQL is" or perhaps "I
wonder why it's having so much trouble performing that operation?".
What we want them to think is "oh crap! my disk is dying".

So I would propose to steer clear of the word "internal", because the
really scary errors typically are not internal to PostgreSQL at all.
What I think we want to distinguish between is things that are
PEBKAC/GIGO, and everything else. In other words, if a particular
error message can be caused by typing something stupid, unexpected,
erroneous, or whatever into psql, it's just an error. But if no
input, however misguided, should ever cause that symptom, then it's, I
don't know what the terminology should be, say, a "severe error". So,
for example, these would all be severe errors:

cannot commit a transaction that deleted files but has no xid
StartTransactionCommand: unexpected state %s
could not create file "%s": %m
xlog flush request %X/%X is not satisfied --- flushed only to %X/%X

All of these are situations that no SQL command should ever be able to
manufacture. Either PostgreSQL has a bug, or the hardware is dying,
or at a minimum there is a disk full, something that the DBA will
certainly want to know about sooner rather than later. The failure of
the those transaction is not the user's "fault"; some external
circumstance has intervened.

Now, in some environments, it may be that things like unique key
violations are worrisome, because they may indicate *application*
bugs. So it would still be up to the DBA to provide monitoring for
those conditions as needed. But at least grepping the logs for severe
errors would provide an easy way for DBAs to know whether the database
believes itself to be sick.

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

Excerpts from Robert Haas's message of jue nov 24 13:14:38 -0300 2011:

> What I think we want to distinguish between is things that are
> PEBKAC/GIGO, and everything else. In other words, if a particular
> error message can be caused by typing something stupid, unexpected,
> erroneous, or whatever into psql, it's just an error. But if no
> input, however misguided, should ever cause that symptom, then it's, I
> don't know what the terminology should be, say, a "severe error".

+1

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 24 November 2011 16:55, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>
> Excerpts from Robert Haas's message of jue nov 24 13:14:38 -0300 2011:
>
>> What I think we want to distinguish between is things that are
>> PEBKAC/GIGO, and everything else.  In other words, if a particular
>> error message can be caused by typing something stupid, unexpected,
>> erroneous, or whatever into psql, it's just an error.  But if no
>> input, however misguided, should ever cause that symptom, then it's, I
>> don't know what the terminology should be, say, a "severe error".
>
> +1

+1

On reflection, that's better than what I proposed.

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

On tor, 2011-11-24 at 11:14 -0500, Robert Haas wrote:
> So I would propose to steer clear of the word "internal", because the
> really scary errors typically are not internal to PostgreSQL at all.
> What I think we want to distinguish between is things that are
> PEBKAC/GIGO, and everything else. In other words, if a particular
> error message can be caused by typing something stupid, unexpected,
> erroneous, or whatever into psql, it's just an error. But if no
> input, however misguided, should ever cause that symptom, then it's, I
> don't know what the terminology should be, say, a "severe error".

The current error levels are designed entirely in terms of the client
session behavior, that is,

warning -- things continue
error -- abort transaction
fatal -- abort session
panic -- abort everything

(more or less). For a client issuing statements and reading responses,
these levels make perfect sense.

What we need is a labeling system in terms of server behavior, which is
completely separate from these client levels. In principle, every
log-issuing statement (that is, ereport) should specify a client and a
server severity level.

On Thu, 24 Nov 2011 11:14:38 -0500, Robert Haas wrote:
> But if no
> input, however misguided, should ever cause that symptom, then it's,
> I
> don't know what the terminology should be, say, a "severe error".

"System Error"?

--
nw