php.net |  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: tandre@php.net Assigned:
Status: Open Package: PDO PgSQL
PHP Version: Irrelevant OS: Any
Private report: No CVE-ID: None
 [2020-01-16 16:58 UTC] 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.


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-01-08 17:02 UTC] tandre@php.net
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
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 23:01:28 2024 UTC