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

Bắt đầu bằng cách so sánh các hàm được yêu cầu pg_enter_pipeline_mode, pg_exit_pipeline_mode, pg_pipeline_sync và hằng số PGSQL_PIPELINE_SYNC với API pipeline của libpq trong PostgreSQL 14, sau đó kiểm tra các điểm vào hiện có cho kết nối, truy vấn và kết quả pgsql. Công việc được xem là hoàn tất khi PHP cung cấp các API này với hành vi pipeline được yêu cầu và ví dụ được cung cấp có thể sử dụng chúng thành công.