On Wed, Feb 2, 2011 at 12:20 AM, Noah Misch <noah@leadboat.com> 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