Re: [HACKERS] Fwd: question on foreign key lock
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 robertmh...@gmail.com wrote: On Thu, Nov 8, 2012 at 3:45 AM, Filip Rembiałkowski filip.rembialkow...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: question on foreign key lock
On Wed, Dec 5, 2012 at 7:08 AM, Filip Rembiałkowski filip.rembialkow...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: question on foreign key lock
On 2012-12-05 17:05:41 -0500, Robert Haas wrote: On Wed, Dec 5, 2012 at 7:08 AM, Filip Rembiałkowski filip.rembialkow...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: question on foreign key lock
Robert Haas robertmh...@gmail.com writes: On Wed, Dec 5, 2012 at 7:08 AM, Filip RembiaÅkowski filip.rembialkow...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: question on foreign key lock
On Thu, Nov 8, 2012 at 3:45 AM, Filip Rembiałkowski filip.rembialkow...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fwd: question on foreign key lock
Hi,
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.
Thanks
-- Forwarded message --
From: Filip Rembiałkowski filip.rembialkow...@gmail.com
Date: Thu, Nov 1, 2012 at 5:33 PM
Subject: question on foreign key lock
To: pgsql-general list pgsql-gene...@postgresql.org
Hello.
Why adding FK creates AccessExclusiveLock on referenced tabble?
{{{
CREATE TABLE A ( id integer, idb integer );
INSERT INTO A (id,idb) SELECT x, x%4 FROM generate_series(1,100) x;
CREATE TABLE B ( id int primary key );
INSERT INTO B VALUES (0),(1),(2),(3);
BEGIN;
ALTER TABLE A ADD CONSTRAINT a_idb_fkey FOREIGN KEY (idb) REFERENCES b;
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;
ROLLBACK;
}}}
Last SELECT is showing AccessExclusive on B.
Why not Exclusive?
Thanks,
Filip
