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@apple.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