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
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 |