[PATCH] Doc: document standard_conforming_strings dump/restore incompatibility
Hi,
Commit 45762084 [1]https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=45762084545ec14dbbe66ace1d69d7e89f8978ac forced standard_conforming_strings to always be ON in
PostgreSQL 19. The release notes mention this change, but neither the
pg_dump/pg_restore reference pages nor the migration section note the
implications for restoring older dump files.
Dump files produced by older pg_dump versions from servers that had
standard_conforming_strings = off contain "SET standard_conforming_strings
= off" in the output. When restored into v19:
1/ Default (COPY format): pg_restore continues past the SET error and
data restores correctly, but exits with non-zero status. With
--exit-on-error, the restore aborts entirely.
2/ --inserts format: string literals containing backslashes may not be
restored correctly, since the escaping conventions differ between
standard_conforming_strings = off (source) and on (target).
The workaround is to restore into a pre-v19 server first, then produce
a fresh dump using v19 pg_dump (which forces scs=on in the source
session).
I verified this by dumping from PG18 with standard_conforming_strings =
off and restoring into PG19devel. The COPY case works because COPY has
its own escape rules independent of standard_conforming_strings. The
--inserts case results in double backslashes in the restored data.
The attached patch adds notes to:
- pg_dump reference page (Notes section)
- pg_restore reference page (Notes section)
- release-19 migration section
The patch applies cleanly on current HEAD and compiles without errors.
Discussion: /messages/by-id/3279216.1767072538@sss.pgh.pa.us
[1]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=45762084545ec14dbbe66ace1d69d7e89f8978ac
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=45762084545ec14dbbe66ace1d69d7e89f8978ac
Thanks,
Baji Shaik
AWS RDS
Attachments:
v1-0001-Doc-standard_conforming_strings-dump-restore-incompatibility.patchapplication/octet-stream; name=v1-0001-Doc-standard_conforming_strings-dump-restore-incompatibility.patchDownload+34-1
[RMT hat]
On Mon, May 11, 2026 at 05:21:57PM -0500, Baji Shaik wrote:
Commit 45762084 [1] forced standard_conforming_strings to always be ON in
PostgreSQL 19. The release notes mention this change, but neither the
pg_dump/pg_restore reference pages nor the migration section note the
implications for restoring older dump files.
There's an open item for this with Tom listed as the owner. Tom, have you
had a chance to take a look at this?
--
nathan
Nathan Bossart <nathandbossart@gmail.com> writes:
[RMT hat]
On Mon, May 11, 2026 at 05:21:57PM -0500, Baji Shaik wrote:Commit 45762084 [1] forced standard_conforming_strings to always be ON in
PostgreSQL 19. The release notes mention this change, but neither the
pg_dump/pg_restore reference pages nor the migration section note the
implications for restoring older dump files.
There's an open item for this with Tom listed as the owner. Tom, have you
had a chance to take a look at this?
Oh, I didn't know this'd been assigned to me. Will look.
regards, tom lane
Baji Shaik <baji.pgdev@gmail.com> writes:
Commit 45762084 [1] forced standard_conforming_strings to always be ON in
PostgreSQL 19. The release notes mention this change, but neither the
pg_dump/pg_restore reference pages nor the migration section note the
implications for restoring older dump files.
Okay, that's an oversight in the release notes for sure. I'm more
skeptical about putting such information into the application
reference pages though. It seems of only transient interest, but
any text we put there is likely to survive for many years.
Independently of that...
The attached patch adds notes to:
- pg_dump reference page (Notes section)
- pg_restore reference page (Notes section)
- release-19 migration section
I don't like this patch, because it presents much too rosy a view of
whether you can get away with not re-taking the dump. It's unsafe
even with COPY-format data, because string literals in views, table
default expressions and constraints, function definitions, etc are all
at hazard of being misinterpreted. I think we should just say it
won't work correctly, full stop.
I'm inclined to do more or less the attached, which also fixes what
seems a thinko in the existing text: we should say that clients still
support standard_conforming_strings = off, not
escape_string_warning = off.
regards, tom lane
Attachments:
v2-document-dump-restore-hazard.patchtext/x-diff; charset=us-ascii; name=v2-document-dump-restore-hazard.patchDownload+12-1
On Wed, Jun 17, 2026 at 12:29 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'm inclined to do more or less the attached, which also fixes what
seems a thinko in the existing text: we should say that clients still
support standard_conforming_strings = off, not
escape_string_warning = off.
Thanks Tom. Good point about views, defaults, and function bodies.
I was only thinking about the data rows. Your v2 looks good to me.
Thanks,
Baji Shaik.
On Wed, Jun 17, 2026 at 01:29:09PM -0400, Tom Lane wrote:
Baji Shaik <baji.pgdev@gmail.com> writes:
Commit 45762084 [1] forced standard_conforming_strings to always be ON in
PostgreSQL 19. The release notes mention this change, but neither the
pg_dump/pg_restore reference pages nor the migration section note the
implications for restoring older dump files.Okay, that's an oversight in the release notes for sure. I'm more
skeptical about putting such information into the application
reference pages though. It seems of only transient interest, but
any text we put there is likely to survive for many years.Independently of that...
The attached patch adds notes to:
- pg_dump reference page (Notes section)
- pg_restore reference page (Notes section)
- release-19 migration sectionI don't like this patch, because it presents much too rosy a view of
whether you can get away with not re-taking the dump. It's unsafe
even with COPY-format data, because string literals in views, table
default expressions and constraints, function definitions, etc are all
at hazard of being misinterpreted. I think we should just say it
won't work correctly, full stop.I'm inclined to do more or less the attached, which also fixes what
seems a thinko in the existing text: we should say that clients still
support standard_conforming_strings = off, not
escape_string_warning = off.
I have applied a slightly modified version to master, attached. The git
commit suggested this problem, but I didn't understand the impact at the
time I read it.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
Attachments:
master.difftext/x-diff; charset=us-asciiDownload+8-1
Bruce Momjian <bruce@momjian.us> writes:
On Wed, Jun 17, 2026 at 01:29:09PM -0400, Tom Lane wrote:
I'm inclined to do more or less the attached, which also fixes what
seems a thinko in the existing text: we should say that clients still
support standard_conforming_strings = off, not
escape_string_warning = off.
I have applied a slightly modified version to master, attached. The git
commit suggested this problem, but I didn't understand the impact at the
time I read it.
This didn't correct the point I complained of above: it still says
Client applications still support <literal>escape_string_warning = off</literal> for compatibility with old servers.
The notion of a client "supporting" escape_string_warning seems quite
meaningless. I think this intended to say standard_conforming_strings =
off.
regards, tom lane
On Thu, Jun 18, 2026 at 11:34:46AM -0400, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
On Wed, Jun 17, 2026 at 01:29:09PM -0400, Tom Lane wrote:
I'm inclined to do more or less the attached, which also fixes what
seems a thinko in the existing text: we should say that clients still
support standard_conforming_strings = off, not
escape_string_warning = off.I have applied a slightly modified version to master, attached. The git
commit suggested this problem, but I didn't understand the impact at the
time I read it.This didn't correct the point I complained of above: it still says
Client applications still support <literal>escape_string_warning = off</literal> for compatibility with old servers.
The notion of a client "supporting" escape_string_warning seems quite
meaningless. I think this intended to say standard_conforming_strings =
off.
Ah, yes, I see my mistake now. Attached patch applied using your wording.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.