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
– Randy, Commented Apr 25, 2012 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
– alan, Commented Apr 25, 2012 at 13:56
Maybe another transaction has a lock on the table so you cannot drop it? — wildplasser
– wildplasser, Commented Apr 25, 2012 at 14:01

vyegorov · Accepted Answer · 2012-04-25 14:04:33Z

71

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, 2012 at 14:04

vyegorov

23k7 gold badges61 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Radu Gheorghiu Over a year ago

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

kgrittn Over a year ago

For a literal value, use single quotes (the apostrophe). PostgreSQL conforms to the SQL standard in treating double-quotes as wrapping an identifier.

Radu Gheorghiu Over a year ago

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.

Madhu V Rao Over a year ago

Yes , I did manage to get over this issue by simply restarting the postgresql and then dropping the table.

Fancy John · Accepted Answer · 2015-11-03 06:56:34Z

28

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 3, 2015 at 6:56

answered Oct 29, 2015 at 13:32

Fancy John

39.7k4 gold badges30 silver badges27 bronze badges

2 Comments

Radu Gheorghiu Over a year ago

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?

chemikadze Over a year ago

@RaduGheorghiu relname is actually a table name.

grepit · Accepted Answer · 2020-06-11 19:17:29Z

19

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

answered Jun 11, 2020 at 19:17

grepit

22.6k6 gold badges109 silver badges82 bronze badges

Comments

Thomas C. G. de Vilhena · Accepted Answer · 2013-12-30 00:57:50Z

11

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.

answered Dec 30, 2013 at 0:57

Thomas C. G. de Vilhena

14.7k3 gold badges58 silver badges45 bronze badges

Comments

radicand · Accepted Answer · 2020-02-23 23:12:53Z

9

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, 2020 at 23:12

radicand

6,7073 gold badges30 silver badges24 bronze badges

Comments

KBurchfiel · Accepted Answer · 2021-10-01 23:40:33Z

7

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

answered Oct 1, 2021 at 23:40

KBurchfiel

9741 gold badge9 silver badges19 bronze badges

1 Comment

Alexandru DuDu Over a year ago

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!

alekzvik · Accepted Answer · 2013-09-28 22:52:09Z

5

Had the same problem.

There were not any locks on the table.

Reboot helped.

answered Sep 28, 2013 at 22:52

alekzvik

712 silver badges3 bronze badges

1 Comment

John Sampson Over a year ago

Same thing for me. I didn't have to reboot, only had to restart postgresql.

kert · Accepted Answer · 2016-11-22 19:01:25Z

2

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;

answered Nov 22, 2016 at 19:01

kert

2,30122 silver badges23 bronze badges

Comments

Ernandes Junior · Accepted Answer · 2025-02-25 21:28:23Z

0

This happened to me.

What I noticed is that there were processes running that simply wouldn't let me execute DROP TABLE or ALTER TABLE on the PK or FK.

First I did:

SELECT * FROM pg_stat_activity

I didn't filter by just the active ones because the ones that were blocking were in "idle" status

And then I killed those processes:

SELECT pg_cancel_backend(<pid of the process>)

I killed all the processes that were running for that database. I made sure that this wouldn't affect my application.

If it still doesn't work, try:

  SELECT pg_terminate_backend(<pid of the process>)

Link : How to stop/kill a query in postgresql?

edited Feb 25 at 21:28

answered Feb 25 at 15:02

Ernandes Junior

735 bronze badges

Collectives™ on Stack Overflow

Postgresql DROP TABLE doesn't work

9 Answers 9

4 Comments

2 Comments

Comments

Comments

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

4 Comments

2 Comments

Comments

Comments

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related