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.
12+ messages / 6 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; 12+ 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] 12+ 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; 12+ 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] 12+ 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; 12+ 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] 12+ messages in thread
* [PATCH v3 2/2] Add the pg_stat_lock view
@ 2025-07-31 09:35 Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Bertrand Drouvot @ 2025-07-31 09:35 UTC (permalink / raw)
This new view reports lock statistics.
This commit also adds documentation and a few tests.
XXX: Bump catversion
---
doc/src/sgml/monitoring.sgml | 151 ++++++++++++++++++
src/backend/catalog/system_views.sql | 12 ++
src/backend/utils/activity/pgstat_lock.c | 8 +
src/backend/utils/adt/pgstatfuncs.c | 39 +++++
src/include/catalog/pg_proc.dat | 9 ++
src/include/pgstat.h | 1 +
src/test/isolation/expected/deadlock-hard.out | 20 ++-
src/test/isolation/specs/deadlock-hard.spec | 5 +-
src/test/regress/expected/advisory_lock.out | 18 +++
src/test/regress/expected/rules.out | 9 ++
src/test/regress/sql/advisory_lock.sql | 4 +
11 files changed, 274 insertions(+), 2 deletions(-)
51.5% doc/src/sgml/
3.3% src/backend/catalog/
14.3% src/backend/utils/adt/
5.7% src/include/catalog/
8.4% src/test/isolation/expected/
5.9% src/test/isolation/specs/
6.7% src/test/regress/expected/
3.8% src/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index b77d189a500..927b62cf649 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -493,6 +493,15 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_lock</structname><indexterm><primary>pg_stat_lock</primary></indexterm></entry>
+ <entry>
+ One row for each lock type, containing cluster-wide locks statistics.
+ See <link linkend="monitoring-pg-stat-lock-view">
+ <structname>pg_stat_lock</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_replication_slots</structname><indexterm><primary>pg_stat_replication_slots</primary></indexterm></entry>
<entry>One row per replication slot, showing statistics about the
@@ -3124,6 +3133,142 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</sect2>
+
+ <sect2 id="monitoring-pg-stat-lock-view">
+ <title><structname>pg_stat_lock</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_lock</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_lock</structname> view will contain one row for each
+ lock type, showing cluster-wide locks statistics.
+ </para>
+
+ <table id="pg-stat-lock-view" xreflabel="pg_stat_lock">
+ <title><structname>pg_stat_lock</structname> View</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para>
+ </entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>locktype</structfield> <type>text</type>
+ </para>
+ <para>
+ Type of the lockable object:
+ <literal>relation</literal>,
+ <literal>extend</literal>,
+ <literal>frozenid</literal>,
+ <literal>page</literal>,
+ <literal>tuple</literal>,
+ <literal>transactionid</literal>,
+ <literal>virtualxid</literal>,
+ <literal>spectoken</literal>,
+ <literal>object</literal>,
+ <literal>userlock</literal>,
+ <literal>advisory</literal>, or
+ <literal>applytransaction</literal>.
+ (See also <xref linkend="wait-event-lock-table"/>.)
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>requests</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of requests for this lock type.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>waits</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times requests for this lock type had to wait.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>timeouts</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times requests for this lock type had to wait longer
+ than <varname>lock_timeout</varname>.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>deadlock_timeouts</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times requests for this lock type had to wait longer
+ than <varname>deadlock_timeout</varname>.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>deadlocks</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times a deadlock occurred on this lock type.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>fastpath</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times this lock type was taken via fast path.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry">
+ <para role="column_definition">
+ <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Time at which these statistics were last reset.
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect2>
+
<sect2 id="monitoring-pg-stat-bgwriter-view">
<title><structname>pg_stat_bgwriter</structname></title>
@@ -5195,6 +5340,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<structname>pg_stat_io</structname> view.
</para>
</listitem>
+ <listitem>
+ <para>
+ <literal>lock</literal>: Reset all the counters shown in the
+ <structname>pg_stat_lock</structname> view.
+ </para>
+ </listitem>
<listitem>
<para>
<literal>recovery_prefetch</literal>: Reset all the counters shown in
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 1ea8f1faa9e..e30c25ced31 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -985,6 +985,18 @@ CREATE VIEW pg_stat_slru AS
s.stats_reset
FROM pg_stat_get_slru() s;
+CREATE VIEW pg_stat_lock AS
+ SELECT
+ l.locktype,
+ l.requests,
+ l.waits,
+ l.timeouts,
+ l.deadlock_timeouts,
+ l.deadlocks,
+ l.fastpath,
+ l.stats_reset
+ FROM pg_stat_get_lock() l;
+
CREATE VIEW pg_stat_wal_receiver AS
SELECT
s.pid,
diff --git a/src/backend/utils/activity/pgstat_lock.c b/src/backend/utils/activity/pgstat_lock.c
index fb4ed5c7362..73bdeb5ff66 100644
--- a/src/backend/utils/activity/pgstat_lock.c
+++ b/src/backend/utils/activity/pgstat_lock.c
@@ -21,6 +21,14 @@
static PgStat_PendingLock PendingLockStats;
static bool have_lockstats = false;
+PgStat_Lock *
+pgstat_fetch_stat_lock(void)
+{
+ pgstat_snapshot_fixed(PGSTAT_KIND_LOCK);
+
+ return &pgStatLocal.snapshot.lock;
+}
+
/*
* Simpler wrapper of pgstat_lock_flush_cb()
*/
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 73ca0bb0b7f..4ca4a3f3b9b 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1725,6 +1725,42 @@ pg_stat_get_wal(PG_FUNCTION_ARGS)
wal_stats->stat_reset_timestamp));
}
+Datum
+pg_stat_get_lock(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_LOCK_COLS 8
+ ReturnSetInfo *rsinfo;
+ PgStat_Lock *lock_stats;
+
+ InitMaterializedSRF(fcinfo, 0);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ lock_stats = pgstat_fetch_stat_lock();
+
+ for (int lcktype = 0; lcktype <= LOCKTAG_LAST_TYPE; lcktype++)
+ {
+ const char *locktypename;
+ Datum values[PG_STAT_LOCK_COLS] = {0};
+ bool nulls[PG_STAT_LOCK_COLS] = {0};
+ PgStat_LockEntry *lck_stats = &lock_stats->stats[lcktype];
+
+ locktypename = LockTagTypeNames[lcktype];
+
+ values[0] = CStringGetTextDatum(locktypename);
+ values[1] = Int64GetDatum(lck_stats->requests);
+ values[2] = Int64GetDatum(lck_stats->waits);
+ values[3] = Int64GetDatum(lck_stats->timeouts);
+ values[4] = Int64GetDatum(lck_stats->deadlock_timeouts);
+ values[5] = Int64GetDatum(lck_stats->deadlocks);
+ values[6] = Int64GetDatum(lck_stats->fastpath);
+ values[7] = TimestampTzGetDatum(lock_stats->stat_reset_timestamp);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+ }
+
+ return (Datum) 0;
+}
+
/*
* Returns statistics of SLRU caches.
*/
@@ -1909,6 +1945,7 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER);
pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
pgstat_reset_of_kind(PGSTAT_KIND_IO);
+ pgstat_reset_of_kind(PGSTAT_KIND_LOCK);
XLogPrefetchResetStats();
pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
pgstat_reset_of_kind(PGSTAT_KIND_WAL);
@@ -1926,6 +1963,8 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
else if (strcmp(target, "io") == 0)
pgstat_reset_of_kind(PGSTAT_KIND_IO);
+ else if (strcmp(target, "lock") == 0)
+ pgstat_reset_of_kind(PGSTAT_KIND_LOCK);
else if (strcmp(target, "recovery_prefetch") == 0)
XLogPrefetchResetStats();
else if (strcmp(target, "slru") == 0)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 83f6501df38..a0c47f6888c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6026,6 +6026,15 @@
proargnames => '{backend_type,object,context,reads,read_bytes,read_time,writes,write_bytes,write_time,writebacks,writeback_time,extends,extend_bytes,extend_time,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}',
prosrc => 'pg_stat_get_io' },
+{ oid => '9375', descr => 'statistics: per lock type statistics',
+ proname => 'pg_stat_get_lock', prorows => '10', proretset => 't',
+ provolatile => 'v', proparallel => 'r', prorettype => 'record',
+ proargtypes => '',
+ proallargtypes => '{text,int8,int8,int8,int8,int8,int8,timestamptz}',
+ proargmodes => '{o,o,o,o,o,o,o,o}',
+ proargnames => '{locktype,requests,waits,timeouts,deadlock_timeouts,deadlocks,fastpath,stats_reset}',
+ prosrc => 'pg_stat_get_lock' },
+
{ oid => '6386', descr => 'statistics: backend IO statistics',
proname => 'pg_stat_get_backend_io', prorows => '5', proretset => 't',
provolatile => 'v', proparallel => 'r', prorettype => 'record',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index cbe226b9a07..c16c3335755 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -647,6 +647,7 @@ extern void pgstat_count_lock_timeouts(uint8 locktag_type);
extern void pgstat_count_lock_deadlock_timeouts(uint8 locktag_type);
extern void pgstat_count_lock_deadlocks(uint8 locktag_type);
extern void pgstat_count_lock_fastpath(uint8 locktag_type);
+extern PgStat_Lock *pgstat_fetch_stat_lock(void);
/*
* Functions in pgstat_database.c
diff --git a/src/test/isolation/expected/deadlock-hard.out b/src/test/isolation/expected/deadlock-hard.out
index 460653f2b86..ff448da5299 100644
--- a/src/test/isolation/expected/deadlock-hard.out
+++ b/src/test/isolation/expected/deadlock-hard.out
@@ -1,6 +1,12 @@
Parsed test spec with 8 sessions
-starting permutation: s1a1 s2a2 s3a3 s4a4 s5a5 s6a6 s7a7 s8a8 s1a2 s2a3 s3a4 s4a5 s5a6 s6a7 s7a8 s8a1 s8c s7c s6c s5c s4c s3c s2c s1c
+starting permutation: s1rl s1a1 s2a2 s3a3 s4a4 s5a5 s6a6 s7a7 s8a8 s1a2 s2a3 s3a4 s4a5 s5a6 s6a7 s7a8 s8a1 s8c s8f s7c s6c s5c s4c s3c s2c s1c s1sl
+step s1rl: SELECT pg_stat_reset_shared('lock');
+pg_stat_reset_shared
+--------------------
+
+(1 row)
+
step s1a1: LOCK TABLE a1;
step s2a2: LOCK TABLE a2;
step s3a3: LOCK TABLE a3;
@@ -21,6 +27,12 @@ step s8a1: <... completed>
ERROR: deadlock detected
step s7a8: <... completed>
step s8c: COMMIT;
+step s8f: SELECT pg_stat_force_next_flush();
+pg_stat_force_next_flush
+------------------------
+
+(1 row)
+
step s7c: COMMIT;
step s6a7: <... completed>
step s6c: COMMIT;
@@ -34,3 +46,9 @@ step s2a3: <... completed>
step s2c: COMMIT;
step s1a2: <... completed>
step s1c: COMMIT;
+step s1sl: SELECT deadlocks > 0, deadlock_timeouts > 0 FROM pg_stat_lock WHERE locktype = 'relation';
+?column?|?column?
+--------+--------
+t |t
+(1 row)
+
diff --git a/src/test/isolation/specs/deadlock-hard.spec b/src/test/isolation/specs/deadlock-hard.spec
index 60bedca237a..6e8330662b4 100644
--- a/src/test/isolation/specs/deadlock-hard.spec
+++ b/src/test/isolation/specs/deadlock-hard.spec
@@ -25,6 +25,8 @@ setup { BEGIN; SET deadlock_timeout = '100s'; }
step s1a1 { LOCK TABLE a1; }
step s1a2 { LOCK TABLE a2; }
step s1c { COMMIT; }
+step s1sl { SELECT deadlocks > 0, deadlock_timeouts > 0 FROM pg_stat_lock WHERE locktype = 'relation'; }
+step s1rl { SELECT pg_stat_reset_shared('lock'); }
session s2
setup { BEGIN; SET deadlock_timeout = '100s'; }
@@ -67,6 +69,7 @@ setup { BEGIN; SET deadlock_timeout = '10ms'; }
step s8a8 { LOCK TABLE a8; }
step s8a1 { LOCK TABLE a1; }
step s8c { COMMIT; }
+step s8f { SELECT pg_stat_force_next_flush(); }
# Note: when s8a1 detects the deadlock and fails, s7a8 is released, making
# it timing-dependent which query completion is received first by the tester.
@@ -76,4 +79,4 @@ step s8c { COMMIT; }
# dummy blocking mark to s8a1 to ensure it will be reported as "waiting"
# regardless of that.
-permutation s1a1 s2a2 s3a3 s4a4 s5a5 s6a6 s7a7 s8a8 s1a2 s2a3 s3a4 s4a5 s5a6 s6a7 s7a8(s8a1) s8a1(*) s8c s7c s6c s5c s4c s3c s2c s1c
+permutation s1rl s1a1 s2a2 s3a3 s4a4 s5a5 s6a6 s7a7 s8a8 s1a2 s2a3 s3a4 s4a5 s5a6 s6a7 s7a8(s8a1) s8a1(*) s8c s8f s7c s6c s5c s4c s3c s2c s1c s1sl
diff --git a/src/test/regress/expected/advisory_lock.out b/src/test/regress/expected/advisory_lock.out
index 02e07765ac2..fdaa1756ba4 100644
--- a/src/test/regress/expected/advisory_lock.out
+++ b/src/test/regress/expected/advisory_lock.out
@@ -2,6 +2,12 @@
-- ADVISORY LOCKS
--
SELECT oid AS datoid FROM pg_database WHERE datname = current_database() \gset
+SELECT pg_stat_reset_shared('lock');
+ pg_stat_reset_shared
+----------------------
+
+(1 row)
+
BEGIN;
SELECT
pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),
@@ -48,6 +54,12 @@ WARNING: you don't own a lock of type ShareLock
f | f | f | f
(1 row)
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
-- automatically release xact locks at commit
COMMIT;
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
@@ -56,6 +68,12 @@ SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid
0
(1 row)
+SELECT requests FROM pg_stat_lock WHERE locktype = 'advisory';
+ requests
+----------
+ 4
+(1 row)
+
BEGIN;
-- holding both session and xact locks on the same objects, xact first
SELECT
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index f9bc213e5a1..8ee2e4cbea1 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1951,6 +1951,15 @@ pg_stat_io| SELECT backend_type,
fsync_time,
stats_reset
FROM pg_stat_get_io() b(backend_type, object, context, reads, read_bytes, read_time, writes, write_bytes, write_time, writebacks, writeback_time, extends, extend_bytes, extend_time, hits, evictions, reuses, fsyncs, fsync_time, stats_reset);
+pg_stat_lock| SELECT locktype,
+ requests,
+ waits,
+ timeouts,
+ deadlock_timeouts,
+ deadlocks,
+ fastpath,
+ stats_reset
+ FROM pg_stat_get_lock() l(locktype, requests, waits, timeouts, deadlock_timeouts, deadlocks, fastpath, stats_reset);
pg_stat_progress_analyze| SELECT s.pid,
s.datid,
d.datname,
diff --git a/src/test/regress/sql/advisory_lock.sql b/src/test/regress/sql/advisory_lock.sql
index 8513ab8e98f..f1bff60fd37 100644
--- a/src/test/regress/sql/advisory_lock.sql
+++ b/src/test/regress/sql/advisory_lock.sql
@@ -4,6 +4,8 @@
SELECT oid AS datoid FROM pg_database WHERE datname = current_database() \gset
+SELECT pg_stat_reset_shared('lock');
+
BEGIN;
SELECT
@@ -26,12 +28,14 @@ SELECT
pg_advisory_unlock(1), pg_advisory_unlock_shared(2),
pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2);
+SELECT pg_stat_force_next_flush();
-- automatically release xact locks at commit
COMMIT;
SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;
+SELECT requests FROM pg_stat_lock WHERE locktype = 'advisory';
BEGIN;
--
2.34.1
--PI+wla+m5/GDNJVD--
^ permalink raw reply [nested|flat] 12+ 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; 12+ 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] 12+ 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; 12+ 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] 12+ 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; 12+ 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] 12+ 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; 12+ 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] 12+ 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; 12+ 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] 12+ 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; 12+ 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] 12+ 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; 12+ 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] 12+ 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; 12+ 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 (789B, ../../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] 12+ messages in thread
end of thread, other threads:[~2026-06-18 16:20 UTC | newest]
Thread overview: 12+ 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>
2025-07-31 09:35 [PATCH v3 2/2] Add the pg_stat_lock view Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
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