ROW SHARE vs. ACCESS EXCLUSIVE; UPDATE vs. SELECT ... FOR UPDATE

I encountered an issue today in a system I'm monitoring where a DDL
statement that occurred during a data import caused a pileup. I just
want to see if there's anything to do other than increase available
resources and wait it out or break referential integrity.

Here's the basic setup:

CREATE TABLE referred (
id int PRIMARY KEY
)

CREATE TABLE parent (
id int PRIMARY KEY,
referred_id int NOT NULL REFERENCES referred( id )
)

CREATE TABLE child1 (
foo text
)
INHERITS (
parent
)

CREATE TABLE child2 (
bar text
)
INHERITS (
parent
)

So then what happens is that during an import a series of statements
like UPDATE child1 SET foo = 'foo' WHERE ...

This UPDATE takes a while.

In the meantime, an ALTER TABLE is run on child2 like ALTER TABLE
child2 ADD CONSTRAINT child2_referred_fkey FOREIGN KEY
( referred_id ) REFERENCES referred ( id ), which happens because the
children don't inherit constraints, and new children are occasionally
created during production.

So despite the fact that the UPDATE doesn't actually include
referred_id, it still takes a ROW SHARE lock, which blocks the ACCESS
EXCLUSIVE taken by the DDL.

I wouldn't expect the SELECT ... FOR UPDATE of referential integrity
to be necessary since the UPDATE isn't affecting the column with the
foreign key. Is there any value to be gained by making these foreign
keys DEFERRABLE? Or would the ACCESS EXCLUSIVE still get blocked in
the queueing process?

Right now, it's easiest just to break the referential integrity
between the children and referrred because there's not much risk of
data loss or lack of integrity in this particular application at that
point.

I know that shared row locks are coming in 8.1, and I suspect they
will alleviate this particular problem, but I'm still curious to know
more about why/whether ROW SHARE is being acquired when a foreign key
shouldn't have to be checked.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005