|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #79134 Add non-blocking way to close pg conn - pg_cancel_query() can take a long time
Submitted: 2020-01-16 16:58 UTC Modified: -
From: Assigned:
Status: Open Package: PDO PgSQL
PHP Version: Irrelevant OS: Any
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
Block user comment
Status: Assign to:
Bug Type:
New email:
PHP Version: OS:


 [2020-01-16 16:58 UTC]
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

   - 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))) {

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.


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2021-01-08 17:02 UTC]
For my use case, there may be better ways of avoiding situations where cancelling a session takes a long time

1. When using pgbouncer, 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?) -
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Apr 13 00:01:28 2024 UTC