Re: (long) improving async support in psycopg

On 23/03/10 01:38, Jan Urbański wrote:
> I'll keep on working on exposing more async features of the C library
> and adding more unit tests (I've started another thread with the fix for
> the test suite for PG 9.0).

Hi again,

here are some notes I took when looking through the async code in psycopg2:

PQsendQuery can return an error is the socket buffer to the server is 
full. Currently psycopg2 will react with raising an OperationalError. 
Another thing is that you should be calling PQflush after using 
PQsendQuery until you get a 0 from PQflush. My idea was to add a method 
on the cursor that would call PQflush for you, like issent().

This means that the safe way of doing async access to the DB would be:

c = conn.cursor()
# make sure the socket is writable
# so we don't get an exception from PQsendQuery
_, _, _, = select.select([], [c.fileno()], [])

# execute the async command
c.execute("select * from pg_class", async=True)

# check if the data has been sent
wait = not c.issent()

# while there's still data to be sent to the server,
# wait for the socket to become writable and flush
while wait:
    _, _, _  = select.select([], [c.fileno()], [])
    wait = not c.issent()

# now wait for the data to arrive at the socket
wait = True
while wait:
    _, _, _  = select.select([c.fileno()], [], [])
    wait = not c.isready()

print c.fetchall()


Another thing that I'm wondering about: why is the fileno() method doing 
PQflush? The comment says it's to make sure the user uses select() 
safely, but AFAICS it does not really do that. First of all one PQflush 
might not be enough to empty the write buffer and second of all, getting 
the file descriptor should not attempt to flush the outgoing buffer 
queue. Instead, the issent() method would be used for that.


Next question is about pq_fetch and the loop where it does pq_is_busy 
and uses select() internally. Is this loop necessary at all? If the 
execute() call on the cursor was synchronous, there's no need to check 
the status of the connection, or to do a select. PQexec would block 
until all data has been transferred.
If the execute() call was asynchronous, then a client should enter a 
isready() loop doing select() on his own, and eventually get all the 
result, so that loop would also not be necessary. I think it could just 
be dropped, maybe after making sure that pending NOTIFY messages are 
processed.


That brings me back to the issue of doing a fetch() on an async cursor 
that has not received all data yet. The current code blocks until it 
gets all data, but I think that an easier solution would be to just 
error out if there's an async cursor that has not consumed all input 
yet. Anyone using async cursors would implement that select() loop 
anyway, so he will consume all input. That would make it possible to 
drop some things like needsfetch from the cursor structure and simplify 
the code around that quite a bit.


Another issue are server-side asynchronous cursors. ATM I don't see any 
sane way to support them, because after creating a named async cursor 
and executing something asynchronously with it, using isready() to loop 
will just make you wait for the DECLARE CURSOR to return. After that the 
only way to actually do the query is to call fetch*(), which will block.
The problem is that the fetch*() method has to return the result, so 
it's too late to ask the user to use select() after that. The only thing 
I came up was making async server-side cursors throw errors for calls to 
fetch() that are not preceded by a call to a new method called load(n), 
that will issue the FETCH query and will make it possible to use 
isready() to wait for the completion. After that fetch() would have the 
data already on the client side and could throw errors if you try to 
access data that has not been loaded yet.


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.


As you can see there are quite a few things to look at to be able to use 
psycopg2 as a fully async library (remember that my use case is Twisted, 
so every call that can potentially block is bad). I'm beginning to 
wonder if it's actually feasible...


Thanks for reading that overly long post,
Jan
_______________________________________________
Psycopg mailing list
[email protected]
http://lists.initd.org/mailman/listinfo/psycopg