[php-src] Issue #9344: PGSQL pipeline mode
| From: | degtyaryov | Date: | Mon, 15 Aug 2022 15:24:44 +0000 |
| Subject: | [php-src] Issue #9344: PGSQL pipeline mode | ||
| Groups: | php.bugs | ||
| Request: | Send a blank email to php-bugs+get-242227@lists.php.net to get a copy of this message | ||
Issue: https://github.com/php/php-src/issues/9344
Author: degtyaryov
### Description
In libpq PostgreSQL 14 added **Pipeline
mode**(https://www.postgresql.org/docs/current/libpq-pipeline-mode.html);
I tested new functions in C language. This speeds up the my app. No network waiting and PostgreSQL
Server not idle.
Please, add functions **pg_enter_pipeline_mode**, **pg_exit_pipeline_mode**, **pg_pipeline_sync**
and const **PGSQL_PIPELINE_SYNC**.
I have already adapted my db driver to work in pipeline mode. Me very miss these features.
My example:
```php
<?php
$connection_string = 'user=postgres host=localhost port=5432 dbname=postgres';
$connect_timeout = 60; // Set need timeout
$statement_timeout = 60; // Set need timeout
$iteration_count = 200;
if (!$connection = pg_connect($connection_string, PGSQL_CONNECT_ASYNC|PGSQL_CONNECT_FORCE_NEW))
{
die("pg_connect() error\n");
}
elseif (pg_connection_status($connection) === PGSQL_CONNECTION_BAD)
{
die("pg_connect() error\n");
}
elseif (!$stream = pg_socket($connection))
{
die("pg_socket() error\n");
}
$start = time();
// Executing any code not related to the database, while connecting to the database
$seconds = $connect_timeout ?? null;
$microseconds = $seconds === null ? null : 0;
$connect_timeout = $seconds === null ? PHP_INT_MAX : $seconds;
while (true)
{
switch (pg_connect_poll($connection))
{
case PGSQL_POLLING_READING:
$read = [$stream]; $write = $ex = [];
while (!stream_select($read, $write, $ex, $seconds, $microseconds))
{
if (time() - $start >= $connect_timeout)
{
die("Connection timeout\n");
}
}
break;
case PGSQL_POLLING_WRITING:
$write = [$stream]; $read = $ex = [];
while (!stream_select($read, $write, $ex, $seconds, $microseconds))
{
if (time() - $start >= $connect_timeout)
{
die("Connection timeout\n");
}
}
break;
case PGSQL_POLLING_FAILED:
die("Async connect error\n");
case PGSQL_POLLING_OK:
break 2;
}
}
// Connection established
if (pg_enter_pipeline_mode($connection)) {
die("Enter pipeline mode error\n");
}
$start = time();
$read = [$stream]; $write = $ex = [];
$seconds = $statement_timeout ?? null;
$microseconds = $seconds === null ? null : 0;
$statement_timeout = $seconds === null ? PHP_INT_MAX : $seconds;
$sql =<<<'SQL'
select $1 as index, now() + ($1||' day')::interval as time;
SQL;
for ($i = 0; $i < $iteration_count; ++$i)
{
pg_send_query_params($connection, $sql, [$i]);
}
if (pg_pipeline_sync($connection))
{
die("Pipeline sync error\n");
}
// Executing any code not related to the database, while executing queries
for ($i = 0; $i < $iteration_count; ++$i)
{
if (pg_connection_busy($connection))
{
while (!stream_select($read, $write, $ex, $seconds, $microseconds))
{
if (time() - $start >= $statement_timeout)
{
pg_cancel_query($connection);
die("Statement timeout");
}
}
if (pg_connection_busy($connection))
{
pg_cancel_query($connection);
die("Statement timeout");
}
}
if (($result = pg_get_result($connection)) === false)
{
die("pg_get_result wrong result\n");
}
$status = pg_result_status($result);
if (($status !== PGSQL_COMMAND_OK) && ($status !== PGSQL_TUPLES_OK))
{
die('Error: '.pg_result_status($result, PGSQL_STATUS_STRING)."\n");
}
$row = pg_fetch_row($result, null, PGSQL_ASSOC);
pg_free_result($result);
if (pg_get_result($connection) !== false)
{
die("pg_get_result wrong result\n");
}
// Handle $row
}
if (($result = pg_get_result($connection)) !== false)
{
if (pg_result_status($result) !== PGSQL_PIPELINE_SYNC)
{
die("pg_get_result wrong result\n");
}
}
else
{
die("pg_get_result wrong result\n");
}
if (pg_exit_pipeline_mode($connection))
{
die("Exit pipeline mode error\n");
}
pg_close($connection);
```