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