MIME-Version: 1.0
References: 
 <CAGHTAeOxqeojgvWEuciRvq1xbZYHKK6mGzs=g2rQ8upM3mwVWA@mail.gmail.com>
 <28416.1535473275@sss.pgh.pa.us>
In-Reply-To: <28416.1535473275@sss.pgh.pa.us>
From: Flavio Henrique Araque Gurgel <fhagur@gmail.com>
Date: Thu, 30 Aug 2018 16:23:14 +0200
Message-ID: 
 <CAGHTAeMG7LRBu+sq5Xoz9TKMuhghzpG5uu9FkX=nATC4X4JBmg@mail.gmail.com>
Subject: Re: Limitation of prepared statement name
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-docs@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000972a0a0574a7d105"
Precedence: bulk

--000000000000972a0a0574a7d105
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Em ter, 28 de ago de 2018 =C3=A0s 18:21, Tom Lane <tgl@sss.pgh.pa.us> escre=
veu:

> 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#SQ=
L-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=3D# prepare
> foo1234567890123456789012345678901234567890123456789012345678901234567890
> as select 1;
> NOTICE:  identifier
> "foo123456789012345678901234567890123456789012345678901234567890123456789=
0"
> 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 stateme=
nt
> > 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.
>

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 would ease the task for developers
that are used to read PostgreSQL docs in a daily basis.

If that is a good thing, I can propose a doc patch at least for the SQL
language pages.

Flavio Gurgel

--000000000000972a0a0574a7d105
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><br><br><div class=3D"gmail_quote"><div dir=3D"ltr">Em ter=
, 28 de ago de 2018 =C3=A0s 18:21, Tom Lane &lt;<a href=3D"mailto:tgl@sss.p=
gh.pa.us">tgl@sss.pgh.pa.us</a>&gt; escreveu:<br></div><blockquote class=3D=
"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding=
-left:1ex">Flavio Henrique Araque Gurgel &lt;<a href=3D"mailto:fhagur@gmail=
.com" target=3D"_blank">fhagur@gmail.com</a>&gt; writes:<br>
&gt; It seems that naming prepared statements is limited to 63 characters a=
s per<br>
&gt; <a href=3D"https://www.postgresql.org/docs/current/static/sql-syntax-l=
exical.html#SQL-SYNTAX-IDENTIFIERS" rel=3D"noreferrer" target=3D"_blank">ht=
tps://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SY=
NTAX-IDENTIFIERS</a><br>
<br>
Yeah, just like every other SQL identifier in Postgres...<br>
<br>
&gt; The dangerous situation is that PostgreSQL seems to ignore rigthmost<b=
r>
&gt; overflowed characters silently.<br>
<br>
It&#39;s hardly silent:<br>
<br>
regression=3D# prepare foo1234567890123456789012345678901234567890123456789=
012345678901234567890 as select 1;<br>
NOTICE:=C2=A0 identifier &quot;foo12345678901234567890123456789012345678901=
23456789012345678901234567890&quot; will be truncated to &quot;foo123456789=
012345678901234567890123456789012345678901234567890&quot;<br>
PREPARE<br></blockquote><div><br></div><div>Sorry, when I said silently I s=
hould&#39;ve say that the message level is too low. A human would easily un=
derstand the NOTICE message but an application code doesn&#39;t. And that w=
as the case here.</div><div><br></div><div>I don&#39;t know how an applicat=
ion 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 lo=
gs and that will never be caught by automated alert systems since they&#39;=
re just NOTICE.<br></div><div><br></div><div>In my case, it goes down to th=
e level of the library used to talk to PostgreSQL, it&#39;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 Postgre=
SQL side since I&#39;m a DBA.<br></div><div>=C2=A0</div><div>I know this is=
 not the right list to discuss this but maybe an ERROR level would be bette=
r, completely aborting the command and the transaction would completely avo=
id the situation and handling at the application side would be complete.<br=
></div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0=
 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
&gt; Wouldn&#39;t it be good to include that information on the prepared st=
atement<br>
&gt; doc page?<br>
<br>
You could as well argue that every single page that describes creating<br>
any sort of object ought to repeat this information.=C2=A0 I doubt that peo=
ple<br>
would find that to be an improvement.<br></blockquote><div><br></div>I&#39;=
ll never argue repeating the same information everywhere in the docs but a =
good solution would be to link the identifiers section everytime an identif=
ier is used in the docs. That would ease the task for developers that are u=
sed to read PostgreSQL docs in a daily basis.</div><div class=3D"gmail_quot=
e"><br></div><div class=3D"gmail_quote">If that is a good thing, I can prop=
ose a doc patch at least for the SQL language pages.<br></div><div class=3D=
"gmail_quote"><br></div><div class=3D"gmail_quote">Flavio Gurgel<br></div><=
/div>

--000000000000972a0a0574a7d105--