postgresql is not strictly serializable/externally consistent
for example this will commit under serializable:
create table counters(counter int);
insert into counters(counter) values(1);
BEGIN TRANSACTION ISOLATION LEVEL serializable;
select sum(counter) from counters;
/* insert sum into counters. wait until committing next transaction before executing the insert */
insert into counters(counter) values(1);
COMMIT;
/* this transaction should commit before doing the insert in the above transaction and after the above transaction has calculated the sum */
BEGIN TRANSACTION ISOLATION LEVEL serializable;
insert into counters(counter) values(10);
COMMIT;
both transactions commit and the final table looks like:
1, 10, 1
which is possible if the first transaction committed first, and then the second transaction committed. but it is possible for another client to see the table as: [1], [1, 10], [1, 1, 10] which is a sequence of states which should not be possible. if you see [1], [1, 10] then you should see [1, 10, 11] as the last state. hence it violates external consistency.
Well, benmmurphy isn't talking about serializability (some correct ordering exists), but strict serializabilty (roughly: at least one correct ordering corresponds to wall clock order). PG does have the former, but not the latter.
Huh? The example is for PostgreSQL's "serializable" isolation level, and that's what I'm confused about.
Serializability should ensure that the outcome is equivalent to some serial execution. What serial execution of those five transactions yields [1], [1, 10], [1, 1, 10]?
But also, I just read that prior to PostgreSQL 9.1 (released in 2011), the "serializable" isolation level was actually just snapshot isolation (now called "repeatable read"). So maybe that's what benmmurphy is referring to?
for example this will commit under serializable:
both transactions commit and the final table looks like:1, 10, 1
which is possible if the first transaction committed first, and then the second transaction committed. but it is possible for another client to see the table as: [1], [1, 10], [1, 1, 10] which is a sequence of states which should not be possible. if you see [1], [1, 10] then you should see [1, 10, 11] as the last state. hence it violates external consistency.