Re: Problem with psycopg2 and asyncio

As far as I know, iterating on the cursor will still "fetchall" the rows(or
worse, fetchone?), unless you're working with serverside cursors.
Does the same delay happen even when doing fetchall ?

On Sun, Feb 28, 2016 at 8:53 AM, Frank Millman <frank(at)chagford(dot)com> wrote:

> Hi all
>
> 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.
>
> I am using psycopg2 2.6.1 and python 3.5 on Fedora 22.
>
> Thanks
>
> Frank Millman
>
>