Re: How to handle disconnect on connection pools

On Fri, Jan 23, 2015 at 5:36 PM, Diego Pasqualin
<dpasqualin@c3sl.ufpr.br> wrote:
> Hi,
>
> I have an application with many threads using a PersistentConnectionPool.
> It works great, until someone restarts the database, disconnecting all
> connections in the pool.
>
> So, what can I do to recover the connections when the server restart?
>
> I was thinking about catching psycopg2.OperationalError in a try/except
> statement, executing a pool.putconn(conn, close=True) to close the
> problematic connection, then trying to execute the SQL query again. Though,
> I'm not sure if the PersistentConnectionPool class would create a new
> connection afterwards, or if this is the right way to do it...

The connection pool should detect a broken connection and replace it
with a fresh one, but this means you will get as many failures as
connections before everything's back on track: this may or may not be
unpleasant, it depends on your app. FYI the pool checks
get_transaction_status() in _putconn() to figure out whether a
connection is broken: this was added in psycopg 2.4.3: previous
version wouldn't discard a broken connection.

If you are fine with multiple failures until the pool is purged, I
don't think you need any action. If you want to completely purge the
pool, your best stab is to take a look to the pool.py source: it's a
simple module and you'll easily figure out a way to do it. I don't
have a ready to use recipe and can't take a look at that now: I'm
pretty sure if you take a look yourself you'll quickly solve the
problem.

-- Daniele