Re: (long) improving async support in psycopg

Daniele Varrazzo wrote:
>> 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.

Yeah, I guess we could live with not permitting sync and async
connection at the same time.

> We have coroutine-based program, currently using eventlet_ and running
> psycopg 

Well the big advantage of psycopg2 is that it's using the official C
library, so it's getting the same fixes as libpq gets updated by the
upstream project and of course it's faster. So it you'd want to use it
transparently with eventlet you'll have to write some code anyway... My
Twisted wrapper needs to deal with quite a few things, so going async
can never be free IMHO. As you said the way you work with async
connections is quite different.

> I think the optimum would be to have an async connection with the
> classic dbapi interface built on top, with coroutine-friendly blocking
> methods.

Yeah, to be honest the whole DBAPI 2.0 concept of connections and
cursors is broken IMHO :( You do queries using cursors, but you commit
using connections... when in fact you are always sending stuff over the
same connection, so the "cursor" is just a layer of indiredction (until
you do real standard SQL cursors, but then they behave very differently).

 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'm not sure I follow: how would SQLAlchemy use the async API? I think
the user needs to be aware that he's using the async features, so he
will do select calls (like Twisted) or will yield control on EAGAIN
errors from the socket (like eventlet).

> 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 agree with that part. Maybe instead of bolting the async features into
the same connection and cursor classes just do them separately, in
separate classes, using separate methods (even if they would look more
or less like DBAPI 2.0). I am fairly sure that frameworks using DBAPI
2.0 (like SQLAlchemy or for instance Django, or even current Twisted DB
integration) will not be able to work seamlessly with an async driver.
So maybe let's just invent our own psycopg2 API for that and let people
write code around that.

Hmm, someone could even come up with an PEP for ADBAPI ;-) But at least
for now that's not going to be me.

Cheers,
Jan