Connection.execute in separate thread closes raw connection

[Lukas Siemon]

After spending a few hours on this I decided to write a minimal test case.

I don't understand why/how the underlying connection get's closed here.

Would be very grateful if you could take a quick look at this. Thank you!

Error:

Error
Traceback (most recent call last):
  File ".../tmp.py", line 48, in test_raw_connection_gets_closed
    print con.execute("SELECT version();").scalar()
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 906, in execute
    return self._execute_text(object, multiparams, params)
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1054, in _execute_text
    statement, parameters
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1078, in _execute_context
    None, None)
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1071, in _execute_context
    conn = self._revalidate_connection()
  File ".../env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 396, in _revalidate_connection
    raise exc.ResourceClosedError("This Connection is closed")
StatementError: (sqlalchemy.exc.ResourceClosedError) This Connection is closed [SQL: u'SELECT version();']

Test Case:

import unittest
from multiprocessing import Pool

from sqlalchemy.ext.declarative import declarative_base
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = (
    "postgres://postgres:password@localhost:5432/tmp")
db = SQLAlchemy(app)

Base = declarative_base()

Base.metadata.drop_all(bind=db.engine)
Base.metadata.create_all(bind=db.engine)

connections = []


def pinger(no):
    version = connections[no].execute("SELECT version();").scalar()
    return "PostgreSQL" in version


class TestCase(unittest.TestCase):

    def test_raw_connection_gets_closed(self):

        con = db.engine.connect()
        print "Version: "
        print con.execute("SELECT version();").scalar()

        global connections
        connections = [con]

        pool = Pool(1)
        results = pool.map(pinger, [0])
        print results

        # this fixes the issue:
        # con.invalidate()

        # does not fix the issue
        # con.close()

        print "Version: "
        print con.execute("SELECT version();").scalar()

[Mike Bayer]

so to clarify, "multiprocessing" does not use threads (as the primary worker at least), it uses processes. So here you're taking a TCP filehandle (the postgresql connection) and having two separate processes make use of it. Assuming the other process ends before the main one, Python garbage collection will result in that connection being closed and the server is then considering that connection to be closed.

http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html?highlight=multiprocessing#basic-usage mentions this:

For a multiple-process application that uses the os.fork system call, or for example the Python multiprocessing module, it’s usually required that a separate Engine be used for each child process. This is because the Engine maintains a reference to a connection pool that ultimately references DBAPI connections - these tend to not be portable across process boundaries. An Engine that is configured not to use pooling (which is achieved via the usage of NullPool) does not have this requirement.

[Mike Bayer]

more details: http://docs.sqlalchemy.org/en/rel_1_0/core/pooling.html#using-connection-pools-with-multiprocessing

[Lukas Siemon]

Ahhhhh... that makes so much sense! Thank you so much for your help!

Best resolution I found was to use threads instead of processed, i.e.:

from multiprocessing.pool import ThreadPool