Thread: Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
From
Alexey Kachalin
Date:
Hello,
I would like to report a bug.
# Title.
Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
# Description.
Prepared sql collision can occur when 2 conditions are met:
1. Prepared SQL name is longer than 63 characters.
2. Both sql names have the same first 63 characters.
This issue happens because Postgres silently truncates prepared SQL names to 63 characters.
According to documentation the length of 63 characters is defined in NAMEDATALEN constant.
In the second example two different SQLs return the same result. It's almost impossible to detect a problem.
A prepared SQL can return unpredictable results based on the time which SQL was prepared first.
Or return an error that is not related to the prepared SQL name.
Also it's possible to miss the warning message of double prepared SQL. Most of the time errors are related to a difference between collided SQL.
The roots of this behaviour are hard detectable on application level.
Is it possible to know which value was used at compilation time from application code?
# Desirable behaviour.
If an identifier exceeds the length defined in NAMEDATALEN an error is thrown, neither silently truncating an identifier.
# Version info
Postgres version 15.3
Php version: 8.2.6
#Code examples on PHP scripts.
--
I would like to report a bug.
# Title.
Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
# Description.
Prepared sql collision can occur when 2 conditions are met:
1. Prepared SQL name is longer than 63 characters.
2. Both sql names have the same first 63 characters.
This issue happens because Postgres silently truncates prepared SQL names to 63 characters.
According to documentation the length of 63 characters is defined in NAMEDATALEN constant.
In the second example two different SQLs return the same result. It's almost impossible to detect a problem.
A prepared SQL can return unpredictable results based on the time which SQL was prepared first.
Or return an error that is not related to the prepared SQL name.
Also it's possible to miss the warning message of double prepared SQL. Most of the time errors are related to a difference between collided SQL.
The roots of this behaviour are hard detectable on application level.
Is it possible to know which value was used at compilation time from application code?
# Desirable behaviour.
If an identifier exceeds the length defined in NAMEDATALEN an error is thrown, neither silently truncating an identifier.
# Version info
Postgres version 15.3
Php version: 8.2.6
#Code examples on PHP scripts.
<?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 = 'smallLengthSQL';// Uncomment for normal behaviour and expected result.
# Uncomment to check an example #1
//$sqlPreparedNameA = $string63 . '_A';
//$sqlPreparedNameB = $string63 . '_B';
//$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_prepareB = 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, $resultB);
//exit();
/*
*
*
* Expected output
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"
}
}
*
*
*
* Gotten output
Warning: pg_prepare(): Query failed: ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists
Warning: pg_execute(): Query failed: ERROR: bind message supplies 2 parameters, but prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" requires 1
Fatal error: Uncaught TypeError: pg_fetch_all(): Argument #1 ($result) must be of type PgSql\Result, bool given
*/
/*
*
*
* Expected output
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"
}
}
*
*
*
* Gotten output
Warning: pg_prepare(): Query failed: ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists
Warning: pg_execute(): Query failed: ERROR: bind message supplies 2 parameters, but prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" requires 1
Fatal error: Uncaught TypeError: pg_fetch_all(): Argument #1 ($result) must be of type PgSql\Result, bool given
*/
# Uncomment to check an example #2
$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_prepareB = 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, $resultB);
exit();
/*
*
* Expected output
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "111"
}
}
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "222"
}
}
*
*
*
* Gotten output
<br />
<b>Warning</b>: pg_prepare(): Query failed: ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "111"
}
}
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "111"
}
}
*/
$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 = 'smallLengthSQL';// Uncomment for normal behaviour and expected result.
# Uncomment to check an example #1
//$sqlPreparedNameA = $string63 . '_A';
//$sqlPreparedNameB = $string63 . '_B';
//$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_prepareB = 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, $resultB);
//exit();
/*
*
*
* Expected output
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"
}
}
*
*
*
* Gotten output
Warning: pg_prepare(): Query failed: ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists
Warning: pg_execute(): Query failed: ERROR: bind message supplies 2 parameters, but prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" requires 1
Fatal error: Uncaught TypeError: pg_fetch_all(): Argument #1 ($result) must be of type PgSql\Result, bool given
*/
/*
*
*
* Expected output
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"
}
}
*
*
*
* Gotten output
Warning: pg_prepare(): Query failed: ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists
Warning: pg_execute(): Query failed: ERROR: bind message supplies 2 parameters, but prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" requires 1
Fatal error: Uncaught TypeError: pg_fetch_all(): Argument #1 ($result) must be of type PgSql\Result, bool given
*/
# Uncomment to check an example #2
$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_prepareB = 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, $resultB);
exit();
/*
*
* Expected output
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "111"
}
}
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "222"
}
}
*
*
*
* Gotten output
<br />
<b>Warning</b>: pg_prepare(): Query failed: ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" already exists
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "111"
}
}
array(1) {
[0]=>
array(1) {
["result_1"]=>
string(3) "111"
}
}
*/
Best regards,
Alexey Kachalin.
Alexey Kachalin.
Re: Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
From
Tom Lane
Date:
Alexey Kachalin <kachalin.alexey@gmail.com> writes:
> Prepared SQL name collision. The name implicitly is truncated by
> NAMEDATALEN
This is not a bug. You exceeded a well-documented implementation
limit, and the response is as documented.
> Is it possible to know which value was used at compilation time from
> application code?
You can do
=# show max_identifier_length;
max_identifier_length
-----------------------
63
(1 row)
or various equivalent ways of inspecting that parameter.
regards, tom lane
Re: Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
From
Alexey Kachalin
Date:
Hello, Tom and other devs.
I would like to point out that this bug report is not about trucating identifiers as mentioned in documentation. The bug is collision, neither trucating.
This is about getting different SQL errors on valid SQL statements.
The valid SQL produces an error, it is a bug, isn't it?
If a programmer did something wrong the error appears, I believe in that.
Please consider an example:
Run
'SELECT 222 as result_1'
and get the result of '111' because of a collision.
How should programmers understand the prepared SQL name exceeding length? No error, no warning, nothing, just valid sql returns the wrong result.
If I exceed the limit I would like to get the error related to an issue, not just my valid SQL returns something unpredictable.
Can I get a proper error for identifying issues and fixing?
Is it expected behaviour that SQL returns corrupt value or error, when a prepared SQL statements name has gone beyond limit?
I would like to point out that this bug report is not about trucating identifiers as mentioned in documentation. The bug is collision, neither trucating.
This is about getting different SQL errors on valid SQL statements.
The valid SQL produces an error, it is a bug, isn't it?
If a programmer did something wrong the error appears, I believe in that.
Please consider an example:
Run
'SELECT 222 as result_1'
and get the result of '111' because of a collision.
How should programmers understand the prepared SQL name exceeding length? No error, no warning, nothing, just valid sql returns the wrong result.
If I exceed the limit I would like to get the error related to an issue, not just my valid SQL returns something unpredictable.
Can I get a proper error for identifying issues and fixing?
Is it expected behaviour that SQL returns corrupt value or error, when a prepared SQL statements name has gone beyond limit?
On Tue, May 23, 2023 at 3:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alexey Kachalin <kachalin.alexey@gmail.com> writes:
> Prepared SQL name collision. The name implicitly is truncated by
> NAMEDATALEN
This is not a bug. You exceeded a well-documented implementation
limit, and the response is as documented.
> Is it possible to know which value was used at compilation time from
> application code?
You can do
=# show max_identifier_length;
max_identifier_length
-----------------------
63
(1 row)
or various equivalent ways of inspecting that parameter.
regards, tom lane
Re: Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
From
Pavel Borisov
Date:
On Wed, 24 May 2023 at 16:49, Alexey Kachalin <kachalin.alexey@gmail.com> wrote: > > Hello, Tom and other devs. > > I would like to point out that this bug report is not about trucating identifiers as mentioned in documentation. The bugis collision, neither trucating. > > This is about getting different SQL errors on valid SQL statements. > The valid SQL produces an error, it is a bug, isn't it? > If a programmer did something wrong the error appears, I believe in that. > > Please consider an example: > Run > 'SELECT 222 as result_1' > and get the result of '111' because of a collision. > How should programmers understand the prepared SQL name exceeding length? No error, no warning, nothing, just valid sqlreturns the wrong result. > > If I exceed the limit I would like to get the error related to an issue, not just my valid SQL returns something unpredictable. > Can I get a proper error for identifying issues and fixing? > Is it expected behaviour that SQL returns corrupt value or error, when a prepared SQL statements name has gone beyond limit? > > On Tue, May 23, 2023 at 3:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> Alexey Kachalin <kachalin.alexey@gmail.com> writes: >> > Prepared SQL name collision. The name implicitly is truncated by >> > NAMEDATALEN >> >> This is not a bug. You exceeded a well-documented implementation >> limit, and the response is as documented. >> >> > Is it possible to know which value was used at compilation time from >> > application code? >> >> You can do >> >> =# show max_identifier_length; >> max_identifier_length >> ----------------------- >> 63 >> (1 row) >> >> or various equivalent ways of inspecting that parameter. >> >> regards, tom lane I see the only thing to fix is to truncate output of error reported i.e ERROR: prepared statement "5c6b58ebdd4464734a57a87431ba24b38d2e49ae5c6b58ebdd4464734a57a87_B" to 63 symbols
Re: Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
From
Julien Rouhaud
Date:
Hi,
Please don't top post on this list.
On Wed, May 24, 2023 at 12:40:26PM +0100, Alexey Kachalin wrote:
>
> I would like to point out that this bug report is not about trucating
> identifiers as mentioned in documentation. The bug is collision, neither
> trucating.
>
> This is about getting different SQL errors on valid SQL statements.
> The valid SQL produces an error, it is a bug, isn't it?
> If a programmer did something wrong the error appears, I believe in that.
>
> Please consider an example:
> Run
> 'SELECT 222 as result_1'
> and get the result of '111' because of a collision.
> How should programmers understand the prepared SQL name exceeding length?
> No error, no warning, nothing, just valid sql returns the wrong result.
>
> If I exceed the limit I would like to get the error related to an issue,
> not just my valid SQL returns something unpredictable.
> Can I get a proper error for identifying issues and fixing?
> Is it expected behaviour that SQL returns corrupt value or error, when a
> prepared SQL statements name has gone beyond limit?
Unless I'm missing something your scenario did raise an error, you just
apparently ignored it and continued, and then eventually got wrong results:
rjuju=# PREPARE aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffggg AS
(SELECT 1);
PREPARE
rjuju=# PREPARE aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggg AS
(SELECT 2);
NOTICE: 42622: identifier "aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggg" will be truncated to
"aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffggg"
ERROR: 42P05: prepared statement "aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffggg" already exists
You should have stopped execution here, or at least tried to do something.
You simply can't expect to execute a prepared statements that you haven't
successfully prepared. The notice does gives you a clue of why it could have
failed, but it could have been something else too.
rjuju=# EXECUTE aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggg;
NOTICE: 42622: identifier "aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggg" will be truncated to
"aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffggg"
?column?
----------
1
(1 row)
The fact that this execution returned the wrong result rather than error out
isn't really different from say a bug on your code that generates duplicated
names that don't get truncated. You shouldn't have tried to execute it.
Re: Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
From
"David G. Johnston"
Date:
On Wednesday, May 24, 2023, Alexey Kachalin <kachalin.alexey@gmail.com> wrote:
If I exceed the limit I would like to get the error related to an issue, not just my valid SQL returns something unpredictable.
Can I get a proper error for identifying issues and fixing?
Is it expected behaviour that SQL returns corrupt value or error, when a prepared SQL statements name has gone beyond limit?
All info beyond 63 chars is discarded early on in the parsing phase. Giving two different prepared statements the same name, as in the first 63 chars, is an application bug since, as you’ve observed, you are likely to end up with non-deterministic behavior. Unfortunately, PostgreSQL will not help you find this kind of bug. There presently are no plans to change this, even though you and others would consider the lack to be undesirable.
David J.
Re: Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
From
Alexey Kachalin
Date:
Thank you for the clarification.
The "bug" report may be closed.
On Wed, May 24, 2023 at 2:22 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, May 24, 2023, Alexey Kachalin <kachalin.alexey@gmail.com> wrote:
If I exceed the limit I would like to get the error related to an issue, not just my valid SQL returns something unpredictable.
Can I get a proper error for identifying issues and fixing?
Is it expected behaviour that SQL returns corrupt value or error, when a prepared SQL statements name has gone beyond limit?All info beyond 63 chars is discarded early on in the parsing phase. Giving two different prepared statements the same name, as in the first 63 chars, is an application bug since, as you’ve observed, you are likely to end up with non-deterministic behavior. Unfortunately, PostgreSQL will not help you find this kind of bug. There presently are no plans to change this, even though you and others would consider the lack to be undesirable.David J.
Re: Prepared SQL name collision. The name implicitly is truncated by NAMEDATALEN
From
"David G. Johnston"
Date:
On Wednesday, May 24, 2023, Alexey Kachalin <kachalin.alexey@gmail.com> wrote:
Thank you for the clarification.The "bug" report may be closed.On Wed, May 24, 2023 at 2:22 PM David G. Johnston <david.g.johnston@gmail.com> wrote:On Wednesday, May 24, 2023, Alexey Kachalin <kachalin.alexey@gmail.com> wrote:
If I exceed the limit I would like to get the error related to an issue, not just my valid SQL returns something unpredictable.
Can I get a proper error for identifying issues and fixing?
Is it expected behaviour that SQL returns corrupt value or error, when a prepared SQL statements name has gone beyond limit?All info beyond 63 chars is discarded early on in the parsing phase. Giving two different prepared statements the same name, as in the first 63 chars, is an application bug since, as you’ve observed, you are likely to end up with non-deterministic behavior. Unfortunately, PostgreSQL will not help you find this kind of bug. There presently are no plans to change this, even though you and others would consider the lack to be undesirable.David J.
Just to be clear, the other comment regarding not blowing past an “object already exists” is going to be your way of noticing this - so “will not help” isn’t quite correct. Though the system doesn’t know why the collision happened or what the requested names were.
David J.