Doc #81173 [Com]: Fatal error. PHP7-8 PG Prepared statement name collision

Edit report at https://bugs.php.net/bug.php?id=81173&edit=1

 ID:                 81173
 Comment by:         marlynrasavong at gmail dot com
 Reported by:        kachalin dot alexey at gmail dot com
 Summary:            Fatal error. PHP7-8 PG Prepared statement name
                     collision
 Status:             Open
 Type:               Documentation Problem
 Package:            PostgreSQL related
 Operating System:   any
 PHP Version:        8.0.7
 Block user comment: N
 Private report:     N

 New Comment:

the missing index notification is a relatively low-severity warning.
(https://www.c4yourself.ltd/)github.com


Previous Comments:
------------------------------------------------------------------------
[2021-06-21 18:18:19] [email protected]

Adding this info to the PHP documentation should not hurt,
although it is not particulary about prepared statements, but
rather about identifiers in general.

------------------------------------------------------------------------
[2021-06-21 18:09:05] kachalin dot alexey at gmail dot com

Okay, I understood.

Maybe be it's good idea to add a notice in PHP documentation, something like "The prepared statement name can be truncated. Default limit set to 63 character."
At least people will be aware about the limit. How do you think?

In some cases prepared statment warning don't showed, but can returns a malformed data.
The prepared statement 'SELECT 222 as result_1' returns 111. No warrning, no error.

<?php

$host = '';
$db   = '';
$port = '';// 5432
$user = '';
$pass = '';


$connectString = "host=$host port=$port dbname=$db user=$user password=$pass";
$pg_pconnect = pg_pconnect($connectString);

$string63 = '5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87';
//$string63 = 'smallLenthSQL';// Uncoment for expected result.

$sqlPreparedNameA = $string63 . '_A';
$sqlPreparedNameB = $string63 . '_B';
$sqlPreparedBodyA = 'SELECT 111 as result_1';
$sqlPreparedBodyB = 'SELECT 222 as result_1';

$pg_prepareA = pg_prepare($pg_pconnect, $sqlPreparedNameA, $sqlPreparedBodyA);
$pg_prepareA = pg_prepare($pg_pconnect, $sqlPreparedNameB, $sqlPreparedBodyB);

$pg_executeA = pg_execute($pg_pconnect, $sqlPreparedNameA, []);
$pg_executeB = pg_execute($pg_pconnect, $sqlPreparedNameB, []);

$resultA     = pg_fetch_all($pg_executeA);
$resultB     = pg_fetch_all($pg_executeB);

var_dump($resultA);
var_dump($resultB);

------------------------------------------------------------------------
[2021-06-21 09:49:56] [email protected]

Yes, that is a general limitation of PostgreSQL[1], and there is
nothing we can do about it.  If you need to have longer
identifiers, increase the value of NAMEDATALEN.

[1] <https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS>

------------------------------------------------------------------------
[2021-06-19 20:12:07] [email protected]

I don't see anything in ext/pgsql or libpq, source or documentation, that says prepared statement names are limited to 63/64 characters. This may be a server-side limitation.

------------------------------------------------------------------------
[2021-06-19 11:57:13] kachalin dot alexey at gmail dot com

Description:
------------
Brifely
Prepared SQL name collision, because the name implicitly truncated to 63 first characters of given name.

Current behavior:
Fatal errors and warnings.

Desirable behavior:
No any error or warning.
Uncomment a "//$string63 = 'smallLengthSQL'; line to check execution with any error.

Affected version.
All 7 and 8.
Latest check on 8.1.

Notice
Don't forget to put your PG server credentials for connection.


Test script:
---------------
<?php

$host = '';
$port = '';// 5432
$db   = '';
$user = '';
$pass = '';

$connectString = "host=$host port=$port dbname=$db user=$user password=$pass";
$pg_pconnect = pg_pconnect($connectString);

$string63 = '5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87';// length - 63
//$string63 = 'smallLengthSQL';// Uncomment for expected result.

$sqlPreparedNameA = $string63 . '_A';// length - 65 for error case.
$sqlPreparedNameB = $string63 . '_B';// length - 65 for error case.
$sqlPreparedBodyA = 'SELECT $1 as result_1'                ;
$sqlPreparedBodyB = 'SELECT $1 as result_1, $2 as result_2';

$pg_prepareA = pg_prepare($pg_pconnect, $sqlPreparedNameA, $sqlPreparedBodyA);
$pg_prepareA = pg_prepare($pg_pconnect, $sqlPreparedNameB, $sqlPreparedBodyB);

$pg_executeA = pg_execute($pg_pconnect, $sqlPreparedNameA, array("Result A1"             ));
$pg_executeB = pg_execute($pg_pconnect, $sqlPreparedNameB, array("Result B1", "Result B2"));

$resultA     = pg_fetch_all($pg_executeA);
$resultB     = pg_fetch_all($pg_executeB);

var_dump($resultA);
var_dump($resultB);

Expected result:
----------------
array(1) {
  [0]=>
  array(1) {
    ["result_1"]=>
    string(9) "Result A1"
  }
}
array(1) {
  [0]=>
  array(2) {
    ["result_1"]=>
    string(9) "Result B1"
    ["result_2"]=>
    string(9) "Result B2"
  }
}


Actual result:
--------------
Warning: pg_prepare(): Query failed: ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists in
Warning: pg_execute(): Query failed: ERROR: bind message supplies 2 parameters, but prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" requires 1 in
Fatal error: Uncaught TypeError: pg_fetch_all(): Argument #1 ($result) must be of type PgSql\Result, bool given in


------------------------------------------------------------------------



--
Edit this bug report at https://bugs.php.net/bug.php?id=81173&edit=1