I'm trying to drop a few tables with the
"DROP TABLE" command but for a unknown reason, the program just "sits" and doesn't delete the table that I want it to in the database.
I have 3 tables in the database:
Product, Bill and Bill_Products which is used for referencing products in bills.
I managed to delete/drop Product, but I can't do the same for bill and Bill_Products.
I'm issuing the same
"DROP TABLE Bill CASCADE;" command but the command line just stalls. I've also used the simple version without the
Do you have any idea why this is happening?
I've been thinking that it is possible for the databases to keep some references from products to bills and maybe that's why it won't delete the Bill table.
So, for that matter i issued a simple
SELECT * from Bill_Products and after a few (10-15) seconds (strangely, because I don't think it's normal for it to last such a long time when there's an empty table) it printed out the table and it's contents, which are none. (so apparently there are no references left from Products to Bill).
What is the output of
SELECT * FROM pg_locks l JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r' WHERE t.relname = 'Bill';
It might be that there're other sessions using your table in parallel and you cannot obtain Access Exclusive lock to drop it.
SELECT pid, relname FROM pg_locks l JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r' WHERE t.relname = 'Bill';
And then kill every pid by
Where 1234 is your actual pid from query results.
You can pipe it all together like this (so you don't have to copy-paste every pid manually):
psql -c "SELECT pid FROM pg_locks l JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r' WHERE t.relname = 'Bill';" | tail -n +3 | head -n -2 | xargs kill