Thread: 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.mkrtchyan@desy.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.mkrtchyan@desy.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@postgresql.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@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 uni= que 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 goo= d! >> Nevertheless the logfiles are full of 'duplicate key value violates uniq= ue >> 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, >> =C2=A0 =C2=A0Tigran. >> >> -- >> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.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@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
On Fri, Nov 18, 2011 at 9:52 PM, Tomas Vondra <tv@fuzzy.cz> wrote: > 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 u= nique 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. =C2=A0This 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.mkrtchyan@desy.de> wrote: > > On Fri, Nov 18, 2011 at 9:52 PM, Tomas Vondra <tv@fuzzy.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@fuzzy.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@fuzzy.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. =A0This 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. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 23 November 2011 02:49, Robert Haas <robertmhaas@gmail.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 =3D ERRCODE_INTERNAL_ERROR; 612: edata->sqlerrcode =3D ERRCODE_INTERNAL_ERROR; 648: edata->sqlerrcode =3D 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). --=20 Peter Geoghegan =A0 =A0 =A0 http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
On Wed, Nov 23, 2011 at 7:19 AM, Peter Geoghegan <peter@2ndquadrant.com> wrote: > On 23 November 2011 02:49, Robert Haas <robertmhaas@gmail.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 --=20 =C3=81lvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On 24 November 2011 16:55, Alvaro Herrera <alvherre@commandprompt.com> wrot= e: > > 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. =A0In 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. =A0But 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. --=20 Peter Geoghegan =A0 =A0 =A0 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