PGSQL pipeline mode

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
$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);

首先将请求的函数 pg_enter_pipeline_mode、pg_exit_pipeline_mode、pg_pipeline_sync 和常量 PGSQL_PIPELINE_SYNC 与 PostgreSQL 14 的 libpq pipeline API 进行比较，然后检查现有的 pgsql 连接、查询和结果入口点。当 PHP 以所请求的 pipeline 行为公开这些 API，并且提供的示例能够成功使用它们时，即表示完成。