[Django]-Postgresql DROP TABLE doesn't work

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

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

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.

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"

Had the same problem.

There were not any locks on the table.

Reboot helped.

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

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;