Twisted wrapper for asynchronous PostgreSQL connections

Jan Urbański

Last update: Apr 22, 2022

Related tags

ORM txpostgres

Overview

This is txpostgres is a library for accessing a PostgreSQL database from the Twisted framework. It builds upon asynchronous features of the Psycopg database library, which in turn exposes the asynchronous features of libpq, the PostgreSQL C library.

It requires a version of Psycopg that includes support for asynchronous connections (versions 2.2.0 and later) and a reasonably recent Twisted (it has been tested with Twisted 10.2 onward). Alternatively, psycopg2cffi or psycopg2-ctypes can be used in lieu of Psycopg.

txpostgres tries to present an interface that will be familiar to users of both Twisted and Psycopg. It features a Cursor wrapper class that mimics the interface of a Psycopg cursor but returns Deferred objects. It also provides a Connection class that is meant to be a drop-in replacement for Twisted's adbapi.Connection with some small differences regarding connection establishing.

The main advantage of txpostgres over Twisted's built-in database support is non-blocking connection building and complete lack of thread usage.

It runs on Python 2.6, 2.7, 3.4, 3.5 and PyPy.

If you got txpostgres as a source tarball, you can run the automated test suite and install the library with:

tar xjf txpostgres-x.x.x.tar.bz2
cd txpostgres-x.x.x
trial test
sudo python setup.py install

Alternatively, just install it from PyPI:

pip install txpostgres

The library is distributed under the MIT License, see the LICENSE file for details. You can contact the author, Jan Urbański, at [email protected]. Feel free to download the source, file bugs in the issue tracker and consult the documentation

Comments

Connection.connectionFactory and cursorFactory are treated as methods of Connection

in txpostgres.py:248 you have:

class Connection(_PollingMixin):
    ...
    connectionFactory = psycopg2.connect
    cursorFactory = Cursor

now later in that file in Connection.connect you have:

            self._connection = self.connectionFactory(*args, **kwargs)

The problem there is that, on Python 2.7 at least, self.connectionFactory is treated as a bound method of Connection, so self is passed in implicitly, causing psycopg2.connect to error out:

Traceback (most recent call last):
Failure: twisted.internet.defer.FirstError: FirstError[#0, [Failure instance: Traceback: <type 'exceptions.TypeError'>: argument 1 must be string, not Connection
    [snip]
--- <exception caught here> ---
/Users/erik.allik/.virtualenvs/dts-agent/lib/python2.7/site-packages/txpostgres/txpostgres.py:293:connect
/Users/erik.allik/.virtualenvs/dts-agent/lib/python2.7/site-packages/psycopg2/__init__.py:179:connect
]]

opened by erikkaplun 14

Implementation of ReconnectingConnectionPool ?

Hi,

I'm a newbie with txpostgres, but a feature is missing for me, it's an automatic reconnection pool, as described here for adbapi: http://www.gelens.org/2009/09/13/twisted-connectionpool-revisited/

From my point of view, it's an important feature in a daemon, I don't understand isn't standard in adbapi.

With txpostgres, I've read some discussions about this in #18 and #20 but nothing to implement this directly inside txpostgres.

Why it isn't implemented in txpostgres ? Lack of time to do this, or it's a mistake to implement a generic reconnecting connection pool ?

Thanks for your answer.

Regards.

opened by GMLudo 11

Unhandled error after `connect()` fails

I suspect that my initial comments on the issue might not be valid anymore; my most recent comment below should provide a clearer and more correct description of the issue

I have some DB wrapper code that is handling DB restarts (i.e. disconnects) by continuously trying to reestablish the connection. Everything is more or less working, with one exception.

It seems that right after the existing connection fails, and after a new attempt also fails (it takes our PostgreSQL 10-15 seconds to accepts new connections after a restart), if you don't explicitly call db.close(); db.del (db is the ConnectionPool instance), the Twisted reactor keeps calling ConnectionPool.doRead(), which in turn eventually reaches the point where psycopg2.connection.poll() (self.pollable().poll()) is called which in turn causes en error (because the connection has already failed) which is then not pushed to the self._pollingD errback as that deferred has been previously "consumed" by the previous error, thus resulting in an Unhandled error being printed—and there's no way to catch this error as self._pollingD was the only way to do it.

So it seems that txpostgres is not cancelling some of the recurring calls coming from the Twisted reactor.

With an explicit db.close(); del db right after a connection error, everything is working smoothly—but it's surprising behaviour and really counterintuitive to manually have to do db.close(); del db right after a failed attempt to db.start().

Here's the code that I'm using (slightly simplified):

(below the code there's also the unhandled error traceback coming from the reactor, after the connection has already failed, i.e. an error has already been passed into self._pollingD.errback and self._pollingD has been set to None, so the next time an error occurs, it's just re-raised, and then the reactor does print "Unhandled error")

class DbUtil(object):

    _db = None
    _connecting_d = None

    @inlineCallbacks
    def _ensure_connected(self, reconnect=False):
        if not self._db or reconnect:
            if self._connecting_d:
                log.msg("already connecting, blocking")
                yield self._connecting_d
                log.msg("...unblocked")
                return

            log.msg('connecting')
            self._connecting_d = Deferred()

            if reconnect:
                log.msg('closing defunct DB')
                self.close()
                yield sleep(5.0)
                log.msg('woke')

            while True:
                try:
                    log.msg('starting ConnectionPool...')
                    self._db = txpostgres.ConnectionPool('pyPgSQL.PgSQL', 'dbname=txpostgres_test')
                    yield self._db.start()
                except Exception as e:
                    log.err("error while connecting: %s" % type(e).__name__)
                    self.close()
                    yield sleep(2.0)
                    log.msg("woke")
                else:
                    log.msg("...DONE")
                    break
            d, self._connecting_d = self._connecting_d, None
            d.callback(None)

    @inlineCallbacks
    def _run_with_retry(self, f):
        while True:
            try:
                ret = yield f()
            except (DatabaseError, OperationalError) as e:
                log.err("database error %s; attempting to reconnect" % type(e).__name__)
                yield self._ensure_connected(reconnect=True)
            else:
                returnValue(ret)

    @inlineCallbacks
    def query(self, sql, *args, **kwargs):
        yield self._ensure_connected()
        ret = yield self._run_with_retry(lambda: self._db.runQuery(sql, *args, **kwargs))
        returnValue(ret)

    @inlineCallbacks
    def query_one(self, sql, *args, **kwargs):
        yield self._ensure_connected()
        res = yield self._run_with_retry(lambda: self.query(sql, *args, **kwargs))
        returnValue(res[0] if res else None)

    @inlineCallbacks
    def query_none(self, sql, *args, **kwargs):
        yield self._ensure_connected()
        yield self._run_with_retry(lambda: self._db.runOperation(sql, *args, **kwargs))

    def close(self):
        if self._db:
            db, self._db = self._db, None
            db.close()

The unhandled error traceback:

2012-02-02 17:35:12+0200 [connection] Unhandled Error
    Traceback (most recent call last):
      File "/Users/erik.allik/.virtualenvs/dts-agent/lib/python2.7/site-packages/twisted/python/log.py", line 84, in callWithLogger
        return callWithContext({"system": lp}, func, *args, **kw)
      File "/Users/erik.allik/.virtualenvs/dts-agent/lib/python2.7/site-packages/twisted/python/log.py", line 69, in callWithContext
        return context.call({ILogContext: newCtx}, func, *args, **kw)
      File "/Users/erik.allik/.virtualenvs/dts-agent/lib/python2.7/site-packages/twisted/python/context.py", line 118, in callWithContext
        return self.currentContext().callWithContext(ctx, func, *args, **kw)
      File "/Users/erik.allik/.virtualenvs/dts-agent/lib/python2.7/site-packages/twisted/python/context.py", line 81, in callWithContext
        return func(*args,**kw)
    --- <exception caught here> ---
      File "/Users/erik.allik/.virtualenvs/dts-agent/lib/python2.7/site-packages/twisted/internet/selectreactor.py", line 150, in _doReadOrWrite
        why = getattr(selectable, method)()
      File "/Users/erik.allik/.virtualenvs/dts-agent/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 595, in doRead
        _PollingMixin.doRead(self)
      File "/Users/erik.allik/.virtualenvs/dts-agent/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 158, in doRead
        self.continuePolling()
      File "/Users/erik.allik/.virtualenvs/dts-agent/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 127, in continuePolling
        state = self.pollable().poll()
    psycopg2.OperationalError: asynchronous connection failed

opened by erikkaplun 8

Unhandled errors on TCP connection timeout

I've been testing txpostgres' response to connection failures/timeouts, and I've run into issues where, during a connection failure or timeout, exceptions occur that are not caught/do not fire an errback and so it's not possible to handle them by i.e. closing the connection and reopening it.

There are two slightly different failure modes. If a tcp_keepalive timeout is allowed to occur, then no errbacks are fired. If a tcp_retries or TCP_USER_TIMEOUT occurs, one errback fires but an unhandled exception still occurs. This happens both with SSL and non-SSL connections -- both are psycopg2.OperationalError exceptions, but the error messages are slightly different (see below).

The tracebacks

Unhandled Error
Traceback (most recent call last):
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/log.py", line 84, in callWithLogger
    return callWithContext({"system": lp}, func, *args, **kw)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/log.py", line 69, in callWithContext
    return context.call({ILogContext: newCtx}, func, *args, **kw)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/context.py", line 118, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/context.py", line 81, in callWithContext
    return func(*args,**kw)
--- <exception caught here> ---
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/internet/posixbase.py", line 581, in _doReadOrWrite
    why = selectable.doRead()
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 584, in doRead
    _PollingMixin.doRead(self)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 151, in doRead
    self.continuePolling()
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 123, in continuePolling
    state = self.pollable().poll()
psycopg2.OperationalError: SSL SYSCALL error: EOF detected

Unhandled Error
Traceback (most recent call last):
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/log.py", line 84, in callWithLogger
    return callWithContext({"system": lp}, func, *args, **kw)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/log.py", line 69, in callWithContext
    return context.call({ILogContext: newCtx}, func, *args, **kw)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/context.py", line 118, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/context.py", line 81, in callWithContext
    return func(*args,**kw)
--- <exception caught here> ---
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/internet/posixbase.py", line 581, in _doReadOrWrite
    why = selectable.doRead()
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 584, in doRead
    _PollingMixin.doRead(self)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 151, in doRead
    self.continuePolling()
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 123, in continuePolling
    state = self.pollable().poll()
psycopg2.OperationalError: could not receive data from server: Connection timed out

Reproducing this error

I created a program to reproduce this: https://gist.github.com/1661012. Apologies if it's not very idiomatic Twisted, but hopefully it does the job.

This must be run on a Linux machine as it requires iptables to simulate connection failures. Having TCP fail quickly using the TCP_USER_TIMEOUT socket option requires running this with kernel >= 2.6.37. Further, the python modules twisted, txpostgres and psycopg2 are required. Finally, a PostgreSQL database listening on 127.0.0.1 with no password needed to access the 'postgres' database from localhost is required.

The program will provide prompts to add and remove an iptables rule -- you can just start the program and follow along.

You can run the program with or without an 'ssl' argument to enable or disable SSL on the connection.

If you don't want to run the program yourself, both failure modes are shown below (which include the tracebacks already shown above):

$ python demo_conn.py ssl (sslmode=require)

An Errback is called (ERRBACK: (<class 'psycopg2.OperationalError'>, SSL SYSCALL error: Connection timed out)) but an unhandled error still occurs:

Unhandled Error
Traceback (most recent call last):
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/log.py", line 84, in callWithLogger
    return callWithContext({"system": lp}, func, *args, **kw)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/log.py", line 69, in callWithContext
    return context.call({ILogContext: newCtx}, func, *args, **kw)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/context.py", line 118, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/context.py", line 81, in callWithContext
    return func(*args,**kw)
--- <exception caught here> ---
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/internet/posixbase.py", line 581, in _doReadOrWrite
    why = selectable.doRead()
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 584, in doRead
    _PollingMixin.doRead(self)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 151, in doRead
    self.continuePolling()
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 123, in continuePolling
    state = self.pollable().poll()
psycopg2.OperationalError: SSL SYSCALL error: EOF detected

$ python demo_conn.py (sslmode=disable)

An Errback is called (ERRBACK: (<class 'psycopg2.OperationalError'>, could not receive data from server: Connection timed out) but an unhandled error still occurs:

Unhandled Error
Traceback (most recent call last):
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/log.py", line 84, in callWithLogger
    return callWithContext({"system": lp}, func, *args, **kw)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/log.py", line 69, in callWithContext
    return context.call({ILogContext: newCtx}, func, *args, **kw)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/context.py", line 118, in callWithContext
    return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/python/context.py", line 81, in callWithContext
    return func(*args,**kw)
--- <exception caught here> ---
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/twisted/internet/posixbase.py", line 581, in _doReadOrWrite
    why = selectable.doRead()
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 584, in doRead
    _PollingMixin.doRead(self)
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 151, in doRead
    self.continuePolling()
  File "/home/woodrow/tmp/txpostgres_noerrback/virt-python/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 123, in continuePolling
    state = self.pollable().poll()
psycopg2.OperationalError: could not receive data from server: Connection timed out

My Environment:

Python 2.7.2 (default, Nov 21 2011, 17:25:27) 
>>> import txpostgres
>>> print(txpostgres.__version__)
(1, 0, 0)
>>> import twisted
>>> print(twisted.__version__)
11.1.0
>>> import psycopg2
>>> print(psycopg2.__version__)
2.4.4 (dt dec pq3 ext)

opened by woodrow 7

error handling with nonexistent table names/wrong connection password

From a comment by https://github.com/oberstet in https://github.com/wulczer/txpostgres/pull/5#issuecomment-2688245

I did some testing with

https://github.com/oberstet/txpostgres/blob/testing/testing/txpostgres_error.py

2 cases:

a) correct database password, should fail on "select * from nonexistent" b) incorrect password

The latest https://github.com/wulczer/txpostgres/commit/d13f8ff5381f83a3521fc5067f5f310eba73fe73

give me tracebacks on both:

https://gist.github.com/1353252

The latest https://github.com/smira/txpostgres/commit/e8571b02dced034a5289ef8cc4f4ea3e8d02d814

gives me traceback only on b), and works for a):

https://gist.github.com/1353259

opened by wulczer 7
Connection.runOperation() and runQuery() may not close the cursor, either

Prompted by #35 I took a look at runQuery() and runOperation(), and they seem to have the same problem as runInteraction() in case of failure. I tried fixing it the same way you fixed runInteraction() but that broke the disconnectWhileRunning test - cursor.close() mustn't be called while the query is still running, but connectionLost() calls the errback (I think?).

opened by mrwonko 5

Calling reactor.stop() while queries are underway raises multiple psycopg2.ProgrammingError

When I have a number of txpostgres.txpostgres.ConnectionPool queries open and I call reactor.stop(), the terminal gets flooded with this traceback:

2011-05-14 15:57:00+0000 [cursor] Unhandled Error
    Traceback (most recent call last):
      File "/home/peter/bzr/env/lib/python2.6/site-packages/Twisted-11.0.0-py2.6-linux-i686.egg/twisted/internet/defer.py", line 542, in _runCallbacks
        current.result = callback(current.result, *args, **kw)
      File "/home/peter/bzr/env/lib/python2.6/site-packages/txpostgres/txpostgres.py", line 407, in rollbackAndPassthrough
        e = defer.maybeDeferred(cursor.execute, "rollback")
      File "/home/peter/bzr/env/lib/python2.6/site-packages/Twisted-11.0.0-py2.6-linux-i686.egg/twisted/internet/defer.py", line 133, in maybeDeferred
        result = f(*args, **kw)
      File "/home/peter/bzr/env/lib/python2.6/site-packages/txpostgres/txpostgres.py", line 178, in execute
        return self._doit('execute', query, params)
    --- <exception caught here> ---
      File "/home/peter/bzr/env/lib/python2.6/site-packages/txpostgres/txpostgres.py", line 196, in _doit
        getattr(self._cursor, name)(*args, **kwargs)
    psycopg2.ProgrammingError: execute cannot be used while an asynchronous query is underway

I never get my shell prompt back without killing the process, so I assume it's stuck in a loop somewhere.

I've written some code to reproduce this:

git clone git://gist.github.com/972617.git gist-972617

opened by ghost 4

Do not allow an asynchronous cursor to duplicate notifications to lis…

I noticed in my application code using 1.2.0 with notification order fix that a large number of notifications were getting duplicated after a transaction was completed. A total of 1613 notifications were coming from the database (confirmed in pgAdminIII).

A sample of debug output looks like:

# first transaction (works as expected)
CREATED CURSOR: <magdb.connection.Cursor object at 0x7fbaf642a290>
CLOSE CURSOR: <magdb.connection.Cursor object at 0x7fbaf642a290> 17
!!!!!!! CURRENT READ: 17
!!!!!!! TOTAL NOTIFICATIONS: 17

# second transaction (works as expected)
CREATED CURSOR: <magdb.connection.Cursor object at 0x7fbaf642a250>
CLOSE CURSOR: <magdb.connection.Cursor object at 0x7fbaf642a250> 17
!!!!!!! CURRENT READ: 17
!!!!!!! TOTAL NOTIFICATIONS: 34

# third transaction (duplicates some of the 1579 notifications)
CREATED CURSOR: <magdb.connection.Cursor object at 0x7f401ad8e2d0>
CLOSE CURSOR: <magdb.connection.Cursor object at 0x7f401ad8e2d0> 74
!!!!!!! CURRENT READ: 74
!!!!!!! TOTAL NOTIFICATIONS: 108
CREATED CURSOR: <magdb.connection.Cursor object at 0x7f401ad8e490>
CLOSE CURSOR: <magdb.connection.Cursor object at 0x7f401ad8e490> 1505
CREATED CURSOR: <magdb.connection.Cursor object at 0x7f401ad8e590>
!!!!!!! CURRENT READ: 1505
!!!!!!! TOTAL NOTIFICATIONS: 1613
CLOSE CURSOR: <magdb.connection.Cursor object at 0x7f401ad8e590> 1505
DUPLICATE  (detected identical 1505 notifications)
!!!!!!! CURRENT READ: 1505
!!!!!!! TOTAL NOTIFICATIONS: 3118

opened by mwalton21 3

Connection.runInteraction() may not close cursor?

I just read through the source for runInteraction() since the documentation left me wondering whether I was supposed to manually close the cursor in the supplied function. (Apparently not.)

If I'm reading this right, rollbackAndPassthrough() will always return a Failure, causing the callback that closes the cursor to never be called since the errback will be called instead? Shouldn't it be addBoth() instead of addCallback() for the c.close() lambda? Or am I missing something?

opened by mrwonko 3
Integrate psycopg2cffi library

https://github.com/chtd/psycopg2cffi provides a CFFI-based implementation of psycopg2, based on the original psycopg2-ctypes library that txpostgres already supports.

The main benefit of integrating psycopg2cffi with txpostgres would be improved performance under pypy.

opened by ldanielburr 3

Check file descriptor before attempting to re-add reader to reactor.

Using: Twisted-12.0.0 pyscopg2-2.5.1

By manually stopping postgresql with an active connection open, the connection is not being properly closed and the doRead function tries to add itself back into the reader list when it has a non-existent file descriptor (-1)

Traceback (most recent call last):
  File "/home/max/nxt/magdb/magdb/connection.py", line 25, in doRead
    txpostgres.Connection.doRead(self)
  File "/home/max/nxt/storesrv/venv/local/lib/python2.7/site-packages/txpostgres/txpostgres.py", line 689, in doRead
    self.reactor.addReader(self)
  File "/home/max/nxt/storesrv/venv/local/lib/python2.7/site-packages/twisted/internet/pollreactor.py", line 117, in addReader
    self._updateRegistration(fd)
  File "/home/max/nxt/storesrv/venv/local/lib/python2.7/site-packages/twisted/internet/pollreactor.py", line 74, in _updateRegistration
    self._poller.unregister(fd)
ValueError: file descriptor cannot be a negative integer (-1)
CONNECTION: <connection object at 0x2ac86b0; dsn: 'sslmode=require connect_timeout=5 keepalives_idle=5 keepalives_interval=5 keepalives_count=4 dbname=storage host=127.0.0.1 password=xxxxxxxxxx', closed: 0>

opened by mwalton21 3

Allow access to rowcount for operations

Occasionally I need to know how many rows where affected by a DELETE or UPDATE. This is currently not possible, since runOperation and runQuery both discard the cursor.

opened by wichert 1
a more erganomic connection pool

something that automaticly connects, reconnects, and checks out connections with optional pessimistic connection checking. With options for connection recycling, size, overflow, lifo/fifo. Similar to https://docs.sqlalchemy.org/en/latest/core/pooling.html#sqlalchemy.pool.QueuePool

opened by graingert 1
`ProgrammingError` after reconnecting.

I'm using ConnectionPool with DeadConnectionDetector. Here's my connection function, if it helps.

This morning, my primary database failed, which happened to be during a period of high-frequency querying. So of course, psycopg2.OperationalError appears several times, before it was replaced with ConnectionDead. Shortly thereafter, my standby server was promoted to master. txpostgres was then able to reconnect, which [I assume] it did. But then, immediately, the following error starts: psycopg2.ProgrammingError: execute cannot be used while an asynchronous query is underway. As far as I can tell, no queries were successfully executed until I restarted the app.

I've been digging around the txpostgres code to try to find how this could happen, but I haven't had any enlightenment so far. I was wondering if you might have any ideas.

Here are some tracebacks, but—as is usual for Twisted—they aren't very helpful.

opened by luhn 7