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

Solution 1

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.

Solution 2

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

Solution 3

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.

Solution 4

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

Solution 5

Had the same problem.

There were not any locks on the table.

Reboot helped.

View more solutions

Comments

[Randy]

what about not doing the CASCADE. perhaps the constraint was referring to the now missing PRODUCT

[Radu Gheorghiu]

Tried without that too, but no effect.

[alan]

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

[Radu Gheorghiu]

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.

[wildplasser]

Maybe another transaction has a lock on the table so you cannot drop it?

[Radu Gheorghiu]

Good idea. I assume a 'reboot' would fix it, could it?

[Admin]

About how many rows does this table have?

[alan]

Is there anything in the postgres log? Probably at: \var\log\postgresql

[Radu Gheorghiu]

@JackManey Bill should have 1 million rows.

[wildplasser]

Rebooting only works for tables named "Bill" ;-)

[njzk2]

did you tried to drop both at the same time?

[Radu Gheorghiu]

@njzk2 No, I couldn't have. I issued the commands one at a time.

[Radu Gheorghiu]

@wildplasser It was a great hint! Thanks!

[Radu Gheorghiu]

@wildplasser It's been 3 years and now I get your "Bill" tables joke. Good one!

[Radu Gheorghiu]

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]

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]

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]

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

[John Sampson]

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

[Radu Gheorghiu]

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]

@RaduGheorghiu relname is actually a table name.