pg-serializable-dup-key.sql

denis=# \d test
     Table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

denis=# select * from test;
 id 
----
  1
  2
(2 rows)

-- session 1: steps are reproduced, one at a time, in session 2 below,
-- before continuing to the next step
denis=# \echo session 1
session 1
denis=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
denis=# select max(id) from test;
 max 
-----
   2
(1 row)

denis=# insert into test values (3);
INSERT 0 1
denis=# commit;
COMMIT

-- session 2: mimics session 1
denis=# \echo session 2
session 2
denis=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
denis=# select max(id) from test;
 max 
-----
   2
(1 row)

denis=# insert into test values (3);    -- it waits here until session 1 commits
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(3) already exists.