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

Commencez par comparer les fonctions demandées pg_enter_pipeline_mode, pg_exit_pipeline_mode, pg_pipeline_sync et la constante PGSQL_PIPELINE_SYNC avec l’API de pipeline de libpq de PostgreSQL 14, puis examinez les points d’entrée existants pour les connexions, les requêtes et les résultats pgsql. Le travail est terminé lorsque PHP expose ces API avec le comportement de pipeline demandé et que l’exemple fourni peut les utiliser avec succès.