Обсуждение: Why does FK creation still take an access exclusive lock on the referenced table?

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

Josh Berkus <josh@agliodbs.com> writes:
> I understand why establishing an FK needs an ExclusiveLock on the
> referenced table, but it doesn't need an AccessExclusiveLock.

Yes, it does, because it's adding triggers.  We can't improve this
in the near term, and complaining about it doesn't change that.

At the very least we need MVCC catalog scans before we can think
about reducing lock levels for DDL.  I forget whether there are any
other issues...

            regards, tom lane