Re: PostgreSQL not ACID compliant?

"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> writes:

> Postgresql supports Serializable transactions, which are 100% ACID
> compliant.

How can I activate it? 8-)

Yes, I know about SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, please
read on.

Given the two tables:

CREATE TABLE items (item INTEGER);
CREATE TABLE counts (count INTEGER);

And transactions following this pattern:

number := <some number>;
INSERT INTO items VALUES (number);
nr := SELECT COUNT(*) FROM items;
INSERT INTO counts VALUES (nr);
COMMIT;

If these transactions are executed serially, the following condition
always holds once the tables are non-empty:

(*) (SELECT COUNT(*) FROM items) = (SELECT MAX(count) FROM counts)

Now look at the following history:

Session 1 Session 2

number := <some number>;
number := <some number>;
INSERT INTO items VALUES (number);
INSERT INTO items VALUES (number);
nr := SELECT COUNT(*) FROM items;
nr := SELECT COUNT(*) FROM items;
INSERT INTO counts VALUES (nr);
INSERT INTO counts VALUES (nr);
COMMIT;
COMMIT;

If you enter these commands in two parallel psql sessions, in the
order indicated, condition (*) no longer holds once both transactions
are completed. Therefore, PostgreSQL must have generated a
non-serializable history.

Is this a bug, or is SQLxx serializability defined in different terms?