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

On 18 Listopad 2011, 20:38, Tigran Mkrtchyan wrote:
> On Fri, Nov 18, 2011 at 6:52 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>> On 18 Listopad 2011, 17:42, Tigran Mkrtchyan wrote:
>>>
>>> The following bug has been logged online:
>>>
>>> Bug reference: =C2=A0 =C2=A0 =C2=A06300
>>> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Tigran Mkrtchyan
>>> Email address: =C2=A0 =C2=A0 =C2=A0tigran.mkrtchyan@desy.de
>>> PostgreSQL version: 9.0.4
>>> Operating system: =C2=A0 Linux x86_64
>>> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0duplicate key value violates un=
ique 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