Psycopg 2.2.x issue with PgBouncer

headers Jan Urbański | 24 Jul 2010 16:18

On 24/07/10 01:45, Jason Yan wrote:
> I'm seeing an issue with how Psycopg 2.2.x handles connections with
> PgBouncer.

Hi!

Thanks for the report, I think it's a psycopg2 regression. With the
merging of the green branch psycopg stopped issuing a ROLLBACK when the
connection is garbage collected while still open. PFA the patches that
fix this.

> And the small test script that I'm reproducing the issue with:
>   import psycopg2
>   connection = psycopg2.connect("dbname=test user=postgres
> host=localhost port=6432")
>   cursor = connection.cursor()
>   cursor.execute("SELECT 1")
>   connection.close()

As an immediate workaround you can add connection.rollback() or
connection.commit() before your connection.close().

I fixed this problem in my repo, in the rollback_on_exit branch:

http://git.wulczer.org/?p=psycopg2.git;a=shortlog;h=ref/head/rollback_on_exit

However that triggered interesting issues with the green connection
tests. In short, when deallocating a connection, conn_close() is called.
It then tries to execute a ROLLBACK, which in case of green connections
leads to calling the user-defined wait callback. After the callback
returns, as the connection has zero references, it gets deallocated
again, recursively, which leads to all sorts of problems.

I fixed it in two ways. First, the code keeps a flag that tells if
deallocation is underway. If the flag is set, the deallocation function
is a noop (this prevents recursive invocation).

Second, if deallocation is underway, the green execution method is never
used (it always blocks). That's not ideal, but otherwise it would mean
calling the user-defined callback with the connection object as an
argument that has zero refs... Not sure how this could be fixed.

Note that with the second fix the first is not needed, as we bypass
green execution mode and so won't trigger recursive dealloc. I still
think it's useful to have a guard there, in case later on we start doing
someting in the connection_dealloc function that will cause similar issues.

Cheers,
Jan

Re: Psycopg 2.2.x issue with PgBouncer