BUG #12330: ACID is broken for unique constraints

The following bug has been logged on the website:

Bug reference: 12330
Logged by: Nikita Volkov
Email address: nikita(dot)y(dot)volkov(at)mail(dot)ru
PostgreSQL version: 9.2.4
Operating system: OSX 10.8.2
Description:

Executing concurrent transactions inserting the same value of a unique key
fails with the "duplicate key" error under code "23505" instead of any of
transaction conflict errors with a "40***" code.

E.g., 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;