Denis Laxalde: Improved query cancellation in PostgreSQL 17 and Psycopg 3.2

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 Reviewed-by: Denis Laxalde 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 c[...]