PostgreSQL Serializable Isolation - false positives

postgresql_serializable_isolation.sql

-- (This code was run in PostgreSQL 9.6.1)
-- Demonstration of how serializable isolation for PostgreSQL, which detects possible
-- interference between concurrent transactions, can produce false positives

-- in psql, create the following table

CREATE TABLE users(
  id SERIAL NOT NULL PRIMARY KEY,
  username VARCHAR NOT NULL
);

-- open a second psql session
-- in session 1, type this
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT * FROM users WHERE username = 'alice';
  INSERT INTO users ("username") VALUES ('alice');
  -- don't type `END;` just yet; we want a concurrent transaction
 
--- in session 2, type this
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT * FROM users WHERE username = 'bob';
  INSERT INTO users ("username") VALUES ('bob');
  
-- now type `END;` in each of the sessions.
-- whichever one you end second will give you
-- this error:

--- 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:4659

-- PostgreSQL is saying "I think the second one to end would have gotten
-- a different result if the first one had been complete before it started"
-- In this case, it wouldn't have; serializable isolation is giving is a 
-- false positive for interference between concurrent transactions

• Original gist: https://gist.github.com/nathanl/f98450014f62dcaf0405394a0955e18e
• https://www.postgresql.org/docs/current/static/transaction-iso.html#XACT-SERIALIZABLE
• https://wiki.postgresql.org/wiki/SSI