Re: PostgreSQL not ACID compliant?

"scott.marlowe" <scott.marlowe@ihs.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?