PostgreSQL DROP TABLE query freezes

Question

I am writing code to create a GUI in Python on the Spyder environment of Anaconda. within this code I operate with a PostgreSQL database and I therefore use the psycopg2 database adapter so that I can interact with directly from the GUI.

The code is too long to post here, as it is over 3000 lines, but to summarize, I have no problem interacting with my database except when I try to drop a table.

When I do so, the GUI frames become unresponsive, the drop table query doesn't drop the intended table and no errors or anything else of that kind are thrown.

Within my code, all operations which result in a table being dropped are processed via a function (DeleteTable). When I call this function, there are no problems as I have inserted several print statements previously which confirmed that everything was in order. The problem occurs when I execute the statement with the cur.execute(sql) line of code.

Can anybody figure out why my tables won't drop?

def DeleteTable(table_name):

    conn=psycopg2.connect("host='localhost' dbname='trial2' user='postgres'   password='postgres'")
    cur=conn.cursor()
    sql="""DROP TABLE """+table_name+""";"""
    cur.execute(sql)
    conn.commit()

Answer 1

That must be because a concurrent transaction is holding a lock that blocks the DROP TABLE statement.

Examine the pg_stat_activity view and watch out for sessions with state equal to idle in transaction or active that have an xact_start of more than a few seconds ago.

This is essentially an application bug: you must make sure that all transactions are closed immediately, otherwise Bad Things can happen.