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

question on foreign key lock

From: Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com>
To: pgsql-general list <pgsql-general(at)postgresql(dot)org>
Subject: question on foreign key lock
Date: 2012-11-01 16:33:22
Message-ID: 5092A452.3070602@gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
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


Responses

pgsql-hackers by date

Next:From: John LumbyDate: 2012-11-01 16:37:14
Subject: Re: [PATCH] Prefetch index pages for B-Tree index scans
Previous:From: Kevin GrittnerDate: 2012-11-01 13:45:18
Subject: Re: Estimation of HashJoin Cost

pgsql-general by date

Next:From: Alban HertroysDate: 2012-11-01 16:40:23
Subject: Re: Unexplained Major Vacuum Archive Activity During Vacuum
Previous:From: Vincent VeyronDate: 2012-11-01 16:24:22
Subject: Re: role does not exist

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