weird connection problems running many sequential webtests with psycopg2

[Iain Duncan]

I'm hoping someone has had a similar issue, 'cause I'm at head banging on desk stage here. 

I have some functional tests for a pyramid+sqlalchemy app, using webtest, in which I make an engine and make a couple of session that get used in addition to the webtest app. I close the sessions in the tearDown method after each test. 

Now that the number of functional tests is over about 100, they have started failing, but *only* when I run the whole mess together. Run file by file, they are all fine. 

The error I'm getting is pasted below. I'm running stock postgres on os x (installed from the universal binary, no special tweaking).

I've tried sqlalchemy engine settings, changing pool_size to 20 and max_overflow to -1 but that doesn't seem to make a difference. I'm wondering if something in webtest is not releasing the connection properly? In the actual app, my db session gets closed in a request end of lifecycle callback. We haven't had this happen running it, but mind you we haven't had heavy loads yet either.

Any clues would be lovely!

thanks

iain

traceback:

src/warp.test/warp/test/functional.py:70: in setUp

    self.init_db()

src/warp.test/warp/test/functional.py:51: in init_db

    cls.engine.execute("drop owned by alimetrix")

env/lib/python2.7/site-packages/sqlalchemy/engine/base.py:1990: in execute

    connection = self.contextual_connect(close_with_result=True)

env/lib/python2.7/site-packages/sqlalchemy/engine/base.py:2039: in contextual_connect

    self._wrap_pool_connect(self.pool.connect, None),

env/lib/python2.7/site-packages/sqlalchemy/engine/base.py:2078: in _wrap_pool_connect

    e, dialect, self)

env/lib/python2.7/site-packages/sqlalchemy/engine/base.py:1405: in _handle_dbapi_exception_noconnection

    exc_info

env/lib/python2.7/site-packages/sqlalchemy/util/compat.py:202: in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb, cause=cause)

env/lib/python2.7/site-packages/sqlalchemy/engine/base.py:2074: in _wrap_pool_connect

    return fn()

env/lib/python2.7/site-packages/sqlalchemy/pool.py:376: in connect

    return _ConnectionFairy._checkout(self)

env/lib/python2.7/site-packages/sqlalchemy/pool.py:713: in _checkout

    fairy = _ConnectionRecord.checkout(pool)

env/lib/python2.7/site-packages/sqlalchemy/pool.py:480: in checkout

    rec = pool._do_get()

env/lib/python2.7/site-packages/sqlalchemy/pool.py:1060: in _do_get

    self._dec_overflow()

env/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py:60: in __exit__

    compat.reraise(exc_type, exc_value, exc_tb)

env/lib/python2.7/site-packages/sqlalchemy/pool.py:1057: in _do_get

    return self._create_connection()

env/lib/python2.7/site-packages/sqlalchemy/pool.py:323: in _create_connection

    return _ConnectionRecord(self)

env/lib/python2.7/site-packages/sqlalchemy/pool.py:449: in __init__

    self.connection = self.__connect()

env/lib/python2.7/site-packages/sqlalchemy/pool.py:607: in __connect

    connection = self.__pool._invoke_creator(self)

env/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py:97: in connect

    return dialect.connect(*cargs, **cparams)

env/lib/python2.7/site-packages/sqlalchemy/engine/default.py:385: in connect

    return self.dbapi.connect(*cargs, **cparams)

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

dsn = 'dbname=alimetrix_test user=alimetrix password=alimetrix host=localhost', database = 'alimetrix_test', user = 'alimetrix', password = 'alimetrix', host = 'localhost', port = None

connection_factory = None, cursor_factory = None, async = False, kwargs = {}, items = [('dbname', 'alimetrix_test'), ('user', 'alimetrix'), ('password', 'alimetrix'), ('host', 'localhost')]

k = 'host', v = 'localhost'

    def connect(dsn=None,

            database=None, user=None, password=None, host=None, port=None,

            connection_factory=None, cursor_factory=None, async=False, **kwargs):

        """

        Create a new database connection.

        The connection parameters can be specified either as a string:

            conn = psycopg2.connect("dbname=test user=postgres password=secret")

        or using a set of keyword arguments:

            conn = psycopg2.connect(database="test", user="postgres", password="secret")

        The basic connection parameters are:

        - *dbname*: the database name (only in dsn string)

        - *database*: the database name (only as keyword argument)

        - *user*: user name used to authenticate

        - *password*: password used to authenticate

        - *host*: database host address (defaults to UNIX socket if not provided)

        - *port*: connection port number (defaults to 5432 if not provided)

        Using the *connection_factory* parameter a different class or connections

        factory can be specified. It should be a callable object taking a dsn

        argument.

        Using the *cursor_factory* parameter, a new default cursor factory will be

        used by cursor().

        Using *async*=True an asynchronous connection will be created.

        Any other keyword parameter will be passed to the underlying client

        library: the list of supported parameters depends on the library version.

        """

        items = []

        if database is not None:

            items.append(('dbname', database))

        if user is not None:

            items.append(('user', user))

        if password is not None:

            items.append(('password', password))

        if host is not None:

            items.append(('host', host))

        if port is not None:

            items.append(('port', port))

        items.extend([(k, v) for (k, v) in kwargs.iteritems() if v is not None])

        if dsn is not None and items:

            raise TypeError(

                "'%s' is an invalid keyword argument when the dsn is specified"

                    % items[0][0])

        if dsn is None:

            if not items:

                raise TypeError('missing dsn and no parameters')

            else:

                dsn = " ".join(["%s=%s" % (k, _param_escape(str(v)))

                    for (k, v) in items])

>       conn = _connect(dsn, connection_factory=connection_factory, async=async)

E       OperationalError: (psycopg2.OperationalError) FATAL:  remaining connection slots are reserved for non-replication superuser connections

[Vincent Catalano]

It definitely sounds like your connection sessions are not being closed properly. Perhaps the way WebTest handles thread locals is interfering with the connection pooling? I could be way off on this but are you calling the pyramid.testing.setUp and pyramid.testing.tearDown functions in your setUp and tearDown functions? (http://docs.pylonsproject.org/projects/pyramid/en/latest/narr/testing.html#test-set-up-and-tear-down)

--
You received this message because you are subscribed to the Google Groups "pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discuss+unsubscribe@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/CAN9NcLxA43yhdw3gHP-DtwbOYyYsWn5CHYBnqJyeLkzWqjM02A%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

--

Vincent Catalano
Software Engineer and Web Ninja,
(520).603.8944

[Iain Duncan]

Solved! Mike Bayer recommended adding a call to engine.dispose() to tearDownClass and that fixed it. Apparently the garbage collector is imperfect for disposing of engines.

thanks for the help!

To post to this group, send email to pylons-...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/CAN9NcLxA43yhdw3gHP-DtwbOYyYsWn5CHYBnqJyeLkzWqjM02A%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

--

Vincent Catalano
Software Engineer and Web Ninja,
(520).603.8944

--
You received this message because you are subscribed to the Google Groups "pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discuss+unsubscribe@googlegroups.com.
To post to this group, send email to pylons-discuss@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/CAEhnOsyqRvrM5dn_cBO1Ywepr2%3Dg670%3DvJ9UXoiocQTnu0onog%40mail.gmail.com.