Re: Documenting serializable vs snapshot isolation levels - Mailing list pgsql-hackers

> I mean that if someone attempts to maintain referential integrity with
> SQL code, without using explicit locks, it is not reliable.
> Presumably the implementation of foreign keys in PostgreSQL takes this
> into account and blocks the kind of behavior shown below.  This
> behavior would not occur with true serializable transactions.

I see your point now, but I don't think we're really getting anywhere
here.   Doing it this way rather than using a foreign key constraint
is dumb, and a foreign key constraint works fine - so I think Simon's
statement that our approach works in most cases is 100% accurate.
With respect to your example here, we're right back to what I said way
upthread: if you're worried about concurrent updates or deletes,
SELECT ... FOR SHARE is sufficient.  If you're worried about
concurrent inserts, as you are here (delete from parent wants to make
sure no row can be concurrently inserted into child), you need to take
a SHARE lock on the table into which you want to prevent inserts.

As you pointed out, SELECT ... FOR SHARE isn't always available; when
it isn't, you can either rewrite the query - if that's feasible - or
take a SHARE lock on the table instead.

It really seems to me that we're going around in circles here.  I
don't feel that statements like this are advancing the dialogue one
bit:

> Referential integrity is a pretty common use case, and it is not covered without explicit locking.  Many other
> common use cases are not, either.

I believe this to be plain false.  Referential integrity as I
understand it (i.e. foreign key constraints, rather than some
half-baked home grown approach) works fine without explicit locking
and without even changing the transaction isolation level.  The
assertion that there are many other common use cases that are not
covered is hand-waving unsupported by evidence.  The only problems
you've raised so far are well-known problems in database theory; I
learned about them from Jim Gray's 1993 "Transaction Processing", but
that's about a 700 page book.  I suspect there are shorter texts that
you could read to pick up the main ideas but I'm not familiar with
them so I can't provide any pointers.

On further review, I actually think that our documentation is pretty
clear about this topic, too.  Everything we've talked about thus far
all seems to be spelled out in chapter 13:

http://www.postgresql.org/docs/8.3/interactive/mvcc-intro.html
http://www.postgresql.org/docs/8.3/interactive/transaction-iso.html
http://www.postgresql.org/docs/8.3/interactive/explicit-locking.html
http://www.postgresql.org/docs/8.3/interactive/applevel-consistency.html

Note in particular section 13.2.2.1. Serializable Isolation versus
True Serializability

...Robert