Postgres savepoint and try/catch
I ran into a simple, but necessary bit of postgres knowledge this week while using a bloom filter to purge outdated database entries. The problem I encountered was within a try/catch block. I was catching a ForeignKeyError if the deletion of a row failed. Since I was doing this transaction within a cursor, I would get an error along the lines of: "Current transaction is aborted, commands ignored until end of transaction block"
The fix
Fortunately, to fix this you just add a savepoint within the try block to rollback to in case of an exception. I’ll give a bit of an example.
query = 'declare myc cursor for select * from tableToCheck'You need to declare a cursor name (in this case ‘myc’) for the transaction to use.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| def purge_filter(postgres_conn, query, del_query, bloom): | |
| ''' pass in an initialized bloom filter | |
| and the queries for a smooth postgres transaction''' | |
| def cursor_function(cursor) | |
| cursor.execute('BEGIN') | |
| cursor.execute(query) | |
| while True: | |
| cursor.execute('fetch myc') | |
| row = cursor.fetchall() | |
| if not row: | |
| break | |
| _id = row[0] | |
| if _id not in bloom: | |
| try: | |
| # set the savepoint for rollback in case of exception | |
| cursor.execute('savepoint s1') | |
| cursor.execute(del_query, (_id,)) # id to be deleted | |
| except ForeignKeyError: | |
| cursor.execute('rollback to savepoint s1') | |
| cursor.execute('COMMIT') | |
| # pass the function to psycopg2 to run the transaction | |
| postgres_conn.run(cursor_function) |