On Wed, Feb 2, 2011 at 12:20 AM, Noah Misch <noah@xxxxxxxxxxxx> wrote: >> CREATE TABLE account (account_id SERIAL PRIMARY KEY, name CHARACTER VARYING >> NOT NULL); >> CREATE TABLE foo (account_id INTEGER NOT NULL REFERENCES account >> (account_id), stuff CHARACTER VARYING); > >> DROP TABLE foo; > >> That query shows that the DROP takes an AccessExclusiveLock on account. >> This isn't totally unexpected but it is unfortunate because it means we >> have to wait for a downtime window to maintain constraints even if they are >> not really in use. > > PostgreSQL 9.1 will contain changes to make similar operations, though not that > one, take ShareRowExclusiveLock instead of AccessExclusiveLock. Offhand, the > same optimization probably could be arranged for it with minimal fuss. If > "account" is heavily queried but seldom changed, that might be enough for you. The problem is that constraints can affect the query plan. If a transaction sees the constraint in the system catalogs (under SnapshotNow) but the table data doesn't conform (under some earlier snapshot) and if the chosen plan depends on the validity of the constraint, then we've got trouble. At least when running at READ COMMITTED, taking an AccessExclusiveLock protects us against that hazard (I'm not exactly sure what if anything protects us at higher isolation levels... but I hope there is something). Now, it's true that in the specific case of a foreign key constraint, we don't currently have anything in the planner that depends on that. But I'm hoping to get around to working on inner join removal again one of these days. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
