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.
Best Answer
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.