pg_pconnect with flags: PGSQL_CONNECT_FORCE_NEW returns the same connection

[georgebarbarosie]

Description

The documentation for pg_pconnect states the following:

If a second call is made to pg_pconnect() with the same connection_string as an existing connection, the existing connection will be returned unless you pass PGSQL_CONNECT_FORCE_NEW as flags.

However this does not seem to be correct. While pg_connect does return separate connections when the PGSQL_CONNECT_FORCE_NEW flag is used, this is not the case with pg_pconnect as the following test case shows.

Test case:

<?php

function test_connection_identity(bool $persistent, bool $force_new)
{
    echo "Testing connection identity: persistent=" . ($persistent ? "true" : "false") . ", force_new=" . ($force_new ? "true" : "false") . "\n";
    $conn1 = $persistent ?
        pg_pconnect(getenv("DSN"), $force_new ? PGSQL_CONNECT_FORCE_NEW : 0) :
        pg_connect(getenv("DSN"), $force_new ? PGSQL_CONNECT_FORCE_NEW : 0);
    $conn2 = $persistent ?
        pg_pconnect(getenv("DSN"), $force_new ? PGSQL_CONNECT_FORCE_NEW : 0) :
        pg_connect(getenv("DSN"), $force_new ? PGSQL_CONNECT_FORCE_NEW : 0);

    pg_connection_reset($conn1);
    pg_connection_reset($conn2);

    $conns_are_identical = false;
    // PIDs are identical?
    if (pg_get_pid($conn1) === pg_get_pid($conn2)) {
        echo "PIDs are identical\n";
        $conns_are_identical = true;
    } else {
        echo "PIDs are different\n";
    }

    // prepare a statement on the first connection
    $result = pg_prepare($conn1, "my_query", 'SELECT $1::int');

    // get the list of prepared statements on the second connection
    $result = pg_query($conn2, "SELECT name, statement FROM pg_prepared_statements where name = 'my_query'");

    if (pg_num_rows($result) === 0) {
        echo "Statement not found\n";
    } else {
        echo "Statement found: " . pg_fetch_assoc($result)['statement'] . "\n";
    }

    // begin a transaction on the first connection
    $result = pg_query($conn1, "BEGIN;");
    if ($result === false) {
        echo "Failed to begin transaction\n";
    } else {
        echo "Transaction begun\n";
    }
    // create a temporary table on the first connection
    $result = pg_query($conn1, "CREATE TEMP TABLE my_temp_table (id serial, name varchar(40))");
    if ($result === false) {
        echo "Failed to create temporary table\n";
    } else {
        echo "Temporary table created\n";
    }

    // insert a row into the temporary table on the first connection
    $result = pg_query($conn1, "INSERT INTO my_temp_table (name) VALUES ('test')");
    if ($result === false) {
        echo "Failed to insert into temporary table\n";
    } else {
        echo "Inserted into temporary table\n";
    }

    // check if table exists on the second connection
    $result = pg_query($conn2, "SELECT exists (SELECT 1 FROM information_schema.tables WHERE table_name = 'my_temp_table')");
    if ($result === false) {
        echo "Failed to check if table exists\n";
    } else {
        if (pg_fetch_result($result, 0, 0) === 't') {
            echo "Table exists\n";
        } else {
            echo "Table does not exist\n";
        }
    }

    // select the row from the temporary table on the second connection
    $result = @pg_query($conn2, "SELECT * FROM my_temp_table");
    if ($result === false) {
        echo "Failed to select from temporary table\n";
    } else {
        echo "Selected from temporary table: " . pg_fetch_assoc($result)['name'] . "\n";
    }

    pg_close($conn1);
    // does $conn2 noeed closing? check
    if (!$conns_are_identical)
        pg_close($conn2);

    echo "----------------------------------------\n\n";
}

// show pgsql persistent connection setting
echo "pgsql.allow_persistent=" . ini_get("pgsql.allow_persistent") . "\n";

test_connection_identity(false, false);
test_connection_identity(false, true);
test_connection_identity(true, false);
test_connection_identity(true, true);

Output:

pgsql.allow_persistent=1
Testing connection identity: persistent=false, force_new=false
PIDs are identical
Statement found: SELECT $1::int
Transaction begun
Temporary table created
Inserted into temporary table
Table exists
Selected from temporary table: test
----------------------------------------

Testing connection identity: persistent=false, force_new=true
PIDs are different
Statement not found
Transaction begun
Temporary table created
Inserted into temporary table
Table does not exist
Failed to select from temporary table
----------------------------------------

Testing connection identity: persistent=true, force_new=false
PIDs are identical
Statement found: SELECT $1::int
Transaction begun
Temporary table created
Inserted into temporary table
Table exists
Selected from temporary table: test
----------------------------------------

Testing connection identity: persistent=true, force_new=true
PIDs are identical
Statement found: SELECT $1::int
Transaction begun
Temporary table created
Inserted into temporary table
Table exists
Selected from temporary table: test
----------------------------------------

PHP Version

PHP 8.2.13, PHP 8.3.3

Operating System

Debian 11 (bullseye), Debian 12 (bookworm)

[devnexen]

Yes persistent connection does not take in account the flag indeed.