Postgresql DROP TABLE doesn't work

Question

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

what about not doing the CASCADE. perhaps the constraint was referring to the now missing PRODUCT — Randy, Apr 25 '12 at 13:52
"The command line just stalls" What does that mean? `psql` crashes, hangs, or freezes? Do you have to kill it? What does Ctrl-C do? I guess I'm just saying define 'stalls'. Or are you not using psql? — alan, Apr 25 '12 at 13:56
Yes, it freezes. When I kill it I get no apparent errors, just a `ERROR: canceling statement due to user request` message. And yes, I'm using psql. — Radu Gheorghiu, Apr 25 '12 at 13:59
Maybe another transaction has a lock on the table so you cannot drop it? — wildplasser, Apr 25 '12 at 14:01
Is there anything in the postgres log? Probably at: `\var\log\postgresql` — alan, Apr 25 '12 at 14:02
@njzk2 No, I couldn't have. I issued the commands one at a time. — Radu Gheorghiu, Apr 25 '12 at 14:08
@wildplasser It's been 3 years and now I get your "Bill" tables joke. Good one! — Radu Gheorghiu, Jun 26 '15 at 06:48

score 69 · Accepted Answer · answered Apr 25 '12 at 14:04

69

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.

answered Apr 25 '12 at 14:04

vyegorov

21,787
7
59
73

1

`psdemo=> SELECT * from pg_locks l join pg_class t on l.relation = t.oid AND t.relkind = 'r' WHERE t.relname="ps_bill"; ERROR: column "ps_bill" does not exist LINE 1: ...ation = t.oid AND t.relkind = 'r' WHERE t.relname="ps_bill";` – Radu Gheorghiu Apr 25 '12 at 14:07
For a literal value, use single quotes (the apostrophe). PostgreSQL conforms to the SQL standard in treating double-quotes as wrapping an *identifier*. – kgrittn Apr 25 '12 at 14:15
2

Thank you but I managed to fix it with a simple reboot. It's kind of a silly and not a demystifying thing, what I did, but it was the shortest way around the problem. I up voted your answer so you know I appreciate your help. I guess indeed there was a transaction that held a lock on the tables. – Radu Gheorghiu Apr 25 '12 at 14:28
2

Yes , I did manage to get over this issue by simply restarting the postgresql and then dropping the table. – Madhu V Rao May 27 '13 at 00:35

Fancy John · Answer 2 · 2015-11-03T06:56:34.303

26

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

edited Nov 03 '15 at 06:56

answered Oct 29 '15 at 13:32

Fancy John

38,140
3
27
27

Even if it's been more than 3 years since I posted the question, I want to thank you for coming and sharing your solution. But, my question is what would `relname` be? – Radu Gheorghiu Oct 29 '15 at 13:58
1

@RaduGheorghiu relname is actually a table name. – chemikadze Aug 30 '16 at 23:24

score 12 · Answer 3 · answered Jun 11 '20 at 19:17

If this is for AWS postgres run the first statement to get the PID (Process ID) and then run the second query to terminate the process (it would be very similar to do kill -9 but since this is in the cloud that's what AWS recommends)

-- gets you the PID
SELECT pid, relname FROM pg_locks l JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r' WHERE t.relname = 'YOUR_TABLE_NAME'

-- what actually kills the PID ...it is select statement but it kills the job!
SELECT pg_terminate_backend(YOUR_PID_FROM_PREVIOUS_QUERY);

source

score 11 · Answer 4 · answered Dec 30 '13 at 00:57

So I was hitting my head against the wall for some hours trying to solve the same issue, and here is the solution that worked for me:

Check if PostgreSQL has a pending prepared transaction that's never been committed or rolled back:

SELECT database, gid FROM pg_prepared_xacts;

If you get a result, then for each transaction gid you must execute a ROLLBACK from the database having the problem:

ROLLBACK PREPARED 'the_gid';

For further information, click here.

score 7 · Answer 5 · answered Feb 23 '20 at 23:12

7

I ran into this today, I was issuing a:

DROP TABLE TableNameHere

and getting ERROR: table "tablenamehere" does not exist. I realized that for case-sensitive tables (as was mine), you need to quote the table name:

DROP TABLE "TableNameHere"

answered Feb 23 '20 at 23:12

radicand

6,068
3
27
22

score 5 · Answer 6 · answered Sep 28 '13 at 22:52

5

Had the same problem.

There were not any locks on the table.

Reboot helped.

answered Sep 28 '13 at 22:52

alekzvik

71
2
3

2

Same thing for me. I didn't have to reboot, only had to restart postgresql. – John Sampson Sep 14 '14 at 02:59

score 3 · Answer 7 · answered Oct 01 '21 at 23:40

3

The same thing happened for me--except that it was because I forgot the semicolon. face palm

answered Oct 01 '21 at 23:40

KBurchfiel

635
6
15

OMG, you saved me. I was wondering why I'm not getting a message even if I misspell the table name. makes so much sense now. Thank you! – Alexandru DuDu Feb 21 '23 at 11:50

score 2 · Answer 8 · answered Nov 22 '16 at 19:01

Old question but ran into a similar issue. Could not reboot the database so tested a few things until this sequence worked :

truncate table foo;
drop index concurrently foo_something; times 4-5x
alter table foo drop column whatever_foreign_key; times 3x
alter table foo drop column id;
drop table foo;

Postgresql DROP TABLE doesn't work

8 Answers8

Linked