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

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
>
>