Обсуждение: Limitation of prepared statement name
Hello all
I just found a limitation in prepared statements names that I didn't know before and can lead applications to be difficult to debug.
It seems that naming prepared statements is limited to 63 characters as per https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
However, the documentation for PREPARE says nothing about it https://www.postgresql.org/docs/10/static/sql-prepare.html
The dangerous situation is that PostgreSQL seems to ignore rigthmost overflowed characters silently. If we try to prepare another statement with the same 63 leftmost characters as the first one, we get an error of duplicate prepared statement name.
Wouldn't it be good to include that information on the prepared statement doc page? Maybe I should notify this as a general bug so we treat this at the code level.
Best,
Flavio Gurgel
Flavio Henrique Araque Gurgel <fhagur@gmail.com> writes: > It seems that naming prepared statements is limited to 63 characters as per > https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS Yeah, just like every other SQL identifier in Postgres... > The dangerous situation is that PostgreSQL seems to ignore rigthmost > overflowed characters silently. It's hardly silent: regression=# prepare foo1234567890123456789012345678901234567890123456789012345678901234567890 as select 1; NOTICE: identifier "foo1234567890123456789012345678901234567890123456789012345678901234567890" will be truncated to "foo123456789012345678901234567890123456789012345678901234567890" PREPARE > Wouldn't it be good to include that information on the prepared statement > doc page? You could as well argue that every single page that describes creating any sort of object ought to repeat this information. I doubt that people would find that to be an improvement. regards, tom lane
Em ter, 28 de ago de 2018 às 18:21, Tom Lane <tgl@sss.pgh.pa.us> escreveu:
Flavio Henrique Araque Gurgel <fhagur@gmail.com> writes:
> It seems that naming prepared statements is limited to 63 characters as per
> https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
Yeah, just like every other SQL identifier in Postgres...
> The dangerous situation is that PostgreSQL seems to ignore rigthmost
> overflowed characters silently.
It's hardly silent:
regression=# prepare foo1234567890123456789012345678901234567890123456789012345678901234567890 as select 1;
NOTICE: identifier "foo1234567890123456789012345678901234567890123456789012345678901234567890" will be truncated to "foo123456789012345678901234567890123456789012345678901234567890"
PREPARE
Sorry, when I said silently I should've say that the message level is too low. A human would easily understand the NOTICE message but an application code doesn't. And that was the case here.
I don't know how an application could follow what the NOTICE says here and most NOTICE are harmless. I think most drivers just ignore NOTICE messages at runtime or throw it on logs and that will never be caught by automated alert systems since they're just NOTICE.
In my case, it goes down to the level of the library used to talk to PostgreSQL, it's the pgx library largely used in Go projects. Developers in my company will submit a patch there to avoid the situation but I would like improve things at the PostgreSQL side since I'm a DBA.
I know this is not the right list to discuss this but maybe an ERROR level would be better, completely aborting the command and the transaction would completely avoid the situation and handling at the application side would be complete.
> Wouldn't it be good to include that information on the prepared statement
> doc page?
You could as well argue that every single page that describes creating
any sort of object ought to repeat this information. I doubt that people
would find that to be an improvement.
If that is a good thing, I can propose a doc patch at least for the SQL language pages.
Flavio Gurgel
Flavio Henrique Araque Gurgel <fhagur@gmail.com> writes:
> Em ter, 28 de ago de 2018 às 18:21, Tom Lane <tgl@sss.pgh.pa.us> escreveu:
>> You could as well argue that every single page that describes creating
>> any sort of object ought to repeat this information. I doubt that people
>> would find that to be an improvement.
> I'll never argue repeating the same information everywhere in the docs but
> a good solution would be to link the identifiers section everytime an
> identifier is used in the docs.
That seems like an impossible level of clutter, too.
regards, tom lane