CODEWITHSUNDEEP
pythonpython-3.xpostgresqlpsycopg2
Nikk
Nikk

Reputation: 7891

psycopg2 Update Query broke up my PostgreSQL table

When I run this UPDATE query it breaks up my PostgreSQL table messages. Am no longer able to update the rows or the table so had to create a new table and delete the old one.

cursor.execute('''
        SELECT
            outbounds.messages.oubd_msg_pvt_uuid,
            outbounds.messages.oubd_ord_pvt_uuid,
            outbounds.messages.oubd_cont_pvt_uuid,
            outbounds.messages.oudb_sndr_pvt_uuid,
            outbounds.messages.end_phn_pvt_uuid,
            outbounds.messages.status          
        FROM outbounds.messages   
        WHERE outbounds.messages.status = false
    ''')
    records = cursor.fetchall()

    for row in records:
        print(row['sender'], ' -> ', row['number'], ' = ', row['content'])

        cursor.execute('UPDATE outbounds.messages SET status=true WHERE oubd_msg_pvt_uuid = %s', (row['oubd_msg_pvt_uuid'],))
        print(cursor.rowcount, "Record Updated successfully ")

And while it says that it was updated, in reality the row is not updated.

Am a newb to Python so now sure what I'm doing wrong?

Upvotes: 0

Views: 347

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

It seems you forgot to commit the transaction.

If you start a transaction in a relational database, all data modifications are not persistent until you end the transaction.

There are two ways to end a transaction:

  • COMMIT makes the changes persistent

  • ROLLBACK undoes all changes from the transaction as if it never happened

If you terminate the database session without committing, the transaction is automatically rolled back.

If your UPDATEs hang, there must be a concurrent open transaction that is holding a lock. Find it in pg_stat_activity and kill it with pg_terminate_backend using the pid.

If a restart doesn't take care of the lock, it may be a prepared transaction. Check in pg_prepared_xacts and use ROLLBACK PREPARED to remove it.

If all that does not do the trick, look into pg_stat_activity and check what the session is waiting for.

Upvotes: 3

Related Questions