Why does FK creation still take an access exclusive lock on the referenced table? - Mailing list pgsql-bugs

Folks,

I'd thought this was fixed with some of the ALTER changes in 9.2, but
apparently not (9.2.2):

SESSION1:

sampledata=# create table test1 ( id int not null primary key );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test1_pkey" for table "test1"
CREATE TABLE
Time: 55.224 ms       ^
sampledata=# create table test2 ( id int not null primary key, test1 int );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test2_pkey" for table "test2"
CREATE TABLE
Time: 40.841 ms
sampledata=# insert into test1 select i from generate_series(1,10) as gs(i);
sampledata=# begin;
BEGIN
Time: 0.088 ms
sampledata=# alter table test2 add constraint test1_fk foreign key (
test1 ) references test1(id);
ALTER TABLE
Time: 2.185 ms
sampledata=#

SESSION2:

sampledata=# select * from test1;
... wait forever
^C

sampledata=# select locktype, mode, relname from pg_locks join pg_class
on relation=oid;
 locktype |        mode         |          relname
----------+---------------------+----------------------------
 relation | AccessShareLock     | test1_pkey
 relation | AccessShareLock     | test2_pkey
 relation | AccessShareLock     | pg_class_relname_nsp_index
 relation | AccessShareLock     | pg_class_oid_index
 relation | AccessShareLock     | pg_class
 relation | AccessShareLock     | pg_locks
 relation | AccessShareLock     | test2
 relation | AccessExclusiveLock | test2
 relation | AccessShareLock     | test1
 relation | RowShareLock        | test1
 relation | AccessExclusiveLock | test1

I understand why establishing an FK needs an ExclusiveLock on the
referenced table, but it doesn't need an AccessExclusiveLock.  This
causes lots of deployment issues for users.


--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com