agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feed[PATCH v6 3/3] Expose WAL usage counters in verbose (auto)vacuum output.
11+ messages / 5 participants
[nested] [flat]
* [PATCH v6 3/3] Expose WAL usage counters in verbose (auto)vacuum output.
@ 2020-03-19 15:08 Julien Rouhaud <julien.rouhaud@free.fr>
0 siblings, 0 replies; 11+ messages in thread
From: Julien Rouhaud @ 2020-03-19 15:08 UTC (permalink / raw)
---
src/backend/access/heap/vacuumlazy.c | 29 +++++++++++++++++++++++++++-
1 file changed, 28 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 03c43efc32..32e6023738 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -65,6 +65,7 @@
#include "commands/dbcommands.h"
#include "commands/progress.h"
#include "commands/vacuum.h"
+#include "executor/instrument.h"
#include "miscadmin.h"
#include "optimizer/paths.h"
#include "pgstat.h"
@@ -381,6 +382,8 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
int nindexes;
PGRUsage ru0;
TimestampTz starttime = 0;
+ WalUsage walusage_start = pgWalUsage;
+ WalUsage walusage = {0, 0, 0};
long secs;
int usecs;
double read_rate,
@@ -569,6 +572,12 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
TimestampDifference(starttime, endtime, &secs, &usecs);
+ walusage.wal_records = pgWalUsage.wal_records -
+ walusage_start.wal_records;
+ walusage.wal_fp_records = pgWalUsage.wal_fp_records -
+ walusage_start.wal_fp_records;
+ walusage.wal_bytes = pgWalUsage.wal_bytes - walusage_start.wal_bytes;
+
read_rate = 0;
write_rate = 0;
if ((secs > 0) || (usecs > 0))
@@ -620,7 +629,12 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
(long long) VacuumPageDirty);
appendStringInfo(&buf, _("avg read rate: %.3f MB/s, avg write rate: %.3f MB/s\n"),
read_rate, write_rate);
- appendStringInfo(&buf, _("system usage: %s"), pg_rusage_show(&ru0));
+ appendStringInfo(&buf, _("system usage: %s\n"), pg_rusage_show(&ru0));
+ appendStringInfo(&buf,
+ _("WAL usage: %ld records, %ld full page records, %ld bytes"),
+ walusage.wal_records,
+ walusage.wal_fp_records,
+ walusage.wal_bytes);
ereport(LOG,
(errmsg_internal("%s", buf.data)));
@@ -713,6 +727,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
IndexBulkDeleteResult **indstats;
int i;
PGRUsage ru0;
+ WalUsage walusage_start = pgWalUsage;
+ WalUsage walusage = {0, 0, 0};
Buffer vmbuffer = InvalidBuffer;
BlockNumber next_unskippable_block;
bool skipping_blocks;
@@ -1690,6 +1706,17 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
"%u pages are entirely empty.\n",
empty_pages),
empty_pages);
+
+ walusage.wal_records = pgWalUsage.wal_records -
+ walusage_start.wal_records;
+ walusage.wal_fp_records = pgWalUsage.wal_fp_records -
+ walusage_start.wal_fp_records;
+ walusage.wal_bytes = pgWalUsage.wal_bytes - walusage_start.wal_bytes;
+ appendStringInfo(&buf, _("%ld WAL records, %ld WAL full page records, %ld WAL bytes\n"),
+ walusage.wal_records,
+ walusage.wal_fp_records,
+ walusage.wal_bytes);
+
appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0));
ereport(elevel,
--
2.20.1
--ftEhullJWpWg/VHq--
^ permalink raw reply [nested|flat] 11+ messages in thread
* [PATCH v7 4/4] Expose WAL usage counters in verbose (auto)vacuum output.
@ 2020-03-19 15:08 Julien Rouhaud <julien.rouhaud@free.fr>
0 siblings, 0 replies; 11+ messages in thread
From: Julien Rouhaud @ 2020-03-19 15:08 UTC (permalink / raw)
Author: Julien Rouhaud
Reviewed-by: Fuji Masao
Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com
---
src/backend/access/heap/vacuumlazy.c | 25 ++++++++++++++++++++++++-
1 file changed, 24 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 03c43efc32..ca4f03f551 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -65,6 +65,7 @@
#include "commands/dbcommands.h"
#include "commands/progress.h"
#include "commands/vacuum.h"
+#include "executor/instrument.h"
#include "miscadmin.h"
#include "optimizer/paths.h"
#include "pgstat.h"
@@ -381,6 +382,8 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
int nindexes;
PGRUsage ru0;
TimestampTz starttime = 0;
+ WalUsage walusage_start = pgWalUsage;
+ WalUsage walusage = {0, 0, 0};
long secs;
int usecs;
double read_rate,
@@ -569,6 +572,9 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
TimestampDifference(starttime, endtime, &secs, &usecs);
+ memset(&walusage, 0, sizeof(WalUsage));
+ WalUsageAccumDiff(&walusage, &pgWalUsage, &walusage_start);
+
read_rate = 0;
write_rate = 0;
if ((secs > 0) || (usecs > 0))
@@ -620,7 +626,13 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
(long long) VacuumPageDirty);
appendStringInfo(&buf, _("avg read rate: %.3f MB/s, avg write rate: %.3f MB/s\n"),
read_rate, write_rate);
- appendStringInfo(&buf, _("system usage: %s"), pg_rusage_show(&ru0));
+ appendStringInfo(&buf, _("system usage: %s\n"), pg_rusage_show(&ru0));
+ appendStringInfo(&buf,
+ _("WAL usage: %ld records, %ld full page records, "
+ UINT64_FORMAT " bytes"),
+ walusage.wal_records,
+ walusage.wal_fpw_records,
+ walusage.wal_bytes);
ereport(LOG,
(errmsg_internal("%s", buf.data)));
@@ -713,6 +725,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
IndexBulkDeleteResult **indstats;
int i;
PGRUsage ru0;
+ WalUsage walusage_start = pgWalUsage;
+ WalUsage walusage = {0, 0, 0};
Buffer vmbuffer = InvalidBuffer;
BlockNumber next_unskippable_block;
bool skipping_blocks;
@@ -1690,6 +1704,15 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
"%u pages are entirely empty.\n",
empty_pages),
empty_pages);
+
+ memset(&walusage, 0, sizeof(WalUsage));
+ WalUsageAccumDiff(&walusage, &pgWalUsage, &walusage_start);
+ appendStringInfo(&buf, _("%ld WAL records, %ld WAL full page records, "
+ UINT64_FORMAT " WAL bytes\n"),
+ walusage.wal_records,
+ walusage.wal_fpw_records,
+ walusage.wal_bytes);
+
appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0));
ereport(elevel,
--
2.20.1
--uAKRQypu60I7Lcqm--
^ permalink raw reply [nested|flat] 11+ messages in thread
* [PATCH v8 4/4] Expose WAL usage counters in verbose (auto)vacuum output.
@ 2020-03-19 15:08 Julien Rouhaud <julien.rouhaud@free.fr>
0 siblings, 0 replies; 11+ messages in thread
From: Julien Rouhaud @ 2020-03-19 15:08 UTC (permalink / raw)
Author: Julien Rouhaud
Reviewed-by: Fuji Masao
Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com
---
src/backend/access/heap/vacuumlazy.c | 25 ++++++++++++++++++++++++-
1 file changed, 24 insertions(+), 1 deletion(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 9726f69629..55df857ff7 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -65,6 +65,7 @@
#include "commands/dbcommands.h"
#include "commands/progress.h"
#include "commands/vacuum.h"
+#include "executor/instrument.h"
#include "miscadmin.h"
#include "optimizer/paths.h"
#include "pgstat.h"
@@ -401,6 +402,8 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
int nindexes;
PGRUsage ru0;
TimestampTz starttime = 0;
+ WalUsage walusage_start = pgWalUsage;
+ WalUsage walusage = {0, 0, 0};
long secs;
int usecs;
double read_rate,
@@ -622,6 +625,9 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
TimestampDifference(starttime, endtime, &secs, &usecs);
+ memset(&walusage, 0, sizeof(WalUsage));
+ WalUsageAccumDiff(&walusage, &pgWalUsage, &walusage_start);
+
read_rate = 0;
write_rate = 0;
if ((secs > 0) || (usecs > 0))
@@ -673,7 +679,13 @@ heap_vacuum_rel(Relation onerel, VacuumParams *params,
(long long) VacuumPageDirty);
appendStringInfo(&buf, _("avg read rate: %.3f MB/s, avg write rate: %.3f MB/s\n"),
read_rate, write_rate);
- appendStringInfo(&buf, _("system usage: %s"), pg_rusage_show(&ru0));
+ appendStringInfo(&buf, _("system usage: %s\n"), pg_rusage_show(&ru0));
+ appendStringInfo(&buf,
+ _("WAL usage: %ld records, %ld full page records, "
+ UINT64_FORMAT " bytes"),
+ walusage.wal_records,
+ walusage.wal_fpw_records,
+ walusage.wal_bytes);
ereport(LOG,
(errmsg_internal("%s", buf.data)));
@@ -765,6 +777,8 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
IndexBulkDeleteResult **indstats;
int i;
PGRUsage ru0;
+ WalUsage walusage_start = pgWalUsage;
+ WalUsage walusage = {0, 0, 0};
Buffer vmbuffer = InvalidBuffer;
BlockNumber next_unskippable_block;
bool skipping_blocks;
@@ -1744,6 +1758,15 @@ lazy_scan_heap(Relation onerel, VacuumParams *params, LVRelStats *vacrelstats,
"%u pages are entirely empty.\n",
empty_pages),
empty_pages);
+
+ memset(&walusage, 0, sizeof(WalUsage));
+ WalUsageAccumDiff(&walusage, &pgWalUsage, &walusage_start);
+ appendStringInfo(&buf, _("%ld WAL records, %ld WAL full page records, "
+ UINT64_FORMAT " WAL bytes\n"),
+ walusage.wal_records,
+ walusage.wal_fpw_records,
+ walusage.wal_bytes);
+
appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0));
ereport(elevel,
--
2.20.1
--DBIVS5p969aUjpLe--
^ permalink raw reply [nested|flat] 11+ messages in thread
* [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility
@ 2026-05-11 22:21 Baji Shaik <baji.pgdev@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Baji Shaik @ 2026-05-11 22:21 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org; +Cc: tgl@sss.pgh.pa.us
Hi,
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.
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: https://postgr.es/m/3279216.1767072538@sss.pgh.pa.us
[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=45762084545ec14dbbe66ace1d69d7e89f897...
Thanks,
Baji Shaik
AWS RDS
Attachments:
[application/octet-stream] v1-0001-Doc-standard_conforming_strings-dump-restore-incompatibility.patch (4.6K, ../../CA+fm-RM8bG0G6SJDaMgWkKmtyf4QG=V5We1ViAG0tZ5PU67rLQ@mail.gmail.com/3-v1-0001-Doc-standard_conforming_strings-dump-restore-incompatibility.patch)
download | inline diff:
From 36293bb2b2449256ef5a4ba874cc786bcf1b699a Mon Sep 17 00:00:00 2001
From: Baji Shaik <baji.pgdev@gmail.com>
Date: Mon, 11 May 2026 07:59:32 -0500
Subject: [PATCH] Doc: document standard_conforming_strings dump/restore
incompatibility
Commit 45762084 forced standard_conforming_strings to always be ON
starting in PostgreSQL 19. This means dump files produced by older
pg_dump versions from servers that had standard_conforming_strings
set to off will produce errors when restored into v19 or later.
While COPY-format dumps restore data correctly despite the SET error,
dumps produced with --inserts may have incorrectly escaped string
literals, resulting in incorrect data being restored.
Add notes to the pg_dump and pg_restore reference pages, and to the
release-19 migration section, documenting this incompatibility and
the workaround (restore into a pre-v19 server first, then re-dump).
Discussion: https://postgr.es/m/3279216.1767072538@sss.pgh.pa.us
---
doc/src/sgml/ref/pg_dump.sgml | 16 ++++++++++++++++
doc/src/sgml/ref/pg_restore.sgml | 17 +++++++++++++++++
doc/src/sgml/release-19.sgml | 1 +
3 files changed, 34 insertions(+)
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index ae1bc14..3d2581a 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1732,6 +1732,22 @@ CREATE DATABASE foo WITH TEMPLATE template0;
reserved-word lists in different <productname>PostgreSQL</productname> versions.
</para>
+ <para>
+ Beginning in <productname>PostgreSQL</productname> 19,
+ <varname>standard_conforming_strings</varname> is always
+ <literal>on</literal> and cannot be set to <literal>off</literal>.
+ When dumping from older servers that have
+ <varname>standard_conforming_strings</varname> set to
+ <literal>off</literal>, a version 19 or later
+ <application>pg_dump</application> will force the setting to
+ <literal>on</literal> during the dump session, ensuring that the
+ output is compatible with version 19 and later. However, dump files
+ previously produced by older versions of <application>pg_dump</application>
+ while <varname>standard_conforming_strings</varname> was
+ <literal>off</literal> may not restore cleanly into version 19 or later;
+ see the <xref linkend="app-pgrestore"/> documentation for details.
+ </para>
+
<para>
When dumping logical replication subscriptions,
<application>pg_dump</application> will generate <command>CREATE
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 5e77ddd..d419866 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -1200,6 +1200,23 @@ CREATE DATABASE foo WITH TEMPLATE template0;
</programlisting>
</para>
+ <para>
+ Dump files produced by <application>pg_dump</application> from servers
+ that had <varname>standard_conforming_strings</varname> set to
+ <literal>off</literal> contain
+ <literal>SET standard_conforming_strings = off</literal>, which will
+ produce an error when restored into <productname>PostgreSQL</productname>
+ 19 or later. By default, <application>pg_restore</application> will
+ continue past this error and restore data correctly when using
+ <command>COPY</command> format, but will exit with a non-zero status.
+ If <option>--exit-on-error</option> is specified, the restore will abort.
+ Dumps produced with <option>--inserts</option> may have incorrectly
+ escaped string literals, resulting in incorrect data being restored.
+ To safely restore such dump files, first restore into a server running
+ a version prior to 19, then produce a fresh dump using a version 19 or
+ later <application>pg_dump</application>.
+ </para>
+
<para>
The limitations of <application>pg_restore</application> are detailed below.
diff --git a/doc/src/sgml/release-19.sgml b/doc/src/sgml/release-19.sgml
index f265ac0..c769672 100644
--- a/doc/src/sgml/release-19.sgml
+++ b/doc/src/sgml/release-19.sgml
@@ -110,6 +110,7 @@ Force standard_conforming_strings to always be "on" in the database server (Tom
<para>
Server variable escape_string_warning has been removed as unnecessary. Client applications still support "standard_conforming_strings = off" for compatibility with old servers.
+Dump files previously produced with standard_conforming_strings set to off may not restore correctly into version 19; in particular, dumps made with <option>--inserts</option> may have incorrectly escaped string literals. To restore such files, first load them into a pre-version-19 server and then produce a fresh dump.
</para>
</listitem>
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility
@ 2026-06-17 15:27 Nathan Bossart <nathandbossart@gmail.com>
parent: Baji Shaik <baji.pgdev@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Nathan Bossart @ 2026-06-17 15:27 UTC (permalink / raw)
To: Baji Shaik <baji.pgdev@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org; tgl@sss.pgh.pa.us
[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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility
@ 2026-06-17 15:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2026-06-17 15:36 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: Baji Shaik <baji.pgdev@gmail.com>; pgsql-hackers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility
@ 2026-06-17 17:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Baji Shaik <baji.pgdev@gmail.com>
1 sibling, 2 replies; 11+ messages in thread
From: Tom Lane @ 2026-06-17 17:29 UTC (permalink / raw)
To: Baji Shaik <baji.pgdev@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org, Bruce Momjian <bruce@momjian.us>
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:
[text/x-diff] v2-document-dump-restore-hazard.patch (1.1K, ../../1131492.1781717349@sss.pgh.pa.us/2-v2-document-dump-restore-hazard.patch)
download | inline diff:
diff --git a/doc/src/sgml/release-19.sgml b/doc/src/sgml/release-19.sgml
index 285bfa123f6..4943885cf01 100644
--- a/doc/src/sgml/release-19.sgml
+++ b/doc/src/sgml/release-19.sgml
@@ -109,7 +109,18 @@ Force <link linkend="guc-standard-conforming-strings"><varname>standard_conformi
</para>
<para>
-Server variable <varname>escape_string_warning</varname> has been removed as unnecessary. Client applications still support <literal>escape_string_warning = off</literal> for compatibility with old servers.
+This carries some risk for upgrades: a dump file produced by
+pre-v19 <application>pg_dump</application> from a server that
+had <literal>standard_conforming_strings = off</literal>
+will not restore correctly into v19 or later. Use
+v19 <application>pg_dump</application> to take the dump, or
+set <literal>standard_conforming_strings = on</literal>
+in the old server before dumping.
+Client applications still support operations with servers
+having <literal>standard_conforming_strings = off</literal>, for
+compatibility with old servers.
+Server variable <varname>escape_string_warning</varname> has been removed
+as unnecessary.
</para>
</listitem>
=
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility
@ 2026-06-17 19:55 Baji Shaik <baji.pgdev@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Baji Shaik @ 2026-06-17 19:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@lists.postgresql.org, Bruce Momjian <bruce@momjian.us>
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility
@ 2026-06-17 23:34 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 11+ messages in thread
From: Bruce Momjian @ 2026-06-17 23:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Baji Shaik <baji.pgdev@gmail.com>; pgsql-hackers@lists.postgresql.org
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 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.
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:
[text/x-diff] master.diff (1.3K, ../../ajMvEd0zXBMN-Bri@momjian.us/2-master.diff)
download | inline diff:
diff --git a/doc/src/sgml/release-19.sgml b/doc/src/sgml/release-19.sgml
index 285bfa123f6..911a198e676 100644
--- a/doc/src/sgml/release-19.sgml
+++ b/doc/src/sgml/release-19.sgml
@@ -109,7 +109,14 @@ Force <link linkend="guc-standard-conforming-strings"><varname>standard_conformi
</para>
<para>
-Server variable <varname>escape_string_warning</varname> has been removed as unnecessary. Client applications still support <literal>escape_string_warning = off</literal> for compatibility with old servers.
+Dumps created using pre-<productname>PostgreSQL</productname> 19 versions of <link linkend="app-pgdump"><application>pg_dump</application></link>
+or <link linkend="app-pg-dumpall"><application>pg_dumpall</application></link>, and using <literal>standard_conforming_strings = off</literal>,
+will not properly load into <productname>PostgreSQL</productname> 19 and later servers. Users should create dumps using <productname>PostgreSQL</productname> 19
+or later versions of these applications, or use <literal>standard_conforming_strings = on</literal>.
+</para>
+
+<para>
+Client applications still support <literal>escape_string_warning = off</literal> for compatibility with old servers. The server variable <varname>escape_string_warning</varname> has been removed as unnecessary.
</para>
</listitem>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility
@ 2026-06-18 15:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2026-06-18 15:34 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Baji Shaik <baji.pgdev@gmail.com>; pgsql-hackers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility
@ 2026-06-18 16:20 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Bruce Momjian @ 2026-06-18 16:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Baji Shaik <baji.pgdev@gmail.com>; pgsql-hackers@lists.postgresql.org
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.
Attachments:
[text/x-diff] master.diff (788B, ../../ajQav8zUY0IHZ0aX@momjian.us/2-master.diff)
download | inline diff:
diff --git a/doc/src/sgml/release-19.sgml b/doc/src/sgml/release-19.sgml
index 7c73cba11b7..96a09d6cd6e 100644
--- a/doc/src/sgml/release-19.sgml
+++ b/doc/src/sgml/release-19.sgml
@@ -116,7 +116,7 @@ or later versions of these applications, or use <literal>standard_conforming_str
</para>
<para>
-Client applications still support <literal>escape_string_warning = off</literal> for compatibility with old servers. The server variable <varname>escape_string_warning</varname> has been removed as unnecessary.
+Client applications still support operations with servers having <literal>standard_conforming_strings = off</literal>, for compatibility with old servers. The server variable <varname>escape_string_warning</varname> has been removed as unnecessary.
</para>
</listitem>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2026-06-18 16:20 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-19 15:08 [PATCH v6 3/3] Expose WAL usage counters in verbose (auto)vacuum output. Julien Rouhaud <julien.rouhaud@free.fr>
2020-03-19 15:08 [PATCH v7 4/4] Expose WAL usage counters in verbose (auto)vacuum output. Julien Rouhaud <julien.rouhaud@free.fr>
2020-03-19 15:08 [PATCH v8 4/4] Expose WAL usage counters in verbose (auto)vacuum output. Julien Rouhaud <julien.rouhaud@free.fr>
2026-05-11 22:21 [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility Baji Shaik <baji.pgdev@gmail.com>
2026-06-17 15:27 ` Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility Nathan Bossart <nathandbossart@gmail.com>
2026-06-17 15:36 ` Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility Tom Lane <tgl@sss.pgh.pa.us>
2026-06-17 17:29 ` Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility Tom Lane <tgl@sss.pgh.pa.us>
2026-06-17 19:55 ` Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility Baji Shaik <baji.pgdev@gmail.com>
2026-06-17 23:34 ` Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility Bruce Momjian <bruce@momjian.us>
2026-06-18 15:34 ` Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility Tom Lane <tgl@sss.pgh.pa.us>
2026-06-18 16:20 ` Re: [PATCH] Doc: document standard_conforming_strings dump/restore incompatibility Bruce Momjian <bruce@momjian.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox