CODEWITHSUNDEEP
pythonpostgresqlwxpythonpsycopg2
louis grivel
louis grivel

Reputation: 65

PostgreSQL DROP TABLE query freezes

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

Upvotes: 4

Views: 8004

Answers (2)

matlihan
matlihan

Reputation: 1

I am having the same issue when using psycopg2 within airflow's postgres hook and I resolved it with with statement. Probably this resolves the issue because the connection becomes local within the with statement.

def drop_table():
    with PostgresHook(postgres_conn_id="your_connection").get_conn() as conn:
        cur = conn.cursor()
        cur.execute("DROP TABLE IF EXISTS your_table")

task_drop_table = PythonOperator(
    task_id="drop_table",
    python_callable=drop_table
)

And a solution is possible for the original code above like this (I didn't test this one):

def DeleteTable(table_name):

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

Please comment if anyone tries this.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246578

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.

Upvotes: 10

Related Questions