Locking referenced table when creating and dropping tables with foreign key constraints

From: frank joerdens <fiskadoro(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: dev <dev(at)woome(dot)com>
Subject: Locking referenced table when creating and dropping tables with foreign key constraints
Date: 2010-02-01 20:22:38
Message-ID: 7d10d2df1002011222g527b24b9v31aba586321c47ee@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

It seems that whenever I create a new empty table with a foreign key
constraint, the transaction will acquire an exclusive lock on the
referenced table, locking out other writers (not sure if even readers
as well), and I don't quite see why that is necessary if the new
entity does not contain any rows since there is nothing to check or
validate in terms of the presence of values in the referenced column.

This is biting us particularly now (it took the site down for a few
minutes each time for the last couple of days) because we have a
number of tables, and intend to add more, that are partitioned by
date, all of which reference the core "person" table, for which we
make new partitions daily via cron, and there is just no way we can
take the app offline each time. And we will also soon start dropping
them (i.e. removing from the inheritance hierarchy, archiving the
content and then dropping them) automatically on an ongoing basis to
keep the core data set manageable.

In fact, it even looks like the dropping also requires a lock on the
referenced table which makes even less sense to me ...

Am I confused, or is there a way around it? We are on 8.3.7 atm.

Regards,

Frank

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2010-02-01 20:25:41 Re: combine SQL SELECT statements into one
Previous Message Igor Neyman 2010-02-01 19:09:48 Re: combine SQL SELECT statements into one