The following bug has been logged on the website:
Bug reference: 13524
Logged by: Robert Rullo
Email address: bobby.rullo@coreos.com
PostgreSQL version: 9.4.1
Operating system: Mac OS X
Description:
Because this requires concurrent transactions, we will consider two
different sequences of SQL statements, each to be executed in PSQL against
the same database.
Sequence 1:
=========
drop table if exists "t1";
create table if not exists "t1" ("id" text not null primary key, "name"
text, "email" text) ;
begin;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
select count(*) from "t1" where email = 'bill@example.com';
insert into "t1" ("id","name","email") values
('1','bill','bill@example.com');
-- EXECUTE sequence1.txt HERE
commit;
Sequence 2:
==========
begin;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--select count(*) from "t1" where email = 'bill@example.com'; --UNCOMMENT
ME LATER
insert into "t1" ("id","name","email") values
('2','william','bill@example.com');
commit;
To reproduce:
1) execute statements in sequence 1 on a connection, stopping at the
"--EXECUTE" statement.
2) In another connection, execute all the statements in Sequence 2.
3) Back in the first connection execute the remaining statement (the
"commit;")
Expected:
error when trying to commit, eg. "ERROR: could not serialize"
Actual:
Both commits succeed.
Interesting tidbit:
If you do the exact steps as above but uncomment the "select count(*)" in
sequence 2, you get the expected error.