I've rearranged the sequence of some lines in the previous post to facilitate discussion. I hope no offense is taken. >>> "Robert Haas" <robertmh...@gmail.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
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
