Обсуждение: Why does FK creation still take an access exclusive lock on the referenced table?
Why does FK creation still take an access exclusive lock on the referenced table?
От
Josh Berkus
Дата:
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