Re: PostgreSQL not ACID compliant?

Quoth tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane):
> Florian Weimer <fw(at)deneb(dot)enyo(dot)de> writes:
>> Is this a bug, or is SQLxx serializability defined in different terms?
>
> Strictly speaking, we do not guarantee serializability because we do not
> do predicate locking. See for example
> http://archives.postgresql.org/pgsql-general/2003-01/msg01581.php
>
> AFAIK, no commercial database does predicate locking either, so we all
> fall short of true serializability. The usual solution if you need the
> sort of behavior you're talking about is to take a non-sharable write
> lock on the table you want to modify, so that only one transaction can
> do the COUNT/INSERT at a time.

It's worth elaborating on the answers here because the "count(*) is
not stable" argument has been made by MySQL folks as, in effect, an
argument that ACID is impossible, and thus conforming to it is
pointless, and thus, anyone that claims to conform to it must be a big
fat idiot/liar.

This also begs two other questions.

1. What, _exactly_, is the aggregate select getting?

The assumption made in Florian's article is that
"SELECT COUNT(*) from items"
is getting the dynamic thing that is the number of rows in the
table.

_Reality_ is that what is actually returned by any database system
we know of is something else, that would be better described as
"How many rows did we find when we ran the query?"

In the context of talking about "ACID," the answer, more
precisely, is "How many committed tuples were there in ITEMS when
we started running this query?"

If two queries query this value at the same time, we would expect
them to get the same answer.

Which means that the condition Florian describes seems ill-framed.

2. Are aggregates actually something that should we should imagine
applying predicate properties to?

They aren't invertable, so I wouldn't think so.

Would you expect to be able to do a query like:
update transaction_table set count(*) = 897411;
???

Then why would you expect to be able to cast assertions based on
aggregates?

We aren't working in some pure form of ML or Haskell, where nothing
ever gets reassigned, but rather the environment gets replaced with
one containing new values.

That kind of system does not cope well with concurrency; if ALL
side-effects are forbidden, then that rules out having just about any
kind of concurrency.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://www.ntlug.org/~cbbrowne/nonrdbms.html
Signs of a Klingon Programmer #5: "I have challenged the entire
quality assurance team to a Bat-Leth contest. They will not concern us
again."