Re: Psycopg2 unable to timeout

On 7/7/14, Will Platnick <wplatnick(at)gmail(dot)com> wrote:
> Good morning!
>
> I am making a program to assist with failing over our PostgreSQL
> master/slave architecture. I am currently using Psycopg2 to talk to
> the Postgres databases, but I've run into an issue in our environment.
>
> We use pgbouncer in our environment, and when pgbouncer is up, but
> postgres is down, psycopg2 just hangs. I need to implement a timeout
> at the Python level since I can't rely on a statement timeout for this
> use, so that if a cursor or execute has been running for x seconds and
> doesn't end, I can force an exception.
>
> In most Python code, I can use signals to check this out, as described
> in
> http://stackoverflow.com/questions/2281850/timeout-function-if-it-takes-too-long-to-finish
>
> But with psycopg2, it goes into a state where my timeouts can't work.
>
> Is there anyway to implement a timeout at the psycopg2 side?

Hello Will,

I'm not really experienced with your problem, but I think you get
stuck in the libpq wait. You can try replacing it with a wait loop
more python-friendly. Try registering the extras.wait_select function
with extensions.set_wait_callback(): this would use Python's select to
do the wait and you may find it more signal friendly.

http://initd.org/psycopg/docs/extras.html#coroutine-support

If you check wait_select code you'll find it very easy to write your
own version and customize it: if you find even python's select too
unresponsive you could replace the single wait

select.select([conn.fileno()], [], [])

with a loop with a timeout in seconds so that the code would
periodically fall back to bytecode-land, e.g (untested):

while not select.select([conn], [], [], 1.0)[0]:
pass

Hope this helps.

-- Daniele