1

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.

dragoon
  • 159
  • 2
  • 5
  • 8
  • 1
    Define "getting stuck". [Edit] the question and tell us what you observe, including any messages or errors. –  Dec 20 '18 at 17:59
  • Something is holding a lock on `table_name` - check for long running transactions. – Laurenz Albe Dec 21 '18 at 08:10
  • Does this answer your question? [Avoid exclusive access locks on referenced tables when DROPping in PostgreSQL](https://stackoverflow.com/questions/32145189/avoid-exclusive-access-locks-on-referenced-tables-when-dropping-in-postgresql) – zutnop Jan 22 '21 at 07:03

1 Answers1

4

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';

SivaBala P
  • 56
  • 3