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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
