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™ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 |
