CODEWITHSUNDEEP
postgresqlforeign-keys
dragoon
dragoon

Reputation: 179

Postgres: drop foreign key constraint query getting stuck

I wanted to remove all foreign key constraints from a schema. I was successful in dropping constraints from most of the tables but in few of them drop foreign key constraint query is getting stuck.

ALTER TABLE table_name DROP CONSTRAINT fkey_name;

I tried truncate cascade but it was also getting stuck. I deleted all rows from both the tables manually. Still getting stuck.

Edits: By getting stuck I mean query continues running for long time without any error message even though tables are empty.

Upvotes: 2

Views: 5611

Answers (1)

SivaBala P
SivaBala P

Reputation: 76

Check for any dead locks using

SELECT * FROM pg_stat_activity;

If any then kill and run below sql,and then drop using

SELECT pg_terminate_backend(pid);

If Not solved check for any virtual transaction

SELECT database, gid FROM pg_prepared_xacts;

Rollback using

ROLLBACK PREPARED 'gid';

Upvotes: 6

Related Questions