Re: How to handle disconnect on connection pools

Multiple failures are not a big deal for me, but the connection pool
doesn't seem to detect broken connections. It keeps failing hours after the
'service postgresql restart' was done.
I'll do some more tests here and look at the pool.py code as you suggested.

Daniele, thank you for the quick answer!

--
Diego G. Pasqualin
UFPR - Universidade Federal do Paraná
C3SL - Centro de Computação Científica e Software Livre

2015-01-23 16:08 GMT-02:00 Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>:

> On Fri, Jan 23, 2015 at 5:36 PM, Diego Pasqualin
> <dpasqualin(at)c3sl(dot)ufpr(dot)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
>