How to delete a table in postgresql

Question

There is a postgresql installation on my server that worked fine so far. However now there is a single table (all other tables work fine) which I cannot open through pgadmin3 or drop.

I've tried restarting the server. Didn't help. I also tried dropping the table with DROP TABLE from the command line on the server. It's just stuck. I've executed the command and it has been just hanging in the console for the past hour.

I don't know what to do. Is there a file I could erase in the data directory perhaps?

Answer 1

Most probably explanation: some other open transaction is holding an exclusive lock on the table.

You are using pgAdmin, so you can check with Tools -> Server Status. The activity pane lists all current connections. For instance, there is one (or more) listings for every open SQL window. Look for long running connections.

You can also try to issue a DROP TABLE and check this list. With any luck you'll see what blocks it. Once you have identified the troublemaker and made sure, it's not needed, you might be able to kill the process. Might be vacuuming gone haywire because of bad settings ..

That, or something is seriously broken.

Answer 2

You could try taking a dump of the database and see if that works? Also have a look at the http://www.postgresql.org/docs/9.1/static/runtime-config-logging.html#GUC-CLIENT-MIN-MESSAGES and log_min_messages options. Change that to debug and see what is happening when you try to drop the table.

Answer 3

In Case of Windows Users. Try restarting Postgresql by follwowing these steps: Start -> Run -> (then type in:) services.msc. Select Local postgresql Server and then press "Restart." Now, go ahead and try deleting the database or the table you wanted to delete (Via pgAdmin). Hope it helps.