I'll repost my (OP) case, for the references to it to make more sense to the others.
Having the following table: CREATE TABLE "song_artist" ( "song_id" INT8 NOT NULL, "artist_id" INT8 NOT NULL, PRIMARY KEY ("song_id", "artist_id") ); Even trying to protect from this with a select, won't help to get away from the error, because at the beginning of the transaction the key does not exist yet. BEGIN ISOLATION LEVEL SERIALIZABLE READ WRITE; INSERT INTO song_artist (song_id, artist_id) SELECT 1, 2 WHERE NOT EXISTS (SELECT * FROM song_artist WHERE song_id=1 AND artist_id=2); COMMIT; 2014-12-26 21:38 GMT+03:00 Kevin Grittner <kgri...@ymail.com>: > Tom Lane <t...@sss.pgh.pa.us> wrote: > > > Just for starters, a 40XXX error report will fail to provide the > > duplicated key's value. This will be a functional regression, > > Not if, as is normally the case, the transaction is retried from > the beginning on a serialization failure. Either the code will > check for a duplicate (as in the case of the OP on this thread) and > they won't see the error, *or* the the transaction which created > the duplicate key will have committed before the start of the retry > and you will get the duplicate key error. > > > I think an appropriate response to these complaints is to fix the > > documentation to point out that duplicate-key violations may also > > be worthy of retries. > > > but I see no mention of the issue in chapter 13.) > > I agree that's the best we can do for stable branches, and worth > doing. > > It would be interesting to hear from others who have rely on > serializable transactions in production environments about what > makes sense to them. This is probably the wrong list to find such > people directly; but I seem to recall Josh Berkus has a lot of > clients who do. Josh? Any opinion on this thread? > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >