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