Re: ADD FOREIGN KEY locking - Mailing list pgsql-hackers

Hello all,

When I add a FK with a statement like this:

ALTER TABLE a ADD FOREIGN KEY (id) REFERENCES b(id);

I see a lock on table b:

select locktype,mode,granted from pg_locks, pg_stat_activity where relation::regclass::text = 'b' AND pg_locks.pid = pg_stat_activity.pid;

locktype | relation

mode     | AccessShareLock

granted  | t

query     | SOME LONG RUNNING QUERY WHICH SELECTS FROM b

locktype | relation

mode     | AccessExclusiveLock

granted  | f

query     | ALTER TABLE a ADD FOREIGN KEY (id) REFERENCES b(id);

This means that my add key won't complete until my long running query does. That seems a bit odd to me? In this database there are lots of datawarehouse type queries running, which makes it a bit hard for me to schedule this operation.

Is this just a manifestation of adding the key being in an ALTER TABLE, which always needs an AccessExclusiveLock? Or am I missing some edge case when this lock would be required in this circumstance?

No real urgency on this question, I just found it a bit strange and thought someone might be able to shed some light.

James Sewell,
Solutions Architect
______________________________________
 

Level 2, 50 Queen St, Melbourne VIC 3000

P (+61) 3 8370 8000  W www.lisasoft.com  F (+61) 3 8370 8099