2

Recently I wanted to drop a FK on table X referencing the userid of the "user" table. The user table is of course very frequently read from, but still I assumed it would be safe to drop the FK without a database downtime. My reasoning was that the user table should not have to be locked while removing the FK from table X.

When executing

ALTER TABLE x drop constraint fk12345;

though, the query took very long, the database load increased significantly and the query had to be aborted.

So my question is: Does removing a FK lock the referenced table? If not, what else might be the explanation for the long duration?

Extra info: The query was run against two postgres9.3 instances running behind pgpool.

  • Well, according to the documentation ALTER TABLE requires an ACCESS EXCLUSIVE lock as noted in the EXPLICIT LOCKING documentation unless otherwise stated in the ALTER TABLE documentation. What's the output of one of the Lock Monitoring queries from the PostgreSQL Wiki? – Kassandry Aug 10 '17 at 6:02
  • Dropping the FK locks the system catalog, not the table, so modifications to the table are blocked until an exclusive lock on the entry in the relevant system table(s) are acquired. The drop command at that point will be near-instantaneous. Dropping a constraint does not require "database downtime", but if your structure is dense enough I guess it could look that way. – bma Aug 11 '17 at 22:28
1

ALTER TABLE x drop constraint fk12345;
take long time because the parent table (user) in transaction(delete,insert update,..) or X table itself in transaction(not commit or rollback), it meant ALTER TABLE x drop constraint fk12345 may not make the User table locked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.