Re: BUG #12330: ACID is broken for unique constraints

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 <kgrittn(at)ymail(dot)com>:

> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
>