Why does the error "psycopg.transaction.OutOfOrderTransactionNesting" happens? and how to avoid it?

Question

I'm running a telegram bot using python-telegram-bot==13.13 and psycopg==3.1.4 for database connections. Yesterday this error raised suddenly: psycopg.transaction.OutOfOrderTransactionNesting: transaction commit at the wrong nesting level.

The function is more than a hundred of lines so I'll put only the transaction where the error raised:

Due to the traceback, it happened on the first line here:

with conn.transaction():
    cur.execute(f'update testauth_profile set balance=balance-{price} where telegram_id={update.effective_chat.id}')
    cur.execute(f'update testauth_profile set balance=balance+{price-cost} where id=622')
    cur.execute(f"insert into testauth_log values ('{data['mdn']}','{name}',now(),{context.user_data['uid']},'{data['id']}')")
    cur.execute(f"insert into verifications values ('{data['id']}',now()+interval'15 minute',{update.effective_chat.id},0,'{data['mdn']}',{price-cost},{price},{context.user_data['lang']})")

where conn is a psycopg connection and has autocommit=True. and cur is a cursor from conn.

After that, everything happend on the database in the next 11 hours were rolled back and I lost them permanently.

Any idea for what caused this error? and how can I avoid this in the future?

I searched in google but nothing about this error. I had a look at the postgres logs in /var/logs/postgres/ but there was no errors in the whole 11 hours and even before. The strange thing is that I never touched the code and it has been running for days without any problem. The function where the error was raised, was executed many times before without problems.

To state the obvious, this error happened because of a bug, either in the bot code (more likely) or in the psycopg code (less likely). Like for a car engine failing to start, there could be lots of different reasons for this behaviour, and more data (logs, stack traces) is needed to identify the real problem. — Grisha, Feb 17 '23 at 16:09
1) Show the actual complete code in your question. 2) Having a transaction open for 11 hours is a bad idea period. — Adrian Klaver, Feb 17 '23 at 16:11
Thanks for comments.. I edited the question to add the code snippet. I never open a transaction for 11 hours, I put my multi-line transactions in a `with conn.transaction():` to make sure closing them — Ali_Jamous, Feb 17 '23 at 17:08
I've encountered the same issue with psycopg3 and lost my data for last 20 hours. Any update on this one by any chance? — Exide, Apr 28 '23 at 08:49

score 0 · Answer 1 · edited May 02 '23 at 18:40

All I did for now is combine all consecutive updates and inserts into one query where possible.

For example, I converted the code in my question to:

cur.execute(f'''update testauth_profile set balance=balance-{price} where telegram_id={update.effective_chat.id};
update testauth_profile set balance=balance+{price-cost} where id=622';
insert into testauth_log values ('{data['mdn']}','{name}',now(),{context.user_data['uid']},'{data['id']}');
insert into verifications values ('{data['id']}',now()+interval'15 minute',{update.effective_chat.id},0,'{data['mdn']}',{price-cost},{price},{context.user_data['lang']});''')

In addition to that, I created a new cursor in every callback in the code (the cursor is deleted at the end of the callback). I don't know if this is the solution, but I didn't face the problem again.

Why does the error "psycopg.transaction.OutOfOrderTransactionNesting" happens? and how to avoid it?

1 Answers1