[sqlalchemy] Rollback raises "connection already closed"

[Wolfgang Schnerring]

Hello,

I'm seeing a somewhat puzzling traceback, in this scenario:

- The database connection goes away (due to networking issues)
- A transaction is committed
- This fails (of course), thus tries to rollback
- The rollback fails, too, (of course) complaining that the
"connection already closed". Is that intentional?

The full traceback is attached, as well as a minimal reproduction test
case using tcpwatch to interrupt the connection. (Tested with postgres
9.1)

While I realize that on the one hand, yes, the rollback failed so
sqlalchemy should complain, I feel on the other hand that this doesn't
really help me much from a pragmatic point of view: since the commit
didn't go through, there should be no harm in the rollback failing
(right?), so why complain?

Thanks for any insights,
Wolfgang

--
Wolfgang Schnerring · w...@gocept.com · Software development
gocept gmbh & co. kg · Forsterstraße 29 · 06112 Halle (Saale) · Germany
http://gocept.com · Tel +49 345 219401-0
Python, Pyramid, Plone, Zope · consulting, development, hosting, operations

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.


Traceback (most recent call last):
  File "/usr/local/python2.7/lib/python2.7/unittest/case.py", line 318, in run
    testMethod()
  File "/home/wosc/tmp/test_rollback.py", line 39, in 
test_disconnected_database_should_rollback_cleanly
    session.commit()
  File "/var/cache/eggs/SQLAlchemy-0.6.7-py2.7.egg/sqlalchemy/orm/session.py", 
line 614, in commit
    self.transaction.commit()
  File "/var/cache/eggs/SQLAlchemy-0.6.7-py2.7.egg/sqlalchemy/orm/session.py", 
line 385, in commit
    self._prepare_impl()
  File "/var/cache/eggs/SQLAlchemy-0.6.7-py2.7.egg/sqlalchemy/orm/session.py", 
line 376, in _prepare_impl
    self.rollback()
  File "/var/cache/eggs/SQLAlchemy-0.6.7-py2.7.egg/sqlalchemy/orm/session.py", 
line 411, in rollback
    transaction._rollback_impl()
  File "/var/cache/eggs/SQLAlchemy-0.6.7-py2.7.egg/sqlalchemy/orm/session.py", 
line 424, in _rollback_impl
    t[1].rollback()
  File "/var/cache/eggs/SQLAlchemy-0.6.7-py2.7.egg/sqlalchemy/engine/base.py", 
line 1524, in rollback
    self._do_rollback()
  File "/var/cache/eggs/SQLAlchemy-0.6.7-py2.7.egg/sqlalchemy/engine/base.py", 
line 1595, in _do_rollback
    self.connection._rollback_twophase_impl(self.xid, self._is_prepared)
  File "/var/cache/eggs/SQLAlchemy-0.6.7-py2.7.egg/sqlalchemy/engine/base.py", 
line 1133, in _rollback_twophase_impl
    self.engine.dialect.do_rollback_twophase(self, xid, is_prepared)
  File 
"/var/cache/eggs/SQLAlchemy-0.6.7-py2.7.egg/sqlalchemy/dialects/postgresql/base.py",
 line 856, in do_rollback_twophase
    self.do_rollback(connection.connection)
  File 
"/var/cache/eggs/SQLAlchemy-0.6.7-py2.7.egg/sqlalchemy/engine/default.py", line 
268, in do_rollback
    connection.rollback()
InterfaceError: connection already closed

import os
import signal
import sqlalchemy
import sqlalchemy.orm
import subprocess
import sys
import tempfile
import time
#import tcpwatch
import unittest


class RollbackTest(unittest.TestCase):

    def test_disconnected_database_should_rollback_cleanly(self):
        # This test needs a postgres database, like this:
        # REAL_DSN = 'postgresql://localhost/foo'
        #
        # We then connect via a TCP proxy, so we can interrupt the connection
        orig_port = 5432
        proxy_port = 10432
        pid = self.tcpwatch(proxy_port, orig_port)
        PROXY_DSN = 'postgresql://localhost:%s/foo' % proxy_port

        engine = sqlalchemy.create_engine(PROXY_DSN)
        session_factory = sqlalchemy.orm.sessionmaker(
                bind=engine, twophase=True, autoflush=False)
        session = session_factory()
        session.execute('CREATE TABLE IF NOT EXISTS example (foo INTEGER)')

        # do some work
        session.execute("INSERT INTO example (foo) VALUES (37)")

        # interrupt database connection
        os.kill(pid, signal.SIGINT)
        self.pid = self.tcpwatch(proxy_port, orig_port)

        # now commit work from above; this should rollback cleanly
        session.commit()
        self.assertEqual(
            0, session.execute('SELECT count(*) FROM example').fetchone()[0])

    def tcpwatch(self, src, dest):
        script = tempfile.NamedTemporaryFile(suffix='.py')
        script.write("""
import sys
sys.path[:] = %(path)r
import tcpwatch
tcpwatch.main(['-L', '%(src)s:%(dest)s', '-s'])
        """ % dict(path=sys.path, src=src, dest=dest))
        script.flush()
        process = subprocess.Popen(
            [sys.executable, script.name],
            stdout=open('/dev/null', 'w'), stderr=subprocess.STDOUT)
        time.sleep(0.5)
        return process.pid