Skip site navigation (1) Skip section navigation (2)

bug #126, referential integrity makes big LOCK

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: bug #126, referential integrity makes big LOCK
Date: 2001-02-13 22:49:11
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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:
huge update
referential triggers start
transaction 2
delete something that referential triggers from tran 1 just checked and are relying on.
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

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


pgsql-bugs by date

Next:From: Tom LaneDate: 2001-02-13 23:39:36
Subject: Re: PgSQL 7.1 beta 3 breaks ODBC
Previous:From: Peter EisentrautDate: 2001-02-13 22:23:07
Subject: Re: bool type could be better documented

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group