On Sat, Jun 20, 2020 at 03:53:54PM +0900, Michael Paquier wrote: > On Sat, Jun 20, 2020 at 09:45:52AM +0530, Amit Kapila wrote: >> Isn't this information specific to checkpoints, so maybe better to >> display in view pg_stat_bgwriter? > > Not sure that's a good match. If we decide to expose that, a separate > function returning a LSN based on the segment number from > XLogGetLastRemovedSegno() sounds fine to me, like > pg_wal_last_recycled_lsn(). Perhaps somebody has a better name in > mind?
I was thinking on this one for the last couple of days, and came up with the name pg_wal_oldest_lsn(), as per the attached, traking the oldest WAL location still available. That's unfortunately too late for beta2, but let's continue the discussion. -- Michael
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 7644147cf5..7de4338910 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202006151
+#define CATALOG_VERSION_NO 202006221
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 61f2c2f5b4..1a07877086 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6090,6 +6090,10 @@
proname => 'pg_current_wal_flush_lsn', provolatile => 'v',
prorettype => 'pg_lsn', proargtypes => '',
prosrc => 'pg_current_wal_flush_lsn' },
+{ oid => '9054', descr => 'oldest wal location still available',
+ proname => 'pg_wal_oldest_lsn', provolatile => 'v',
+ prorettype => 'pg_lsn', proargtypes => '',
+ prosrc => 'pg_wal_oldest_lsn' },
{ oid => '2850',
descr => 'wal filename and byte offset, given a wal location',
proname => 'pg_walfile_name_offset', prorettype => 'record',
@@ -10063,9 +10067,9 @@
proname => 'pg_get_replication_slots', prorows => '10', proisstrict => 'f',
proretset => 't', provolatile => 's', prorettype => 'record',
proargtypes => '',
- proallargtypes => '{name,name,text,oid,bool,bool,int4,xid,xid,pg_lsn,pg_lsn,text,pg_lsn}',
- proargmodes => '{o,o,o,o,o,o,o,o,o,o,o,o,o}',
- proargnames => '{slot_name,plugin,slot_type,datoid,temporary,active,active_pid,xmin,catalog_xmin,restart_lsn,confirmed_flush_lsn,wal_status,min_safe_lsn}',
+ proallargtypes => '{name,name,text,oid,bool,bool,int4,xid,xid,pg_lsn,pg_lsn,text}',
+ proargmodes => '{o,o,o,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{slot_name,plugin,slot_type,datoid,temporary,active,active_pid,xmin,catalog_xmin,restart_lsn,confirmed_flush_lsn,wal_status}',
prosrc => 'pg_get_replication_slots' },
{ oid => '3786', descr => 'set up a logical replication slot',
proname => 'pg_create_logical_replication_slot', provolatile => 'v',
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 290658b22c..ccb3b5d5db 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -387,6 +387,31 @@ pg_current_wal_flush_lsn(PG_FUNCTION_ARGS)
PG_RETURN_LSN(current_recptr);
}
+/*
+ * Report the oldest WAL location still available after WAL segment removal
+ *
+ * This is useful to monitor how much WAL retention is happening with
+ * replication slots and concurrent checkpoints. NULL means that no WAL
+ * segments have been removed since startup yet.
+ */
+Datum
+pg_wal_oldest_lsn(PG_FUNCTION_ARGS)
+{
+ XLogRecPtr oldestptr;
+ XLogSegNo last_removed_seg;
+
+ last_removed_seg = XLogGetLastRemovedSegno();
+
+ /* Leave if no segments have been removed since startup */
+ if (last_removed_seg == 0)
+ PG_RETURN_NULL();
+
+ XLogSegNoOffsetToRecPtr(last_removed_seg + 1, 0,
+ wal_segment_size, oldestptr);
+
+ PG_RETURN_LSN(oldestptr);
+}
+
/*
* Report the last WAL receive location (same format as pg_start_backup etc)
*
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 5314e9348f..507b602a08 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -878,8 +878,7 @@ CREATE VIEW pg_replication_slots AS
L.catalog_xmin,
L.restart_lsn,
L.confirmed_flush_lsn,
- L.wal_status,
- L.min_safe_lsn
+ L.wal_status
FROM pg_get_replication_slots() AS L
LEFT JOIN pg_database D ON (L.datoid = D.oid);
diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c
index 06e4955de7..590f7054d6 100644
--- a/src/backend/replication/slotfuncs.c
+++ b/src/backend/replication/slotfuncs.c
@@ -236,7 +236,7 @@ pg_drop_replication_slot(PG_FUNCTION_ARGS)
Datum
pg_get_replication_slots(PG_FUNCTION_ARGS)
{
-#define PG_GET_REPLICATION_SLOTS_COLS 13
+#define PG_GET_REPLICATION_SLOTS_COLS 12
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
TupleDesc tupdesc;
Tuplestorestate *tupstore;
@@ -282,7 +282,6 @@ pg_get_replication_slots(PG_FUNCTION_ARGS)
Datum values[PG_GET_REPLICATION_SLOTS_COLS];
bool nulls[PG_GET_REPLICATION_SLOTS_COLS];
WALAvailability walstate;
- XLogSegNo last_removed_seg;
int i;
if (!slot->in_use)
@@ -366,19 +365,6 @@ pg_get_replication_slots(PG_FUNCTION_ARGS)
elog(ERROR, "invalid walstate: %d", (int) walstate);
}
- if (max_slot_wal_keep_size_mb >= 0 &&
- (walstate == WALAVAIL_NORMAL || walstate == WALAVAIL_RESERVED) &&
- ((last_removed_seg = XLogGetLastRemovedSegno()) != 0))
- {
- XLogRecPtr min_safe_lsn;
-
- XLogSegNoOffsetToRecPtr(last_removed_seg + 1, 0,
- wal_segment_size, min_safe_lsn);
- values[i++] = Int64GetDatum(min_safe_lsn);
- }
- else
- nulls[i++] = true;
-
Assert(i == PG_GET_REPLICATION_SLOTS_COLS);
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
diff --git a/src/test/recovery/t/019_replslot_limit.pl b/src/test/recovery/t/019_replslot_limit.pl
index cba7df920c..721e94a1a3 100644
--- a/src/test/recovery/t/019_replslot_limit.pl
+++ b/src/test/recovery/t/019_replslot_limit.pl
@@ -28,9 +28,9 @@ $node_master->safe_psql('postgres',
# The slot state and remain should be null before the first connection
my $result = $node_master->safe_psql('postgres',
- "SELECT restart_lsn IS NULL, wal_status is NULL, min_safe_lsn is NULL FROM pg_replication_slots WHERE slot_name = 'rep1'"
+ "SELECT restart_lsn IS NULL, wal_status is NULL FROM pg_replication_slots WHERE slot_name = 'rep1'"
);
-is($result, "t|t|t", 'check the state of non-reserved slot is "unknown"');
+is($result, "t|t", 'check the state of non-reserved slot is "unknown"');
# Take backup
@@ -54,9 +54,9 @@ $node_standby->stop;
# Preparation done, the slot is the state "normal" now
$result = $node_master->safe_psql('postgres',
- "SELECT wal_status, min_safe_lsn is NULL FROM pg_replication_slots WHERE slot_name = 'rep1'"
+ "SELECT wal_status FROM pg_replication_slots WHERE slot_name = 'rep1'"
);
-is($result, "normal|t", 'check the catching-up state');
+is($result, "normal", 'check the catching-up state');
# Advance WAL by five segments (= 5MB) on master
advance_wal($node_master, 1);
@@ -64,18 +64,18 @@ $node_master->safe_psql('postgres', "CHECKPOINT;");
# The slot is always "safe" when fitting max_wal_size
$result = $node_master->safe_psql('postgres',
- "SELECT wal_status, min_safe_lsn is NULL FROM pg_replication_slots WHERE slot_name = 'rep1'"
+ "SELECT wal_status FROM pg_replication_slots WHERE slot_name = 'rep1'"
);
-is($result, "normal|t", 'check that it is safe if WAL fits in max_wal_size');
+is($result, "normal", 'check that it is safe if WAL fits in max_wal_size');
advance_wal($node_master, 4);
$node_master->safe_psql('postgres', "CHECKPOINT;");
# The slot is always "safe" when max_slot_wal_keep_size is not set
$result = $node_master->safe_psql('postgres',
- "SELECT wal_status, min_safe_lsn is NULL FROM pg_replication_slots WHERE slot_name = 'rep1'"
+ "SELECT wal_status FROM pg_replication_slots WHERE slot_name = 'rep1'"
);
-is($result, "normal|t", 'check that slot is working');
+is($result, "normal", 'check that slot is working');
# The standby can reconnect to master
$node_standby->start;
@@ -93,23 +93,19 @@ max_slot_wal_keep_size = ${max_slot_wal_keep_size_mb}MB
));
$node_master->reload;
-# The slot is in safe state. The distance from the min_safe_lsn should
-# be as almost (max_slot_wal_keep_size - 1) times large as the segment
-# size
-
+# The slot should be in a normal state.
$result = $node_master->safe_psql('postgres',
"SELECT wal_status FROM pg_replication_slots WHERE slot_name = 'rep1'");
is($result, "normal", 'check that max_slot_wal_keep_size is working');
# Advance WAL again then checkpoint, reducing remain by 2 MB.
+# The slot should still be in a normal state after checkpoint.
advance_wal($node_master, 2);
$node_master->safe_psql('postgres', "CHECKPOINT;");
-
-# The slot is still working
$result = $node_master->safe_psql('postgres',
"SELECT wal_status FROM pg_replication_slots WHERE slot_name = 'rep1'");
is($result, "normal",
- 'check that min_safe_lsn gets close to the current LSN');
+ 'check that the slot state changes to "normal"');
# The standby can reconnect to master
$node_standby->start;
@@ -153,9 +149,9 @@ advance_wal($node_master, 1);
# Slot gets into 'lost' state
$result = $node_master->safe_psql('postgres',
- "SELECT wal_status, min_safe_lsn is NULL FROM pg_replication_slots WHERE slot_name = 'rep1'"
+ "SELECT wal_status FROM pg_replication_slots WHERE slot_name = 'rep1'"
);
-is($result, "lost|t", 'check that the slot state changes to "lost"');
+is($result, "lost", 'check that the slot state changes to "lost"');
# The standby still can connect to master before a checkpoint
$node_standby->start;
@@ -184,9 +180,9 @@ ok( find_in_log(
# This slot should be broken
$result = $node_master->safe_psql('postgres',
- "SELECT slot_name, active, restart_lsn IS NULL, wal_status, min_safe_lsn FROM pg_replication_slots WHERE slot_name = 'rep1'"
+ "SELECT slot_name, active, restart_lsn IS NULL, wal_status FROM pg_replication_slots WHERE slot_name = 'rep1'"
);
-is($result, "rep1|f|t||", 'check that the slot became inactive');
+is($result, "rep1|f|t|", 'check that the slot became inactive');
# The standby no longer can connect to the master
$logstart = get_log_size($node_standby);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index b813e32215..48245774e1 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1463,9 +1463,8 @@ pg_replication_slots| SELECT l.slot_name,
l.catalog_xmin,
l.restart_lsn,
l.confirmed_flush_lsn,
- l.wal_status,
- l.min_safe_lsn
- FROM (pg_get_replication_slots() l(slot_name, plugin, slot_type, datoid, temporary, active, active_pid, xmin, catalog_xmin, restart_lsn, confirmed_flush_lsn, wal_status, min_safe_lsn)
+ l.wal_status
+ FROM (pg_get_replication_slots() l(slot_name, plugin, slot_type, datoid, temporary, active, active_pid, xmin, catalog_xmin, restart_lsn, confirmed_flush_lsn, wal_status)
LEFT JOIN pg_database d ON ((l.datoid = d.oid)));
pg_roles| SELECT pg_authid.rolname,
pg_authid.rolsuper,
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 5a66115df1..5122bfcd8b 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -11260,15 +11260,6 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
field is null.
</para></entry>
</row>
-
- <row>
- <entry role="catalog_table_entry"><para role="column_definition">
- <structfield>min_safe_lsn</structfield> <type>pg_lsn</type>
- </para>
- <para>
- The minimum LSN currently available for walsenders.
- </para></entry>
- </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index b7c450ea29..afc1c1410c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24067,6 +24067,22 @@ SELECT collation for ('foo' COLLATE "de_DE");
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_oldest_wal_lsn</primary>
+ </indexterm>
+ <function>pg_oldest_wal_lsn</function> ()
+ <returnvalue>pg_lsn</returnvalue>
+ </para>
+ <para>
+ Returns the oldest WAL location still available on the system,
+ calculated based on the latest WAL segment recycled, or
+ <literal>NULL</literal> if no WAL segments have been removed since
+ startup.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
signature.asc
Description: PGP signature
