BUG #13736: pg_dump should use E'' quotes

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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 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.

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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...​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.

​I apparently always mis-understood what it was getting at...​

From this I gather that standard conforming strings do not have any concept
of
​slash-​
escaping
​, just the​
doubling-up the uni-quote
​,​
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.
​

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs