Re: (long) improving async support in psycopg

On Wed, Mar 24, 2010 at 10:56 AM, Jan Urbański <[email protected]> wrote:
> Daniele Varrazzo wrote:
>> On Wed, Mar 24, 2010 at 3:38 AM, Jan Urbański <[email protected]> wrote:
>>> On 23/03/10 01:38, Jan Urbański wrote:
>>
>>> I won't bother you with the details of how an async connection building
>>> procedure could look like, I just want to raise one last issue. The
>>> connection class issues a couple of queries, like SHOW client_encoding or
>>> SHOW default_transaction_isolation. These are issues synchronously and there
>>> is no control over them. That would even be OK, hopefully an application
>>> would not create and close lots of connection during it's lifetime. The bad
>>> thing is the BEGIN query that gets issued synchronously and even less
>>> convenient is that COMMITs are synchronous, and these could take some time
>>> on systems with a certain configuration.
>>
>> I was wondering if it wouldn't be better to have "async" as a
>> connection characteristic (a `psycopg2.connect()` parameter) instead
>> of as a parameter of the single query executions. This way the driver
>> could performed these steps in asynchronous fashion too. Also the
>> issues arising by mixing up sync/async operation would disappear.
>
> That might make sense. Although then you can't use the same connection
> for both sync and async things, so if in your software you sometimes
> want to just execute a simple blocking query and then do na asynchronous
> one you would have to open two separate connections.
>
> You could argue that an application that uses the async interface will
> not want to block ever (that's certainly the case with my Twisted PG
> module, I would never use sync execution there). But is it worth it to
> impose that limit? It's easy enough to just disallow running anything
> while the connection is processing an async query.

I think an application with mixed needs could easily create two
connections with different sync parameter to mix the call styles. Of
course they couldn't be in the same transaction, but this would be a
still more uncommon use case.

I have a different use case for async psycopg, maybe worth mentioning
for completeness and in order to see if it would be possible to have
an interface useful in different async environments too. Notice that I
am not working directly on these issues, so my knowledge of the issues
is more from code read than from code written, so I may actually be
not completely accurate.

We have coroutine-based program, currently using eventlet_ and running
psycopg in a thread to avoid being blocked (a service offered by
eventlet itself via the db_pool module). We'd like to move to gevent_,
a library built on libevent_. Both eventlet and gevent can
"monkeypatch" pure python code in order to make it non-blocking,
cooperatively yielding the control to another coroutine in presence of
a blocking call. In a pilot program using gevent we used a different
postgres adapter (pg8000, non libpq based) because, being written in
pure python, gevent can replace its socket with a cooperative one (but
pg8000 is rather immature, e.g. we had to patch it to avoid leaving
idle transactions open, something it does by design).

_eventlet: http://wiki.secondlife.com/wiki/Eventlet
_gevent: http://www.gevent.org/
_libevent: http://monkey.org/~provos/libevent/

I think the optimum would be to have an async connection with the
classic dbapi interface built on top, with coroutine-friendly blocking
methods. A dbapi interface (thus without the extra `cursor.execute()`
parameter) would also enable the async psycopg to be used with
existing clients, e.g. SQLAlchemy. I haven't thought if it is possible
to have this independently from the async framework used (twisted,
gevent, libevent, maybe providing an hook point where the libs could
install their callbacks...), I just think that having the concept of
an "async connection" instead of just an "async execute" could be a
better starting point (the sync/async code paths can be widely
different: there could be two C classes to handle them, which could
clean up a lot of code paths).

I'd really like to do myself some tries in this direction, but I don't
think it will be possible in the next days. I'd like to hear some
feedback by people knowing twisted and/or coroutine-based libraries to
hear if there would be show-stoppers I don't know about in this
approach.

-- Daniele
_______________________________________________
Psycopg mailing list
[email protected]
http://lists.initd.org/mailman/listinfo/psycopg