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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: tandre@php.net
New email:
PHP Version: OS:

 

 [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: Sat Nov 02 18:01:29 2024 UTC