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