Re: PostgreSQL not ACID compliant?

Christopher Browne <cbbrowne(at)acm(dot)org> writes:
> It's worth elaborating on the answers here

Agreed.

> 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.

What it's really getting is the static thing that was the number of
committed rows as of the relevant query snapshot instant (either start
of transaction or start of command, depending on your isolation mode).

This *is* reproducible (as long as you keep using the same snapshot of
course). Whether it is useful to solve any particular problem is quite
a different discussion.

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

They would be guaranteed to get the same answer as long as no
transaction (that affected the table) commits between the instants of
their two snapshots.

(Caveat: if you are thinking about queries inside transactions that
have themselves modified the table, then such queries see the effects
of prior commands in their own transaction, on top of the relevant
query snapshot of the outside world's effects.)

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

Actually, I think the standard academic discussion of this shows that
you can have serializability failures any time a row is added/deleted/
updated that would pass the WHERE clause of any concurrent query.
Whether that query is aggregating or not is not relevant.

regards, tom lane