Req #79134 [Com]: Add non-blocking way to close pg conn - pg_cancel_query() can take a long time

Edit report at https://bugs.php.net/bug.php?id=79134&edit=1 ID: 79134 Comment by: tandre@php.net Reported by: tandre@php.net Summary: Add non-blocking way to close pg conn - pg_cancel_query() can take a long time Status: Open Type: Feature/Change Request Package: PDO PgSQL Operating System: Any PHP Version: Irrelevant Block user comment: N Private report: N New Comment: For my use case, there may be better ways of avoiding situations where cancelling a session takes a long time 1. When using pgbouncer, https://www.pgbouncer.org/config.html#connect_query looks like it can be used to set the session statement_timeout and/or lock_timeout for executing statements. 2. When not using pgbouncer, a sql statement can be issued after connecting to the db to set a session statement_timeout and/or lock_timeout (probably through options= param of the connection string?) - https://www.postgresql.org/docs/10/runtime-config-client.html Previous Comments: ------------------------------------------------------------------------ [2020-01-16 16:58:10] tandre@php.net Description: ------------ When a Postgres server is under high CPU load, pg_cancel_query can take a long time, causing issues for the application. (I'm also using pgbouncer, but it's probably not relevant) 1. pg_cancel_query() has no asynchronous version. It may be convenient to add pg_cancel_query_async() - that way, PHP applications can do other work and asynchronously wait for the connection to close using stream_socket_select() and pg_socket() using the async capabilities mentioned in https://grokbase.com/t/php/php-internals/141d5nb7a2/non-blocking-postgresql. - If the query (e.g. SELECT * FROM MY_TABLE) is unintentionally the cause of the high load, it'd make sense to send a message to cancel it before closing the connection. 2. It's currently possible to stop the current connection without waiting for results by calling pg_connection_reset(). However, if the server is having issues, then the side effect of reconnecting is counterproductive. It may be useful to also add pg_connection_terminate() to stop the connection with PQfinish(), without calling PQgetResult(). Right now, the available ways of cancelling requests I can find all call PQgetResult(), which will block waiting for the result of any in-progress queries for much more than 10 seconds when the Postgres server is under high load. ``` static void _close_pgsql_link(zend_resource *rsrc) { PGconn *link = (PGconn *)rsrc->ptr; PGresult *res; zval *hash; while ((res = PQgetResult(link))) { PQclear(res); } PQfinish(link); ``` Expected result: ---------------- There should be a way for the client to cancel and close in-progress postgres queries with a bounded timeout, in cases when the Postgres server is under high load and slow to respond. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=79134&edit=1