SQLAlchemy + postgres : (InternalError) current transaction is aborted, commands ignored until end of transaction block

Question

I am attempting to save a parent/children set of records, and I want to wrap the inserts in a transaction. I am using SQLAlchemy with postgresql 8.4.

Here is a snippet of my code:

def insert_data(parent, child_rows):
    # Start a transaction
    conn = _get_connection()
    tran = conn.begin()

    try:
        sql = get_sql_from_parent(parent)
        res = conn.execute(sql)  # <- Code barfs at this line
        item = res.fetchone() if res else None
        parent_id = item['id'] if ((item) and ('id' in item)) else -1

        if parent_id == -1:
            raise Exception('Parent could not be saved in database')

        # Import children
        for child in child_rows:
            child_sql = get_child_sql(parent_id, child)                        
            conn.execute(child_sql)

        tran.commit()

    except IntegrityError:
        pass  # rollback?

    except Exception as e:
        tran.rollback()
        print "Exception in user code:"
        print '-'*60
        traceback.print_exc(file=sys.stdout)
        print '-'*60

When I invoke the function, I get the following stacktrace:

Traceback (most recent call last):
  File "import_data.py", line 125, in <module>
    res = conn.execute(sql)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.4-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1405, in execute
    params)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.4-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1582, in _execute_text
    statement, parameters
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.4-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1646, in _execute_context
    context)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.4-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1639, in _execute_context
    context)
  File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.4-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py", line 330, in do_execute
    cursor.execute(statement, parameters)
InternalError: (InternalError) current transaction is aborted, commands ignored until end of transaction block
...

Does anyone know why I am getting this cryptic error - and how do I resolve it?

(I don't read python, but) Your front end program does not need to roll back; after an error the current statement(s) is/are automatically rolled back by the backend (and all cursors are closed) The "transaction" does not exist anymore, but -for convinience- is set to an invalid state, just to remind you to restart a new transaction. There is no other way, since the DBMS cannot guarantee its internal state at this moment. — wildplasser
– wildplasser, Commented Jun 5, 2012 at 11:19

Idaho · Accepted Answer · 2012-06-05 11:53:32Z

1

Can you activate the log query on postgresql ? (min_duration set to 0 in postgresql.conf then restart).

Then look at your postgresql logs to debug it.

answered Jun 5, 2012 at 11:53

Idaho

1,4231 gold badge10 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLAlchemy + postgres : (InternalError) current transaction is aborted, commands ignored until end of transaction block

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related