Toulouse, 15 April 2024
The upcoming PostgreSQL 17 will ship with improved query cancellation capabilities, as part of the libpq, and so will the upcoming release of Psycopg version 3.2.
About a month ago, Alvaro Herrera committed the following patch to PostgreSQL:
libpq: Add encrypted and non-blocking query cancellation routines
The existing PQcancel API uses blocking IO, which makes PQcancel
impossible to use in an event loop based codebase without blocking the
event loop until the call returns. It also doesn't encrypt the
connection over which the cancel request is sent, even when the original
connection required encryption.
This commit adds a PQcancelConn struct and assorted functions, which
provide a better mechanism of sending cancel requests; in particular all
the encryption used in the original connection are also used in the
cancel connection. The main entry points are:
- PQcancelCreate creates the PQcancelConn based on the original
connection (but does not establish an actual connection).
- PQcancelStart can be used to initiate non-blocking cancel requests,
using encryption if the original connection did so, which must be
pumped using
- PQcancelPoll.
- PQcancelReset puts a PQcancelConn back in state so that it can be
reused to send a new cancel request to the same connection.
- PQcancelBlocking is a simpler-to-use blocking API that still uses
encryption.
Additional functions are
- PQcancelStatus, mimicks PQstatus;
- PQcancelSocket, mimicks PQcancelSocket;
- PQcancelErrorMessage, mimicks PQerrorMessage;
- PQcancelFinish, mimicks PQfinish.
Author: Jelte Fennema-Nio <postgres@jeltef.nl>
Reviewed-by: Denis Laxalde <denis.laxalde@dalibo.com>
Discussion: https://postgr.es/m/AM5PR83MB0178D3B31CA1B6EC4A8ECC42F7529@AM5PR83MB0178.EURPRD83.prod.outlook.com
This should be shipped with the upcoming PostgreSQL 17 release.
Being involved in the development Psycopg, a Python driver for PostgreSQL, I pay special attention to how the libpq evolves in PostgreSQL core and this changeset obviously caught my attention.
The initial version of the patch was submitted by Jelte Fennema-Nio more than one year ago, entitled Add non-blocking version of PQcancel and planned in March 2023’s commitfest. By that time, I took the opportunity to review it and started integrating in Psycopg. Sadly, the patch did not get committed for PostgreSQL 16 last year. Luckily, subsequent reviews brought interesting features to the initial patch set, especially concerning security when connection encryption is used.
What makes this changeset interesting?
Well, it’s explained in the commit message above:
- one can now cancel queries in progress in a non-blocking manner from a client program (typically one using asynchronous I/O), and,
- the connection used to drive query cancellation is now as secured as the original connection was.
How does Psycopg benefit from this?
The Psycopg Connection already had a cancel() method, which used the (now legacy) PQcancel interface.
For the upcoming Psycopg 3.2, the integration is being done closely with Daniele Varrazzo; here’s a (sub-)set of pull requests at stake:
- add libpq interface for encrypted and non-blocking cancellation
- encrypted and non-blocking cancellation
- add a timeout parameter to Connection.cancel_safe()
And the target result should be the following high-level interface:
class AsyncConnection:
...
async def cancel_safe(self, *, timeout: float) -> None:
"""Cancel the current operation on the connection.
This is a non-blocking version of cancel() which leverages a more
secure and improved cancellation feature of the libpq, which is only
available from version 17.
If the underlying libpq is older than version 17, the method will fall
back to using the same implementation of cancel().
Raises:
CancellationTimeout - If the cancellation did not terminate within
specified timeout.
"""
...
which would make it possible to write this kind of application code using asyncio:
async with await psycopg.AsyncConnection.connect() as conn:
...
try:
async with asyncio.timeout(delay):
await conn.execute("... long running query ...")
except TimeoutError:
print("operation did not terminate within {delay}s, cancelling")
await conn.cancel_safe()
in which the cancel operation (ie. the await conn.cancel_safe() instruction)
would not block the program, thus allowing it to handle other requests while
waiting for cancellation to complete.
So… Waiting for PostgreSQL 17, and Psycopg 3.2!
