Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgresql DROP TABLE doesn't work

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 CASCADE option.

Do you have any idea why this is happening?

Update:

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).

like image 796
Radu Gheorghiu Avatar asked Apr 25 '12 13:04

Radu Gheorghiu


People also ask

How do I drop a table in PostgreSQL?

PostgreSQL has a DROP TABLE statement that is used to remove an existing table or tables from the database. Syntax: DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT]; Let's analyze the above syntax: We specify the table name after the DROP TABLE keyword to remove the table permanently from the database.

Are there restrictions on dropping tables?

Restriction on Dropping Tables You cannot directly drop the storage table of a nested table. Instead, you must drop the nested table column using the ALTER TABLE ... DROP COLUMN clause. Specify CASCADE CONSTRAINTS to drop all referential integrity constraints that refer to primary and unique keys in the dropped table.

What is drop cascade in PostgreSQL?

The CASCADE option allows you to remove the table and its dependent objects. The RESTRICT option rejects the removal if there is any object depends on the table. The RESTRICT option is the default if you don't explicitly specify it in the DROP TABLE statement.

Does dropping a table drop the index Postgres?

DROP TABLE removes tables from the database. Only the table owner, the schema owner, and superuser can drop a table. To empty a table of rows without destroying the table, use DELETE or TRUNCATE . DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table.


2 Answers

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.

like image 110
vyegorov Avatar answered Oct 03 '22 01:10

vyegorov


Just do

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

kill 1234 

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 
like image 27
Fancy John Avatar answered Oct 03 '22 00:10

Fancy John