Thread: BUG #6624: Tab completion of identifier containing single backslash triggers warnings

The following bug has been logged on the website:

Bug reference:      6624
Logged by:          Stuart Bishop
Email address:      stuart@stuartbishop.net
PostgreSQL version: 9.1.3
Operating system:   Ununtu
Description:=20=20=20=20=20=20=20=20

psql tab completion can emit WARNING messages if escape_string_warning is
set to true. The trigger seems to be an identifier containing a single
backslash.

# CREATE SEQUENCE "\foo";
CREATE SEQUENCE
# \ds "\

(at this point, hit <TAB> to trigger completion)

# drop sequence "\WARNING:  nonstandard use of \\ in a string literal
LINE 1: ... substring(pg_catalog.quote_ident(c.relname),1,2)=3D'"\\' AND ...
                                                             ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING:  nonstandard use of \\ in a string literal
LINE 3: ...ing(pg_catalog.quote_ident(n.nspname) || '.',1,2)=3D'"\\' AND ...
                                                             ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING:  nonstandard use of \\ in a string literal
LINE 3: ...alog.quote_ident(nspname) || '.',1,2) =3D substring('"\\',1,pg...
                                                             ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING:  nonstandard use of \\ in a string literal
LINE 5: ... || '.' || pg_catalog.quote_ident(c.relname),1,2)=3D'"\\' AND ...
                                                             ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING:  nonstandard use of \\ in a string literal
LINE 5: ...og.quote_ident(n.nspname) || '.',1,2) =3D substring('"\\',1,pg...
                                                             ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING:  nonstandard use of \\ in a string literal
LINE 5: ...alog.quote_ident(nspname) || '.',1,2) =3D substring('"\\',1,pg...
                                                             ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
foo" ;

On Wed, May 02, 2012 at 12:59:58PM +0000, stuart@stuartbishop.net wrote:
> The following bug has been logged on the website:
>
> Bug reference:      6624
> Logged by:          Stuart Bishop
> Email address:      stuart@stuartbishop.net
> PostgreSQL version: 9.1.3
> Operating system:   Ununtu
> Description:
>
> psql tab completion can emit WARNING messages if escape_string_warning is
> set to true. The trigger seems to be an identifier containing a single
> backslash.
>
> # CREATE SEQUENCE "\foo";
> CREATE SEQUENCE
> # \ds "\

I am unable to reproduce this failure on my copy of 9.1.3.  Have you
perhaps changed any server settings?

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


>
> (at this point, hit <TAB> to trigger completion)
>
> # drop sequence "\WARNING:  nonstandard use of \\ in a string literal
> LINE 1: ... substring(pg_catalog.quote_ident(c.relname),1,2)='"\\' AND ...
>                                                              ^
> HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
> WARNING:  nonstandard use of \\ in a string literal
> LINE 3: ...ing(pg_catalog.quote_ident(n.nspname) || '.',1,2)='"\\' AND ...
>                                                              ^
> HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
> WARNING:  nonstandard use of \\ in a string literal
> LINE 3: ...alog.quote_ident(nspname) || '.',1,2) = substring('"\\',1,pg...
>                                                              ^
> HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
> WARNING:  nonstandard use of \\ in a string literal
> LINE 5: ... || '.' || pg_catalog.quote_ident(c.relname),1,2)='"\\' AND ...
>                                                              ^
> HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
> WARNING:  nonstandard use of \\ in a string literal
> LINE 5: ...og.quote_ident(n.nspname) || '.',1,2) = substring('"\\',1,pg...
>                                                              ^
> HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
> WARNING:  nonstandard use of \\ in a string literal
> LINE 5: ...alog.quote_ident(nspname) || '.',1,2) = substring('"\\',1,pg...
>                                                              ^
> HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
> foo" ;
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On Wed, May 2, 2012 at 11:21 AM, Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, May 02, 2012 at 12:59:58PM +0000, stuart@stuartbishop.net wrote:
>> # CREATE SEQUENCE "\foo";
>> CREATE SEQUENCE
>> # \ds "\
>
> I am unable to reproduce this failure on my copy of 9.1.3. =A0Have you
> perhaps changed any server settings?

I only get the error if I:
  SET standard_conforming_strings TO off;

otherwise, it works fine. Perhaps it's still worth fixing though.

Josh

Josh Kupershmidt <schmiddy@gmail.com> writes:
> On Wed, May 2, 2012 at 11:21 AM, Bruce Momjian <bruce@momjian.us> wrote:
>> On Wed, May 02, 2012 at 12:59:58PM +0000, stuart@stuartbishop.net wrote:
>>> # CREATE SEQUENCE "\foo";
>>> CREATE SEQUENCE
>>> # \ds "\

>> I am unable to reproduce this failure on my copy of 9.1.3.  Have you
>> perhaps changed any server settings?

> I only get the error if I:
>   SET standard_conforming_strings TO off;
> otherwise, it works fine. Perhaps it's still worth fixing though.

AFAICT, you get this behavior in every release back to 8.1, if you have
both escape_string_warning ON and standard_conforming_strings OFF;
which was the default configuration from 8.2 through 9.0.  The lack of
prior complaints suggests pretty strongly that backslashes in names are
not common in the field.

The only way we could suppress such warnings would be if we made
tab-complete.c use E'' strings for literals containing name prefixes;
which is perhaps doable but it would mean having tab-complete.c roll
its own string escaping rather than use any facility now available
from libpq.  (PQescapeString and friends presume that the result will
be emitted as a plain string.)

This issue isn't peculiar to tab-complete.c, either; in general, *any*
client that uses PQescapeString and friends is going to get bombarded
with this type of warning when the server is configured that way, even
though what it's doing is perfectly safe.  I don't remember at the
moment why we designed those functions that way, but presumably the
alternatives were worse.

I'm inclined to think that if we got this far without complaint, there's
not a lot of point in writing new string-escaping support to solve what
is now a legacy problem.

            regards, tom lane

On Thu, May 3, 2012 at 5:29 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> I'm inclined to think that if we got this far without complaint, there's
> not a lot of point in writing new string-escaping support to solve what
> is now a legacy problem.

I'm fine with that - I wasn't even sure if I should bother reporting
the issue, except for the chance that this bug might have deeper
impact I hadn't thought of.

--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/

On Wed, May 2, 2012 at 6:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The only way we could suppress such warnings would be if we made
> tab-complete.c use E'' strings for literals containing name prefixes;
> which is perhaps doable but it would mean having tab-complete.c roll
> its own string escaping rather than use any facility now available
> from libpq.

PQescapeLiteral will do the job, no?  At least in 9.0+.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, May 2, 2012 at 6:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The only way we could suppress such warnings would be if we made
>> tab-complete.c use E'' strings for literals containing name prefixes;
>> which is perhaps doable but it would mean having tab-complete.c roll
>> its own string escaping rather than use any facility now available
>> from libpq.

> PQescapeLiteral will do the job, no?  At least in 9.0+.

Hmm ... it would, but then psql would fail entirely when talking to
pre-8.1 servers, so we'd need to check the server version to decide
which quoting method to use.  Do you think this is important enough to
add yet more version-specific tests to that code?

            regards, tom lane

On Thu, May 10, 2012 at 9:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Wed, May 2, 2012 at 6:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> The only way we could suppress such warnings would be if we made
>>> tab-complete.c use E'' strings for literals containing name prefixes;
>>> which is perhaps doable but it would mean having tab-complete.c roll
>>> its own string escaping rather than use any facility now available
>>> from libpq.
>
>> PQescapeLiteral will do the job, no? =A0At least in 9.0+.
>
> Hmm ... it would, but then psql would fail entirely when talking to
> pre-8.1 servers, so we'd need to check the server version to decide
> which quoting method to use. =A0Do you think this is important enough to
> add yet more version-specific tests to that code?

That's a good question.  I don't think I personally have the time in
my budget to go write the code for this, but I wouldn't complain if
somebody else went and made it work.

Another question is - what exactly is our ambition in terms of
maintaining psql compatibility with old versions?  According to the
header comment in describe.c, right now we're aiming for compatibility
with anything >=3D 7.4.  Presumably at some point we're going to throw
7.4 and 8.0 under the bus, at which point we could do this more
simply, but I'm not sure how long we want to want to wait before doing
that.  If, for example, we're going to be willing to pull the plug in
9.3, then it's probably not worth putting in any effort now.  But if
we're not going to be willing to pull the plug for another 5 years,
maybe it is.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company