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.y.volkov@mail.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;