v4-0001-Add-last-commit-s-LSN-to-pg_stat_database.patch

From 3d984d3881f3757b280c043043d365092c41b6a9 Mon Sep 17 00:00:00 2001
From: jcoleman <jtc331@gmail.com>
Date: Fri, 9 Jun 2023 20:12:31 -0500
Subject: [PATCH v4] Add last commit's LSN to pg_stat_database

---
 doc/src/sgml/monitoring.sgml                 |  9 ++++
 src/backend/catalog/system_views.sql         |  1 +
 src/backend/utils/activity/pgstat_database.c | 12 +++++
 src/backend/utils/adt/pgstatfuncs.c          |  3 ++
 src/include/catalog/pg_proc.dat              |  4 ++
 src/include/pgstat.h                         |  2 +
 src/test/regress/expected/rules.out          |  1 +
 src/test/regress/expected/stats.out          | 49 ++++++++++++++++++++
 src/test/regress/sql/stats.sql               | 24 ++++++++++
 9 files changed, 105 insertions(+)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 053da8d6e4..25190389a3 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3249,6 +3249,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
       </para></entry>
      </row>
 
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>last_commit_lsn</structfield> <type>pg_lsn</type>
+      </para>
+      <para>
+       Write-ahead log location of the last commit in this database
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>xact_commit</structfield> <type>bigint</type>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 53047cab5f..81d3f415b8 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1052,6 +1052,7 @@ CREATE VIEW pg_stat_database AS
                     WHEN (D.oid = (0)::oid) THEN 0
                     ELSE pg_stat_get_db_numbackends(D.oid)
                 END AS numbackends,
+            pg_stat_get_db_last_commit_lsn(D.oid) AS last_commit_lsn,
             pg_stat_get_db_xact_commit(D.oid) AS xact_commit,
             pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback,
             pg_stat_get_db_blocks_fetched(D.oid) -
diff --git a/src/backend/utils/activity/pgstat_database.c b/src/backend/utils/activity/pgstat_database.c
index 29bc090974..a01754ea85 100644
--- a/src/backend/utils/activity/pgstat_database.c
+++ b/src/backend/utils/activity/pgstat_database.c
@@ -20,6 +20,7 @@
 #include "storage/procsignal.h"
 #include "utils/pgstat_internal.h"
 #include "utils/timestamp.h"
+#include "access/xlog.h"
 
 
 static bool pgstat_should_report_connstat(void);
@@ -280,6 +281,13 @@ pgstat_update_dbstats(TimestampTz ts)
 
 	dbentry = pgstat_prep_database_pending(MyDatabaseId);
 
+	/*
+	 * Track the LSN of the most recent commit. Since we're local to the
+	 * current backend we don't have to worry if we're advancing or not.
+	 */
+	if (!XLogRecPtrIsInvalid(XactLastCommitEnd))
+		dbentry->last_commit_lsn = XactLastCommitEnd;
+
 	/*
 	 * Accumulate xact commit/rollback and I/O timings to stats entry of the
 	 * current database.
@@ -427,6 +435,10 @@ pgstat_database_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
 	PGSTAT_ACCUM_DBCOUNT(sessions_killed);
 #undef PGSTAT_ACCUM_DBCOUNT
 
+	/* Only update last_commit_lsn if our backend has the newest commit. */
+	if (pendingent->last_commit_lsn > sharedent->stats.last_commit_lsn)
+		sharedent->stats.last_commit_lsn = pendingent->last_commit_lsn;
+
 	pgstat_unlock_entry(entry_ref);
 
 	memset(pendingent, 0, sizeof(*pendingent));
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 3876339ee1..44194567f7 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1060,6 +1060,9 @@ PG_STAT_GET_DBENTRY_INT64(tuples_returned)
 /* pg_stat_get_db_tuples_updated */
 PG_STAT_GET_DBENTRY_INT64(tuples_updated)
 
+/* pg_stat_get_db_last_commit_lsn */
+PG_STAT_GET_DBENTRY_INT64(last_commit_lsn)
+
 /* pg_stat_get_db_xact_commit */
 PG_STAT_GET_DBENTRY_INT64(xact_commit)
 
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4..9495a449b8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5581,6 +5581,10 @@
   proname => 'pg_stat_get_db_numbackends', provolatile => 's',
   proparallel => 'r', prorettype => 'int4', proargtypes => 'oid',
   prosrc => 'pg_stat_get_db_numbackends' },
+{ oid => '8000', descr => 'statistics: wal location of last committed transaction',
+  proname => 'pg_stat_get_db_last_commit_lsn', provolatile => 's',
+  proparallel => 'r', prorettype => 'pg_lsn', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_last_commit_lsn' },
 { oid => '1942', descr => 'statistics: transactions committed',
   proname => 'pg_stat_get_db_xact_commit', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 2136239710..19ab016904 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -18,6 +18,7 @@
 #include "utils/backend_status.h"	/* for backward compatibility */
 #include "utils/relcache.h"
 #include "utils/wait_event.h"	/* for backward compatibility */
+#include "access/xlogdefs.h"
 
 
 /* ----------
@@ -322,6 +323,7 @@ typedef struct PgStat_IO
 
 typedef struct PgStat_StatDBEntry
 {
+	XLogRecPtr	last_commit_lsn;
 	PgStat_Counter xact_commit;
 	PgStat_Counter xact_rollback;
 	PgStat_Counter blocks_fetched;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index ef658ad740..2f650b1e23 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1839,6 +1839,7 @@ pg_stat_database| SELECT oid AS datid,
             WHEN (oid = (0)::oid) THEN 0
             ELSE pg_stat_get_db_numbackends(oid)
         END AS numbackends,
+    pg_stat_get_db_last_commit_lsn(oid) AS last_commit_lsn,
     pg_stat_get_db_xact_commit(oid) AS xact_commit,
     pg_stat_get_db_xact_rollback(oid) AS xact_rollback,
     (pg_stat_get_db_blocks_fetched(oid) - pg_stat_get_db_blocks_hit(oid)) AS blks_read,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 6e08898b18..359f2f18da 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -828,6 +828,55 @@ SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = (SELEC
  t
 (1 row)
 
+CREATE TABLE test_commit_increments(i int);
+-- Test that last_commit_lsn is incremented when a two-phase transaction commits
+SELECT last_commit_lsn AS db_stat_last_commit_lsn_2pc FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+BEGIN;
+INSERT INTO test_commit_increments(i) VALUES (1);
+PREPARE TRANSACTION 't1';
+COMMIT PREPARED 't1';
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+SELECT last_commit_lsn > :'db_stat_last_commit_lsn_2pc'::pg_lsn FROM pg_stat_database WHERE datname = (SELECT current_database());
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT :'db_stat_last_commit_lsn_2pc'::pg_lsn < pg_current_wal_insert_lsn();
+ ?column? 
+----------
+ t
+(1 row)
+
+-- Test that last_commit_lsn is incremented when a transaction commits
+SELECT last_commit_lsn AS db_stat_last_commit_lsn FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+BEGIN;
+INSERT INTO test_commit_increments(i) VALUES (1);
+COMMIT;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+SELECT last_commit_lsn > :'db_stat_last_commit_lsn'::pg_lsn FROM pg_stat_database WHERE datname = (SELECT current_database());
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT :'db_stat_last_commit_lsn'::pg_lsn < pg_current_wal_insert_lsn();
+ ?column? 
+----------
+ t
+(1 row)
+
+DROP TABLE test_commit_increments;
 -- Test pg_stat_checkpointer checkpointer-related stats, together with pg_stat_wal
 SELECT num_requested AS rqst_ckpts_before FROM pg_stat_checkpointer \gset
 -- Test pg_stat_wal (and make a temp table so our temp schema exists)
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index d8ac0d06f4..5c4509baf6 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -417,6 +417,30 @@ SELECT sessions AS db_stat_sessions FROM pg_stat_database WHERE datname = (SELEC
 SELECT pg_stat_force_next_flush();
 SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database());
 
+CREATE TABLE test_commit_increments(i int);
+
+-- Test that last_commit_lsn is incremented when a two-phase transaction commits
+SELECT last_commit_lsn AS db_stat_last_commit_lsn_2pc FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+BEGIN;
+INSERT INTO test_commit_increments(i) VALUES (1);
+PREPARE TRANSACTION 't1';
+COMMIT PREPARED 't1';
+SELECT pg_stat_force_next_flush();
+SELECT last_commit_lsn > :'db_stat_last_commit_lsn_2pc'::pg_lsn FROM pg_stat_database WHERE datname = (SELECT current_database());
+SELECT :'db_stat_last_commit_lsn_2pc'::pg_lsn < pg_current_wal_insert_lsn();
+
+
+-- Test that last_commit_lsn is incremented when a transaction commits
+SELECT last_commit_lsn AS db_stat_last_commit_lsn FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+BEGIN;
+INSERT INTO test_commit_increments(i) VALUES (1);
+COMMIT;
+SELECT pg_stat_force_next_flush();
+SELECT last_commit_lsn > :'db_stat_last_commit_lsn'::pg_lsn FROM pg_stat_database WHERE datname = (SELECT current_database());
+SELECT :'db_stat_last_commit_lsn'::pg_lsn < pg_current_wal_insert_lsn();
+
+DROP TABLE test_commit_increments;
+
 -- Test pg_stat_checkpointer checkpointer-related stats, together with pg_stat_wal
 SELECT num_requested AS rqst_ckpts_before FROM pg_stat_checkpointer \gset
 
-- 
2.39.3 (Apple Git-146)