处理psycopg2.InternalError：事务中止且需保留cursor的解决方案咨询

Let's break down how to solve this common psycopg2 issue, especially since you can't use the with statement (need to keep the cursor alive for reuse) and want to avoid the broad impact of autocommit.

First, why this error happens #

When a query fails in a transaction, psycopg2 puts the transaction in a "failed" state. All subsequent commands get blocked until you explicitly roll back the transaction to reset it. That's exactly what the error message current transaction is aborted, commands ignored until end of transaction block is telling you.

The rollback + retry implementation you need #

Since you're building a Django-like get() method and need to keep the cursor open, here's a practical, reusable approach that catches the specific error, rolls back the transaction, and retries the operation:

import psycopg2
from psycopg2 import InternalError

def django_like_get(conn, cursor, query, params, max_retries=2):
    retry_attempts = 0
    while retry_attempts <= max_retries:
        try:
            # Execute your query (works perfectly for SELECT operations like a get() method)
            cursor.execute(query, params)
            return cursor.fetchone()
        except InternalError as err:
            # Check if this is exactly the transaction abort error we care about
            error_msg = str(err)
            if "current transaction is aborted, commands ignored until end of transaction block" in error_msg:
                # Reset the transaction with rollback (this must be called on the connection, not the cursor)
                conn.rollback()
                retry_attempts += 1
                if retry_attempts > max_retries:
                    raise RuntimeError(f"Failed after {max_retries} retries: {err}") from err
            else:
                # Not the transaction abort error? Re-raise it to handle elsewhere
                raise
        # Catch other exceptions (like connection issues) as needed for your use case
        except Exception as err:
            raise

Key details to keep in mind #

• Rollback on the connection: Always use conn.rollback()—transaction state is managed by the connection, not the cursor itself.
• Targeted error checking: We explicitly match the error message to avoid catching other InternalError cases that aren't related to transaction aborts.
• Retry limits: The max_retries parameter prevents infinite loops if the error persists (e.g., a broken query that will always fail).
• Cursor reuse: After rolling back, the cursor's state is reset automatically, so you can keep using it for subsequent operations without closing it.
• Idempotency: This approach is safest for read operations (like your get() method), since retries won't cause unintended side effects. If you were modifying data, you'd need to ensure the operation is safe to repeat.

Why we're skipping other solutions #

• with statement: Automatically closes the cursor/connection when exiting the block, which conflicts with your need to keep the cursor alive for reuse.
• autocommit = True: Forces every single command to commit immediately, breaking transactional integrity for multi-step operations and being overkill for fixing just this specific error.

内容的提问来源于stack exchange，提问作者CodeUnsolved