Thread: BUG #13736: pg_dump should use E'' quotes
The following bug has been logged on the website:
Bug reference: 13736
Logged by: Felipe Gasper
Email address: felipe@felipegasper.com
PostgreSQL version: 9.4.5
Operating system: Linux
Description:
When dumping a DB whose name has a backslash in it, I get a warning like:
------
pg_dump: WARNING: nonstandard use of \\ in a string literal
LINE 1: ...) AS description FROM pg_database WHERE datname = 'i have / ...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
------
pg_dump should be using PostgreSQLâs own recommended syntax, should it not?
Is there anything that would break from making this change?
felipe@felipegasper.com writes:
> When dumping a DB whose name has a backslash in it, I get a warning like:
> ------
> pg_dump: WARNING: nonstandard use of \\ in a string literal
> LINE 1: ...) AS description FROM pg_database WHERE datname = 'i have / ...
> ^
> HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
> ------
It took me some time to reproduce that, but I eventually realized that
you must have standard_conforming_strings turned off in your database
settings. This has been a deprecated setting since 9.1.
> pg_dump should be using PostgreSQLâs own recommended syntax, should it not?
> Is there anything that would break from making this change?
Yes. For one thing, there would immediately be zero chance of loading
view definitions produced by pg_dump into any other DBMS, or even into old
versions of PG, without painstaking hand-editing to remove all the E's
(and then also fix the string contents, which would likely be actively
wrong without E).
We could avoid the problem by having pg_dump force
standard_conforming_strings to ON rather than adopting the prevailing
database setting, but again that would complicate back-porting its output
to older PG versions. It might also annoy people who are accustomed to
seeing old-style strings in their dumps; presumably people who are still
using standard_conforming_strings = OFF are a bit set in their ways.
Basically there are a number of tradeoffs here and avoiding a purely
cosmetic warning is the consideration that loses out.
At some point we might decide that backward compatibility to old PG
versions is no longer of interest; but what we'd probably do then is have
pg_dump force standard_conforming_strings to ON, not adopt E'' syntax.
regards, tom lane
On Mon, Oct 26, 2015 at 5:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > felipe@felipegasper.com writes: > > When dumping a DB whose name has a backslash in it, I get a warning lik= e: > > > ------ > > pg_dump: WARNING: nonstandard use of \\ in a string literal > > LINE 1: ...) AS description FROM pg_database WHERE datname =3D 'i have = / > ... > > ^ > > HINT: Use the escape string syntax for backslashes, e.g., E'\\'. > > ------ > > It took me some time to reproduce that, but I eventually realized that > you must have standard_conforming_strings turned off in your database > settings. =E2=80=8B[...] =E2=80=8B > > Yes. For one thing, there would immediately be zero chance of loading > view definitions produced by pg_dump into any other DBMS, Ironic...=E2=80=8Bwe cannot write a standard conforming string out because = we are concerned other databases will be unable to read it. The OP is advised to set "escape_string_warning" to "off" if they also wish to have "standard_conforming_strings" set to "off". The question then is whether we should do so during restore regardless of whether the user has done so. David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Oct 26, 2015 at 5:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yes. For one thing, there would immediately be zero chance of loading
>> view definitions produced by pg_dump into any other DBMS,
> Ironic...âwe cannot write a standard conforming string out because we are
> concerned other databases will be unable to read it.
Hm? The E'' syntax would specifically *not* be standard conforming.
regards, tom lane
On 26 Oct 2015 4:55 PM, David G. Johnston wrote: > On Mon, Oct 26, 2015 at 5:33 PM, Tom Lane <tgl@sss.pgh.pa.us > <mailto:tgl@sss.pgh.pa.us>>wrote: > > felipe@felipegasper.com <mailto:felipe@felipegasper.com> writes: > > When dumping a DB whose name has a backslash in it, I get a warning like: > > > ------ > > pg_dump: WARNING: nonstandard use of \\ in a string literal > > LINE 1: ...) AS description FROM pg_database WHERE datname = 'i have / ... > > ^ > > HINT: Use the escape string syntax for backslashes, e.g., E'\\'. > > ------ > > It took me some time to reproduce that, but I eventually realized that > you must have standard_conforming_strings turned off in your database > settings. > > > â[...] > â > > > Yes. For one thing, there would immediately be zero chance of loading > view definitions produced by pg_dump into any other DBMS, > > > Ironic...âwe cannot write a standard conforming string out because we > are concerned other databases will be unable to read it. > > The OP is advised to set "escape_string_warning" to "off" if they also > wish to have "standard_conforming_strings" set to "off". The question > then is whether we should do so during restore regardless of whether the > user has done so. The problem is that I donât control the DB server... -FG
On 2015-10-26 17:06:04 -0500, Felipe Gasper wrote: > The problem is that I donât control the DB server... PGOPTIONS='-c standard_conforming_strings=on -c escape_string_warning=off' pg_dump ought to do the trick.
On Mon, Oct 26, 2015 at 6:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "David G. Johnston" <david.g.johnston@gmail.com> writes: > > On Mon, Oct 26, 2015 at 5:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> Yes. For one thing, there would immediately be zero chance of loading > >> view definitions produced by pg_dump into any other DBMS, > > > Ironic...=E2=80=8Bwe cannot write a standard conforming string out beca= use we are > > concerned other databases will be unable to read it. > > Hm? The E'' syntax would specifically *not* be standard conforming. > =E2=80=8BI apparently always mis-understood what it was getting at...=E2=80= =8B From this I gather that standard conforming strings do not have any concept of =E2=80=8Bslash-=E2=80=8B escaping =E2=80=8B, just the=E2=80=8B doubling-up the uni-quote =E2=80=8B,=E2=80=8B and so we introduced a non-standard version with an "E" prefix that maintains the escaping behavior previously allowed? I think part of my confusing was assuming that the normal was to allow slash-escaping...which if we are outputting such in order to expect external tools to accept the data for input would seem to be a reasonable assumption. David J. =E2=80=8B
On 26 Oct 2015 5:08 PM, Andres Freund wrote: > On 2015-10-26 17:06:04 -0500, Felipe Gasper wrote: >> The problem is that I donât control the DB server... > > PGOPTIONS='-c standard_conforming_strings=on -c escape_string_warning=off' pg_dump > > ought to do the trick. > Confirmed -- thank you! :) -F
On 10/26/15 5:33 PM, Tom Lane wrote: > We could avoid the problem by having pg_dump force > standard_conforming_strings to ON rather than adopting the prevailing > database setting, It does do that. What it doesn't do is set escape_string_warning, which is really what the reporter would need. (There is code in pg_dump to do it, but it apparently only runs when standard_confirming_string is not set. I haven't traced what that code is really meant to do.)