Re: How to handle disconnect on connection pools

From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
To: Diego Pasqualin <dpasqualin(at)c3sl(dot)ufpr(dot)br>
Cc: "psycopg(at)postgresql(dot)org" <psycopg(at)postgresql(dot)org>
Subject: Re: How to handle disconnect on connection pools
Date: 2015-01-23 18:08:05
Message-ID: CA+mi_8aK0H_PJ079sGQSVLA33gJU6L-Ab3T_nSXyKhnbC6CE0A@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: psycopg

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

In response to

Responses

Browse psycopg by date

  From Date Subject
Next Message Diego Pasqualin 2015-01-26 11:30:55 Re: How to handle disconnect on connection pools
Previous Message Diego Pasqualin 2015-01-23 17:36:24 How to handle disconnect on connection pools