BUG #9301: INSERT WHERE NOT EXISTS on table with UNIQUE constraint in concurrent SERIALIZABLE transactions

The following bug has been logged on the website:

Bug reference: 9301
Logged by: Scott Marcy
Email address: mscott(at)apple(dot)com
PostgreSQL version: 9.3.2
Operating system: Mac OS X 10.9, CentOS 6.5
Description:

------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- The following seems to violate the fundamental guarantee of ISOLATION
LEVEL SERIALIZABLE in that the two
-- transactions below do not behave the same as if they were run serially.
Code that checks for
-- serialization failures obviously doesn't catch this problem and there is
no good workaround (other than
-- removing the UNIQUE constraint) as you get the same behavior if you use a
plpgsql function and run the
-- SELECT query separately.
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------

-----------
-- Setup --
-----------
CREATE TABLE test (
key integer UNIQUE,
val text
);

CREATE OR REPLACE FUNCTION insert_unique(k integer, v text) RETURNS void
LANGUAGE SQL AS $$
INSERT INTO test (key, val) SELECT k, v WHERE NOT EXISTS (SELECT key FROM
test WHERE key = k);
$$;

----------
-- Test --
----------

---------------------------
-- On psql Connection #1 --
---------------------------
\set VERBOSITY verbose
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT insert_unique(1, '1');

---------------------------
-- On psql Connection #2 --
---------------------------
\set VERBOSITY verbose
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT insert_unique(1, '2');

-- (Connection #2 waits here for #1)

---------------------------
-- On psql Connection #1 --
---------------------------
COMMMIT;

-- Connection #2 blows up:
-- ERROR: 23505: duplicate key value violates unique constraint
"test_key_key"
-- DETAIL: Key (key)=(1) already exists.
-- CONTEXT: SQL function "insert_unique" statement 1
-- LOCATION: _bt_check_unique, nbtinsert.c:398

-- Adding a "LOCK TABLE test IN ACCESS EXCLUSIVE MODE;" at the top of the
function doesn't help if you've performed
-- any queries prior to using this function.
-- Adding a "FOR UPDATE" to the WNE subquery does not help
-- Removing the UNIQUE constraint avoids the duplicate key error and
properly causes a serialization failure on
-- Connection #2's transaction.

-- It appears that the UNIQUE INDEX is not snapshotted at the start of a
transaction the same way the data table
-- is. The row inserted by Connection #1 is obviosuly visible in the index
in the transaction on Connection #2.

---------------------------
-- On psql Connection #2 --
---------------------------
ROLLBACK;
ALTER TABLE test DROP CONSTRAINT test_key_key;
DELETE FROM test;

-- Now repeat the test.

-- Output on Connection #2 (might need to 'COMMIT' on Connection #2) is as
expected:
-- ERROR: 40001: could not serialize access due to read/write dependencies
among transactions
-- DETAIL: Reason code: Canceled on identification as a pivot, during
commit attempt.
-- HINT: The transaction might succeed if retried.
-- LOCATION: PreCommit_CheckForSerializationFailure, predicate.c:4651