bug #126, referential integrity makes big LOCK

Dave E Martin (xxiii(at)cyberdude(dot)com) reports a bug with a severity of 4
The lower the number the more severe it is.

Short Description
bug #126, referential integrity makes big LOCK

Long Description
In referral to bug #126, where someone is wondering why triggers use FOR UPDATE on data they aren't actually going to update:

Alas, bug responses are not public, so either this is really a problem, or its not, I don't know. I post here to make sure that this is a scenario that has been considered. I assume the reason is:

given mvcc, the following could happen if not careful:

transaction 1:
begin
huge update
commit
referential triggers start
transaction 2
begin
delete something that referential triggers from tran 1 just checked and are relying on.
commit
tran 2 triggers see pre-tran 1 data and think its ok to delete.
tran 2 finishes before tran 1.
tran 1 already checked that tran 2 row and thinks its fine, commit
finishes.

Because of mvcc tran 2 does not realize there is a problem (its triggers looked at the state of things before tran 1 began)
meanwhile tran 2 then completes, but now the state is not the same as it was when tran 2 began, and we now have a database with an integrity violation. So, either tran 2 (or at least its triggers) needs access to tran 1's changes, even though tran 1 isn't finished yet, or tran 1 needs a way to block tran 2 from altering data its relying on, until tran 2's triggers can see tran 1's alterations.

Presumably, by the triggers using FOR UPDATE, they circumvent mvcc, and force the right thing to happen, even though they are not actually going to alter the thing being selected with FOR UPDATE. By locking it, "we" force other entities to not alter it until the assumptions "we" made based on that value are committed.

This is some speculation on my part based on what i've read, and I at least hope that postgres would not allow the above scenario to happen.

Perhaps something about this could be put in the documentation on referential integrity.

Sample Code

No file was uploaded with this report