Thread: Fwd: question on foreign key lock
Hi,<br /><br />maybe this is a better group for this question?<br /><br />I can't see why creating foreign key on table
Areferencing table B, generates an AccessExclusiveLock on B.<br />It seems (to a layman :-) ) that only writes to B
shouldbe blocked.<br /><br />I'm really interested if this is either expected effect or any open TODO item or
suboptimalbehavior of postgres.<br /><br />Thanks<br /><br /><br /><div class="gmail_quote">---------- Forwarded
message----------<br />From: <b class="gmail_sendername">Filip Rembiałkowski</b> <span dir="ltr"><<a
href="mailto:filip.rembialkowski@gmail.com">filip.rembialkowski@gmail.com</a>></span><br/> Date: Thu, Nov 1, 2012 at
5:33PM<br />Subject: question on foreign key lock<br />To: pgsql-general list <<a
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>><br/><br /><br />Hello.<br /><br /> Why
addingFK creates AccessExclusiveLock on referenced tabble?<br /><br /> {{{<br /> CREATE TABLE A ( id integer, idb
integer);<br /> INSERT INTO A (id,idb) SELECT x, x%4 FROM generate_series(1,100) x;<br /><br /> CREATE TABLE B ( id int
primarykey );<br /> INSERT INTO B VALUES (0),(1),(2),(3);<br /><br /> BEGIN;<br /> ALTER TABLE A ADD CONSTRAINT
a_idb_fkeyFOREIGN KEY (idb) REFERENCES b;<br /> SELECT * FROM pg_locks l, pg_class c WHERE l.pid = pg_backend_pid() AND
l.locktype='relation'AND l.mode ilike '%exclusive%' AND l.relation=c.oid;<br /> ROLLBACK;<br /> }}}<br /><br /><br />
LastSELECT is showing AccessExclusive on B.<br /> Why not Exclusive?<br /><br /><br /> Thanks,<br /> Filip<br
/></div><br/>
On Thu, Nov 8, 2012 at 3:45 AM, Filip Rembiałkowski <filip.rembialkowski@gmail.com> wrote: > maybe this is a better group for this question? > > I can't see why creating foreign key on table A referencing table B, > generates an AccessExclusiveLock on B. > It seems (to a layman :-) ) that only writes to B should be blocked. > > I'm really interested if this is either expected effect or any open TODO > item or suboptimal behavior of postgres. This comment explains it: /* * Grab an exclusive lock on the pk table, so that someone doesn't delete * rows out from under us. (Althougha lesser lock would do for that * purpose, we'll need exclusive lock anyway to add triggers to the pk * table;trying to start with a lesser lock will just create a risk of * deadlock.) */ pkrel = heap_openrv(fkconstraint->pktable,AccessExclusiveLock); Concurrent DDL is something that's been discussed in detail on this list in the past; unfortunately, there are some tricky race conditions are the shared invalidation queue and SnapshotNow that make it hard to implement properly. I'm hoping to have some time to work on this at some point, but it hasn't happened yet. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert, thank you for the answer. 1. "need exclusive lock anyway to add triggers". Why adding a trigger needs exclusive lock? Someone would say blocking reads is not needed (since possible trigger events are: Insert/Update/Delete/Truncate). 2. "will create a risk of deadlock". From user perspective a risk of deadlock is sometimes better than excessive locking. Transactional DDL users should be prepared for exceptions/retries anyway. 3. I made a naive test of simply changing AccessExclusiveLock to ExclusiveLock, and seeing how many regression tests it breaks. It breaks none :-) Current Git head gives me 2 fails/133 tests regardless of this change. regards, Filip On Mon, Nov 12, 2012 at 5:20 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Thu, Nov 8, 2012 at 3:45 AM, Filip Rembiałkowski > <filip.rembialkowski@gmail.com> wrote: >> maybe this is a better group for this question? >> >> I can't see why creating foreign key on table A referencing table B, >> generates an AccessExclusiveLock on B. >> It seems (to a layman :-) ) that only writes to B should be blocked. >> >> I'm really interested if this is either expected effect or any open TODO >> item or suboptimal behavior of postgres. > > This comment explains it: > > /* > * Grab an exclusive lock on the pk table, so that someone doesn't delete > * rows out from under us. (Although a lesser lock would do for that > * purpose, we'll need exclusive lock anyway to add triggers to the pk > * table; trying to start with a lesser lock will just create a risk of > * deadlock.) > */ > pkrel = heap_openrv(fkconstraint->pktable, AccessExclusiveLock); > > Concurrent DDL is something that's been discussed in detail on this > list in the past; unfortunately, there are some tricky race conditions > are the shared invalidation queue and SnapshotNow that make it hard to > implement properly. I'm hoping to have some time to work on this at > some point, but it hasn't happened yet. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company
On Wed, Dec 5, 2012 at 7:08 AM, Filip Rembiałkowski <filip.rembialkowski@gmail.com> wrote: > Robert, thank you for the answer. > > 1. "need exclusive lock anyway to add triggers". > Why adding a trigger needs exclusive lock? > Someone would say blocking reads is not needed (since possible trigger > events are: Insert/Update/Delete/Truncate). > > 2. "will create a risk of deadlock". > From user perspective a risk of deadlock is sometimes better than > excessive locking. Transactional DDL users should be prepared for > exceptions/retries anyway. > > 3. I made a naive test of simply changing AccessExclusiveLock to > ExclusiveLock, and seeing how many regression tests it breaks. It > breaks none :-) > Current Git head gives me 2 fails/133 tests regardless of this change. Sure. You could probably downgrade it quite a bit further without breaking the regression tests, but that doesn't mean it's safe in all cases. Rather than having this discussion all over again, I suggest that you have a look at commits 2dbbda02e7e688311e161a912a0ce00cde9bb6fc, 2c3d9db56d5d49bdc777b174982251c01348e3d8, a195e3c34f1eeb6a607c342121edf48e49067ea9, and the various mailing list discussions pertaining thereto, particularly the thread "ALTER TABLE lock strength reduction patch is unsafe" which was started by Tom Lane. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2012-12-05 17:05:41 -0500, Robert Haas wrote: > On Wed, Dec 5, 2012 at 7:08 AM, Filip Rembiałkowski > <filip.rembialkowski@gmail.com> wrote: > > Robert, thank you for the answer. > > > > 1. "need exclusive lock anyway to add triggers". > > Why adding a trigger needs exclusive lock? > > Someone would say blocking reads is not needed (since possible trigger > > events are: Insert/Update/Delete/Truncate). > > > > 2. "will create a risk of deadlock". > > From user perspective a risk of deadlock is sometimes better than > > excessive locking. Transactional DDL users should be prepared for > > exceptions/retries anyway. > > > > 3. I made a naive test of simply changing AccessExclusiveLock to > > ExclusiveLock, and seeing how many regression tests it breaks. It > > breaks none :-) > > Current Git head gives me 2 fails/133 tests regardless of this change. > > Sure. You could probably downgrade it quite a bit further without > breaking the regression tests, but that doesn't mean it's safe in all > cases. Rather than having this discussion all over again, I suggest > that you have a look at commits > 2dbbda02e7e688311e161a912a0ce00cde9bb6fc, > 2c3d9db56d5d49bdc777b174982251c01348e3d8, > a195e3c34f1eeb6a607c342121edf48e49067ea9, and the various mailing list > discussions pertaining thereto, particularly the thread "ALTER TABLE > lock strength reduction patch is unsafe" which was started by Tom > Lane. Just to give an example about the complexities surrounding this: Lowering the lock level for foreign key creation probably would be dangerous for query planning more precisely join removal. S1: BEGIN TRANSACTION ISOLATION LEVEL REPATABLE READ; S1: SELECT * FROM a; S2: DELETE FROM a WHERE a.id IN (all_duplicate_ids); S2: ALTER TABLE a ADD CONTSTRAINT a_unique UNIQUE (a); S2: ALTER TABLE b ADD CONSTRAINT b_fkey FOREIGN KEY (b_id) REFERENCES a(id)); S1: SELECT b.id FROM a LEFT JOIN b ON(b.id = a.id); The last S1 query might now remove the join to b because of the foreign key (which it sees due to SnapshotNow semantics) although rows that violate unique key (which is required for the foreign key) still exist. The existance of those duplicate values would change the result though! (come to think of it, I think we might still hit the above case if S1 doesn't access a before the foreign key gets altered...) Greetings, Andres Freund --Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Dec 5, 2012 at 7:08 AM, Filip Rembiałkowski
> <filip.rembialkowski@gmail.com> wrote:
>> 3. I made a naive test of simply changing AccessExclusiveLock to
>> ExclusiveLock, and seeing how many regression tests it breaks. It
>> breaks none :-)
> Sure. You could probably downgrade it quite a bit further without
> breaking the regression tests, but that doesn't mean it's safe in all
> cases.
In fact, since the regression tests make no attempt whatsoever to stress
DDL executed concurrently with table accesses, it doesn't prove a darn
thing. The standard regression tests actually try quite hard to avoid
such scenarios, so that the results will be repeatable. You could
perhaps build relevant test cases using the isolationtester
infrastructure, but I don't think anyone has tried particularly.
regards, tom lane