On May 11, 2010, at 20:05 , Jan Wieck wrote: > The problem really is that in the case of deleting a PK row while a > concurrent transaction creates such a reference cannot be solved with user > level visibility rules in case of a serializable transacton, unless you go > really expensive routes.
Yeah. The information to detect this is there, though - the xmax of the PK row will be a multixact in this case, and one member of that set won't be deemed visible by the deleting transaction. > One corner case is that the transaction doing the FK INSERT commits after the > serializable transaction doing the PK DELETE got its snapshot and also does > the PK check before the PK DELETE got the lock on it. No user level > visibility allows it to see that newly created reference. And unless the FK > INSERTer actually UPDATE's the PK row (expensive), the PK DELETE will not > throw anything. It will wait to get the lock and go ahead with the delete. Exactly. It consciously waits for the lock (knowing that it was held by a concurrent transaction *not* visible to the deleting transaction), and after obtaining the lock goes on to delete the row. If the concurrent transaction hadn't held a mere lock, but had instead UPDATEd the row, this would cause a serialization error. > The PK DELETE needs to be able to do some sort of dirty scan in order to see > those new references. That is what I think Tom was referring to. Yeah. Though the need for that "dirty scan" (it's not actually a scan with DIRTY READ semantics, but rather one with READ COMMITTED semantics) might vanish if a SHARE lock had the same effect (causing a serialization error) on concurrent transactions that an UPDATE has. I'm not yet convinced that this is true, nor do I necessarily think that making all SHARE locks behave that way would be a good idea. But if my assertion is in fact true it would allow for robust user-level referential constraints by either modifying SHARE-lock behavior or adding a new row-lock type. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
