Why does FK creation still take an access exclusive lock on the referenced table?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Why does FK creation still take an access exclusive lock on the referenced table?
Date: 2013-02-07 18:06:40
Message-ID: 5113ED30.1070903@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2013-02-07 18:12:58 Re: Why does FK creation still take an access exclusive lock on the referenced table?
Previous Message Ivano Luberti 2013-02-06 21:30:22 Re: SOLVED: BUG #7851: Installer crash with message: An error occured executing the Microsoft VC++ runtimeinstaller