Thread: psycopg and gevent with pgbouncer

Hello.

I'm the author of this topic on stackoverflow
http://stackoverflow.com/questions/7658714/psycopg2-pgbouncer-async-mode-with-gevent-error
http://archives.postgresql.org/psycopg/2011-10/msg00006.php

I have two more questions:

1. When I use connection.commit()  before end of application handle request, I've got such messages in logs:


2011-10-05 13:00:52.941 26120 LOG C-0x7ea9d0: database/user@10.58.65.143:39380 login successful: db=database user=user
2011-10-05 13:00:52.941 26120 LOG C-0x7eb5a0: database/user@10.58.65.143:39378 login successful: db=database user=user
2011-10-05 13:00:52.942 26120 LOG C-0x7ecfe0: database/user@10.58.65.143:39377 login successful: db=database user=user
2011-10-05 13:00:52.948 26120 LOG C-0x7ea5e0: database/user@10.58.65.143:39293 closing because: client close request (age=0)
2011-10-05 13:00:52.978 26120 LOG C-0x7ea880: database/user@10.58.65.143:39381 login successful: db=database user=user
2011-10-05 13:00:52.978 26120 LOG C-0x7ea340: database/user@10.58.65.143:39315 closing because: client close request (age=0)
2011-10-05 13:00:52.993 26120 LOG C-0x7ec2c0: database/user@10.58.65.143:39314 closing because: client close request (age=0)

As I understand It `age` - is a count of connection uses. If I run application in sync mode (without gevent), messages in logs have age = 140 and more. Maybe there is a reason why in async mode age is such a small?

2. I use psycopg simple connection pooler :
pool = SimpleConnectionPool(1, 3, connection_string)

if I add async option:
pool = SimpleConnectionPool(1, 3, connection_string, async=1)

I've got an error, than I can't understand:
  File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 940, in __create_execution_context
    self._handle_dbapi_exception(e, kwargs.get('statement', None), kwargs.get('parameters', None), None, None)
  File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception
    raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)
OperationalError: (OperationalError) asynchronous connection attempt underway None [{}]


 

 

On Thu, Oct 6, 2011 at 10:45 AM, Raoul Duke <knevcher@gmail.com> wrote:
> I'm the author of this topic on stackoverflow
> http://stackoverflow.com/questions/7658714/psycopg2-pgbouncer-async-mode-with-gevent-error
> http://archives.postgresql.org/psycopg/2011-10/msg00006.php

Hey!

> I have two more questions:
>
> 1. When I use connection.commit()  before end of application handle request,
> I've got such messages in logs:
>
>
> 2011-10-05 13:00:52.941 26120 LOG C-0x7ea9d0:
> database/user@10.58.65.143:39380 login successful: db=database user=user
> 2011-10-05 13:00:52.941 26120 LOG C-0x7eb5a0:
> database/user@10.58.65.143:39378 login successful: db=database user=user
> 2011-10-05 13:00:52.942 26120 LOG C-0x7ecfe0:
> database/user@10.58.65.143:39377 login successful: db=database user=user
> 2011-10-05 13:00:52.948 26120 LOG C-0x7ea5e0:
> database/user@10.58.65.143:39293 closing because: client close request
> (age=0)
> 2011-10-05 13:00:52.978 26120 LOG C-0x7ea880:
> database/user@10.58.65.143:39381 login successful: db=database user=user
> 2011-10-05 13:00:52.978 26120 LOG C-0x7ea340:
> database/user@10.58.65.143:39315 closing because: client close request
> (age=0)
> 2011-10-05 13:00:52.993 26120 LOG C-0x7ec2c0:
> database/user@10.58.65.143:39314 closing because: client close request
> (age=0)
>
> As I understand It `age` - is a count of connection uses. If I run
> application in sync mode (without gevent), messages in logs have age = 140
> and more. Maybe there is a reason why in async mode age is such a small?

No, age is in seconds, how long the connection has been connected.

Also note the C- before 0x... means the message is about client connection,
S- means it's about server connection.

> 2. I use psycopg simple connection pooler :
> pool = SimpleConnectionPool(1, 3, connection_string)
>
> if I add async option:
> pool = SimpleConnectionPool(1, 3, connection_string, async=1)
>
> I've got an error, than I can't understand:
>   File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 940,
> in __create_execution_context
>     self._handle_dbapi_exception(e, kwargs.get('statement', None),
> kwargs.get('parameters', None), None, None)
>   File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 931,
> in _handle_dbapi_exception
>     raise exc.DBAPIError.instance(statement, parameters, e,
> connection_invalidated=is_disconnect)
> OperationalError: (OperationalError) asynchronous connection attempt
> underway None [{}]
>
> Do I understand that when using the gevent, I have to use async option?

I leave that to psycopg guys to answer.

--
marko

On Thu, Oct 6, 2011 at 9:54 AM, Marko Kreen <markokr@gmail.com> wrote:
> On Thu, Oct 6, 2011 at 10:45 AM, Raoul Duke <knevcher@gmail.com> wrote:

>> 2. I use psycopg simple connection pooler :
>> pool = SimpleConnectionPool(1, 3, connection_string)
>>
>> if I add async option:
>> pool = SimpleConnectionPool(1, 3, connection_string, async=1)
>>
>> I've got an error, than I can't understand:
>>   File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 940,
>> in __create_execution_context
>>     self._handle_dbapi_exception(e, kwargs.get('statement', None),
>> kwargs.get('parameters', None), None, None)
>>   File "/usr/lib/pymodules/python2.6/sqlalchemy/engine/base.py", line 931,
>> in _handle_dbapi_exception
>>     raise exc.DBAPIError.instance(statement, parameters, e,
>> connection_invalidated=is_disconnect)
>> OperationalError: (OperationalError) asynchronous connection attempt
>> underway None [{}]
>>
>> Do I understand that when using the gevent, I have to use async option?
>
> I leave that to psycopg guys to answer.

An async connection it is not usable just after it's been created:
it's in an half baked state where poll() returns POLL_READ or
POLL_WRITE until the client and the server have completed the
asynchronous login handshake. The protocol must be pushed by the
client as described in
<http://initd.org/psycopg/docs/advanced.html#asynchronous-support>.
The wait loop should be somewhere after the connection creation and
before the usage: I don't know if the libraries you are using allow
that.

Note that if you are using gevent and not twisted you may use the
green mode instead
<http://initd.org/psycopg/docs/advanced.html#support-to-coroutine-libraries>,
that provides you async connections whose behaviour is entirely
equivalent to the blocking one (they block a single greenlet and
schedule the other to go). I suspect you should try them instead of
the raw async mode.

-- Daniele

I use psycopgreen for gevent and psycopg2 and it works fine. 
Is there any way to detect if my application reconnect to db (pgbouncer) on each client request? I use psygopg2.pool.