Re: Problem with psycopg2 and asyncio

On 28/02/16 08:53, Frank Millman wrote:
> I know that psycopg2 has some support for asyncio, and that there is a
> package ‘aiopg’ to assist with this, but my question has nothing to do
> with either of these.
> I am writing a server-side program in a client-server environment. I
> have chosen to support three databases – sqlite3 using python’s built-in
> module, Sql Server using pyodbc, and PostgreSQL using psycopg2.
> I have been using asyncio for some time on the network side, and it is
> working well. I have recently turned my attention to ensuring that
> database calls do not block the event loop. I want to keep my code as
> generic as possible across the three databases, so I am looking for a
> solution that will work with all three.
> The recommended approach is to use ‘run_in_executor()’, but I did not
> want to do that because, AFAICT, you would have to use cur.fetchall(),
> and I would prefer to iterate over the cursor. I came up with a solution
> that seems to work well.
> I run each database connection in its own thread, with its own
> queue.Queue() as a request queue. When I want to issue a command, I
> create an instance of an asyncio.Queue() as a return queue, and make a
> tuple of the command and the return queue. I ‘put’ the tuple on the
> request queue, and ‘await’ the return queue. The connection ‘gets’ the
> tuple, executes the command, iterates over the cursor, and ‘puts’ the
> rows retrieved on the return queue in blocks of 50.
> The theory is that the issuer of the command will block while it awaits
> the response, but the main event loop will not be blocked, so no other
> users should experience any delays.
> The theory works with sqlite3 and with pyodbc, but for some reason it
> does not work with psycopg2. The main event loop experiences noticeable
> delays while the connection is retrieving the rows, even though it is
> running in a different thread.
> I have written a program that demonstrates this, but it is 135 lines
> long. I can post it if required, but I thought I would ask the question
> first to see if this is a known issue.

If the fetch runs in a different thread the only reason for the main
loop to experiences noticeable delays is that you're fetching a lot of
data, hogging CPU and memory. Try using a server-side cursor:

http://initd.org/psycopg/docs/usage.html#server-side-cursors

federico

--
Federico Di Gregorio                         federico.digregorio@dndg.it
DNDG srl                                                  http://dndg.it
   The devil speaks truth much oftener than he's deemed.
    He has an ignorant audience.    -- Byron (suggested by Alice Fontana)