I'm trying to implement a deterministic configuration for database sessions, and one problem seems to be with sequences. The following example demonstrates:
CREATE TABLE foo (id serial, n integer);
-- connection 1
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
-- connection 2
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
insert into foo (n) values (1),(2),(3);
-- connection 1
select nextval('fun_id_seq') - (select count(*) from fun) as leak;
leak
----------
4
(1 row)
This looks like a dirty read. If I was to run the transactions serially I would not get this result. Why is this then considered SERIALIZABLE?
Thanks