Re: psycopg, pgpool and closing connections

Hello Marko,

I'm after a StackOverflow question
<http://stackoverflow.com/questions/7658714/psycopg2-pgbouncer-async-mode-with-gevent-error/>
where the user reports pgpool closing connections used by Python. Part
of them were about the issue we discussed on the ML one year ago,
where connections closed in transaction are discarded with "unclean
server". But after he has ensured clear transactions, still pgpool
drops the connections with "client close request".

psycopg calls PQfinish on the connections when they are destroyed by
python: I am assuming that pgpool intercepts any signal sent to the
server on this event and instead resets a connection and puts it back
to the pool. Am I wrong? Is there anything the user can do, e.g. in
pgpool configuration, to reuse the connections, or the interaction
between pgpool and psycopg is more fundamentally broken?

Thank you.

-- Daniele

On Wed, Oct 5, 2011 at 3:26 PM, Daniele Varrazzo
<daniele(dot)varrazzo(at)gmail(dot)com> wrote:
> I'm after a StackOverflow question
> <http://stackoverflow.com/questions/7658714/psycopg2-pgbouncer-async-mode-with-gevent-error/>
> where the user reports pgpool closing connections used by Python. Part
> of them were about the issue we discussed on the ML one year ago,
> where connections closed in transaction are discarded with "unclean
> server". But after he has ensured clear transactions, still pgpool
> drops the connections with "client close request".

pgbouncer "client close request" is client-only.
It will be in log on pqfinish(). It will not affect server.

If you dont see "unclean server", the server is still in pool.

> psycopg calls PQfinish on the connections when they are destroyed by
> python: I am assuming that pgpool intercepts any signal sent to the
> server on this event and instead resets a connection and puts it back
> to the pool. Am I wrong? Is there anything the user can do, e.g. in
> pgpool configuration, to reuse the connections, or the interaction
> between pgpool and psycopg is more fundamentally broken?

The connection must not be in tx.

The problem is in user code, unless there is unexpected behavior
in psycopg async mode.

--
marko

On Wed, Oct 5, 2011 at 3:19 PM, Marko Kreen <markokr(at)gmail(dot)com> wrote:
> On Wed, Oct 5, 2011 at 3:26 PM, Daniele Varrazzo
> <daniele(dot)varrazzo(at)gmail(dot)com> wrote:
>> I'm after a StackOverflow question
>> <http://stackoverflow.com/questions/7658714/psycopg2-pgbouncer-async-mode-with-gevent-error/>
>> where the user reports pgpool closing connections used by Python. Part
>> of them were about the issue we discussed on the ML one year ago,
>> where connections closed in transaction are discarded with "unclean
>> server". But after he has ensured clear transactions, still pgpool
>> drops the connections with "client close request".
>
> pgbouncer "client close request" is client-only.
> It will be in log on pqfinish().  It will not affect server.
>
> If you dont see "unclean server", the server is still in pool.

Thank you Marko, I think the user was only scared by the "client close
request" in the logs. I've invited him to write to this ML in case he
has still any doubt.

-- Daniele