Psql command-line completion bug

If you hit tab on a table name containing a \ you get spammed with a series of
WARNINGS and HINTS about nonstandard use of \\ in a string literal:

postgres=# select * from bar\baz<<TAB>>

WARNING: nonstandard use of \\ in a string literal
LINE 1: ... substring(pg_catalog.quote_ident(c.relname),1,7)='bar\\baz'...
^
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,7)='bar\\baz'...
^
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,7) = substring('bar\\baz'...
^
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,7)='bar\\baz'...
^
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,7) = substring('bar\\baz'...
^
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,7) = substring('bar\\baz'...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.

There are a few options here:

1) Use E'' in all the psql completion queries. This means they won't work on
older versions of postgres (but they don't in general do so anyways). It would
also break anybody who set standard_conforming_string = 'on'. Ideally we would
want to use E'' and then pass false directly to PQEscapeStringInternal but
that's a static function.

2) Use $$%s$$ style quoting. Then we don't need to escape the strings at all.
We would probably have to move all the quoting outside the C strings and
borrow the function from pg_dump to generate the quoting as part of sprintf
parameter substitution.

3) set standards_conforming_strings=on for psql tab-completion queries and
then reset it afterwards. That way we can just use plain standard-conforming
'' and not get any warnings.

4) Replace PQExec with PQExecParam in tab-complete.c

Personally I think (4) is the best long-term option but at this point that
doesn't seem feasible. (3) or (2) seems the next best option.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

[ catching up on email... ]

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> If you hit tab on a table name containing a \ you get spammed with a
> series of WARNINGS and HINTS about nonstandard use of \\ in a string
> literal:

Yeah. This is actually a generic problem for *all* applications using
PQescapeString or PQescapeStringConn: it's impossible for those routines
to suppress the warning, since they don't get to put E'' around their
output.

This class of problem isn't really going to go away until
standard_conforming_strings is usually ON. I'm not sure I want to see
it ON by default in 8.4, but it'll happen eventually. Given that that's
where things are going, maybe extreme effort to fix the problem now
isn't warranted.

> There are a few options here:

> 1) Use E'' in all the psql completion queries. This means they won't work on
> older versions of postgres (but they don't in general do so anyways). It would
> also break anybody who set standard_conforming_string = 'on'.

Huh? It'll work fine with standard_conforming_strings ON. The
backward-compatibility problem is a bigger one. Right now, tab
completion (at least for table names) works fine with servers back to
7.3. If we depend on E'' then it would only work back to 8.1.

> 2) Use $$%s$$ style quoting.

This would work back to 8.0, which is better but not by much. And as
you note it'd be a PITA from a programming point of view.

> 3) set standards_conforming_strings=on for psql tab-completion queries and
> then reset it afterwards.

Ugh :-(

> 4) Replace PQExec with PQExecParam in tab-complete.c

Doable but notationally tedious.

Another idea that comes to mind is to suppress warning messages during
tab completion, by temporarily altering the libpq notice-processor hook.
This is pretty ugly too but would at least be a localized change.

regards, tom lane

Added to TODO:

o Prevent escape string warnings when object names have
backslashes

http://archives.postgresql.org/pgsql-hackers/2008-01/msg00227.php

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

Gregory Stark wrote:
>
> If you hit tab on a table name containing a \ you get spammed with a series of
> WARNINGS and HINTS about nonstandard use of \\ in a string literal:
>
> postgres=# select * from bar\baz<<TAB>>
>
> WARNING: nonstandard use of \\ in a string literal
> LINE 1: ... substring(pg_catalog.quote_ident(c.relname),1,7)='bar\\baz'...
> ^
> 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,7)='bar\\baz'...
> ^
> 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,7) = substring('bar\\baz'...
> ^
> 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,7)='bar\\baz'...
> ^
> 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,7) = substring('bar\\baz'...
> ^
> 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,7) = substring('bar\\baz'...
> ^
> HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
>
>
> There are a few options here:
>
> 1) Use E'' in all the psql completion queries. This means they won't work on
> older versions of postgres (but they don't in general do so anyways). It would
> also break anybody who set standard_conforming_string = 'on'. Ideally we would
> want to use E'' and then pass false directly to PQEscapeStringInternal but
> that's a static function.
>
> 2) Use $$%s$$ style quoting. Then we don't need to escape the strings at all.
> We would probably have to move all the quoting outside the C strings and
> borrow the function from pg_dump to generate the quoting as part of sprintf
> parameter substitution.
>
> 3) set standards_conforming_strings=on for psql tab-completion queries and
> then reset it afterwards. That way we can just use plain standard-conforming
> '' and not get any warnings.
>
> 4) Replace PQExec with PQExecParam in tab-complete.c
>
> Personally I think (4) is the best long-term option but at this point that
> doesn't seem feasible. (3) or (2) seems the next best option.
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
> Ask me about EnterpriseDB's On-Demand Production Tuning
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +