I've rearranged the sequence of some lines in the previous post to
facilitate discussion. I hope no offense is taken.
>>> "Robert Haas" <robertmhaas(at)gmail(dot)com> wrote:
> 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
I read all of the above over very carefully, several times, before
starting this thread. These are precisely the sections I feel could
use correction and improvement.
> Doing it this way rather than using a foreign key constraint
> is dumb, and a foreign key constraint works fine
The point is that it is something that would work reliably under
serializable isolation, but not under snapshot isolation. I picked it
merely because it is a simple integrity test that someone might choose
to enforce in a trigger in some other database, and might not
recognize it as an unreliable technique in PostgreSQL. Dumb or not,
they may lose integrity after moving to PostgreSQL if they miss this,
and I propose documenting the issue to assist people.
> 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.
> 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.
This advice seems consistent with the current PostgreSQL documentation
(cited above) and might lead one to believe that in the example you
reference, adding a FOR SHARE to the SELECT which confirms the
existence of the parent row, and a LOCK TABLE on the child table at
the start of the transaction which does the DELETE of the parent would
provide integrity. It does not; try it if you want confirmation. It
does introduce blocking, but after the block clears, the result in the
database is identical to the example as originally posted. This is
why I think the documentation could use enhancement.
> It really seems to me that we're going around in circles here.
Agreed. I guess I contributed to that by questioning whether "most"
or "many" was a more appropriate adjective, which is pretty
irrelevant, really. I'll try to stay focused on examples of things
that work in one environment and don't in the other, with tips to get
the desired behavior within PostgreSQL. I have come up with many more
examples of these than I have posted on-list, but posting every single
example doesn't seem valuable to me. I'm trying to generalize to
provide useful guidelines, but feel sure that I'm re-inventing the
wheel here.
Thanks for suggesting Jim Gray's "Transaction Processing". I'll look
for it. If it's framing things from a theoretical point of view,
there will be some work necessary to distill it down to the concise
and practical advice which I've found necessary to effectively guide
application programmers, but at least I can do it with more confidence
that I've covered all the relevant ground.
-Kevin