On Tue, Jan 18, 2022 at 4:32 PM Andres Freund <and...@anarazel.de> wrote:
>
> I wonder if a very different approach could make sense here. Presumably this
> wouldn't need to be queried at a very high frequency, right? If so, what about
> storing the latest commit LSN for each backend in PGPROC? That could be
> maintained without a lock/atomics, and should be just about free.
> pg_last_committed_xact() then would have to iterate over all PGPROCs to
> complete the LSN, but that's not too bad for an operation like that. We'd also
> need to maintain a value for all disconnected backends, but that's also not a
> hot
> path.
Is something roughly like the attached what you'd envisioned? I
wouldn't expect the final implementation to be in commit_ts.c, but I
left it there for expediency's sake in demonstrating the idea since
pg_last_committed_xact() currently finds its home there.
I think we need a shared ProcArrayLock to read the array, correct? We
also need to do the global updating under lock, but given it's when a
proc is removed, that shouldn't be a performance issue if I'm
following what you are saying.
Thanks,
James Coleman
From 7e8a4810f5b4cd16ca2a6f76585513711dbf529a Mon Sep 17 00:00:00 2001
From: jcoleman <jtc...@gmail.com>
Date: Fri, 14 Jan 2022 22:26:10 +0000
Subject: [PATCH v2] Expose LSN of last commit via pg_last_committed_xact
---
src/backend/access/transam/commit_ts.c | 34 ++++++++++++++++++++------
src/backend/access/transam/twophase.c | 2 +-
src/backend/access/transam/xact.c | 10 +++++---
src/backend/storage/ipc/procarray.c | 4 +++
src/include/access/commit_ts.h | 5 ++--
src/include/access/transam.h | 2 ++
src/include/catalog/pg_proc.dat | 6 ++---
src/include/storage/proc.h | 5 ++++
8 files changed, 51 insertions(+), 17 deletions(-)
diff --git a/src/backend/access/transam/commit_ts.c b/src/backend/access/transam/commit_ts.c
index 659109f8d4..50a7be82bc 100644
--- a/src/backend/access/transam/commit_ts.c
+++ b/src/backend/access/transam/commit_ts.c
@@ -34,9 +34,11 @@
#include "miscadmin.h"
#include "pg_trace.h"
#include "storage/shmem.h"
+#include "storage/proc.h"
#include "utils/builtins.h"
#include "utils/snapmgr.h"
#include "utils/timestamp.h"
+#include "utils/pg_lsn.h"
/*
* Defines for CommitTs page sizes. A page is the same BLCKSZ as is used
@@ -93,6 +95,7 @@ static SlruCtlData CommitTsCtlData;
typedef struct CommitTimestampShared
{
TransactionId xidLastCommit;
+ XLogRecPtr lsnLastCommit;
CommitTimestampEntry dataLastCommit;
bool commitTsActive;
} CommitTimestampShared;
@@ -135,7 +138,7 @@ static void WriteTruncateXlogRec(int pageno, TransactionId oldestXid);
void
TransactionTreeSetCommitTsData(TransactionId xid, int nsubxids,
TransactionId *subxids, TimestampTz timestamp,
- RepOriginId nodeid)
+ XLogRecPtr commitLsn, RepOriginId nodeid)
{
int i;
TransactionId headxid;
@@ -414,10 +417,22 @@ pg_last_committed_xact(PG_FUNCTION_ARGS)
TransactionId xid;
RepOriginId nodeid;
TimestampTz ts;
- Datum values[3];
- bool nulls[3];
+ XLogRecPtr lsn;
+ Datum values[4];
+ bool nulls[4];
TupleDesc tupdesc;
HeapTuple htup;
+ int index;
+
+ LWLockAcquire(ProcArrayLock, LW_SHARED);
+ lsn = ShmemVariableCache->finishedProcsLastCommitLSN;
+ for (index = 0; index < ProcGlobal->allProcCount; index++)
+ {
+ XLogRecPtr procLSN = ProcGlobal->allProcs[index].lastCommitLSN;
+ if (procLSN > lsn)
+ lsn = procLSN;
+ }
+ LWLockRelease(ProcArrayLock);
/* and construct a tuple with our data */
xid = GetLatestCommitTsData(&ts, &nodeid);
@@ -426,12 +441,14 @@ pg_last_committed_xact(PG_FUNCTION_ARGS)
* Construct a tuple descriptor for the result row. This must match this
* function's pg_proc entry!
*/
- tupdesc = CreateTemplateTupleDesc(3);
+ tupdesc = CreateTemplateTupleDesc(4);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "xid",
XIDOID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "timestamp",
TIMESTAMPTZOID, -1, 0);
- TupleDescInitEntry(tupdesc, (AttrNumber) 3, "roident",
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "lsn",
+ PG_LSNOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "roident",
OIDOID, -1, 0);
tupdesc = BlessTupleDesc(tupdesc);
@@ -447,8 +464,11 @@ pg_last_committed_xact(PG_FUNCTION_ARGS)
values[1] = TimestampTzGetDatum(ts);
nulls[1] = false;
- values[2] = ObjectIdGetDatum((Oid) nodeid);
- nulls[2] = false;
+ values[2] = LSNGetDatum(lsn);
+ nulls[2] = lsn == InvalidXLogRecPtr;
+
+ values[3] = ObjectIdGetDatum((Oid) nodeid);
+ nulls[3] = false;
}
htup = heap_form_tuple(tupdesc, values, nulls);
diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c
index 271a3146db..cd33849aea 100644
--- a/src/backend/access/transam/twophase.c
+++ b/src/backend/access/transam/twophase.c
@@ -2300,7 +2300,7 @@ RecordTransactionCommitPrepared(TransactionId xid,
TransactionTreeSetCommitTsData(xid, nchildren, children,
replorigin_session_origin_timestamp,
- replorigin_session_origin);
+ recptr, replorigin_session_origin);
/*
* We don't currently try to sleep before flush here ... nor is there any
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index c9516e03fa..9da257b83a 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -1355,6 +1355,7 @@ RecordTransactionCommit(void)
else
{
bool replorigin;
+ XLogRecPtr commit_lsn;
/*
* Are we using the replication origins feature? Or, in other words,
@@ -1391,7 +1392,7 @@ RecordTransactionCommit(void)
SetCurrentTransactionStopTimestamp();
- XactLogCommitRecord(xactStopTimestamp,
+ commit_lsn = XactLogCommitRecord(xactStopTimestamp,
nchildren, children, nrels, rels,
nmsgs, invalMessages,
RelcacheInitFileInval,
@@ -1418,7 +1419,8 @@ RecordTransactionCommit(void)
TransactionTreeSetCommitTsData(xid, nchildren, children,
replorigin_session_origin_timestamp,
- replorigin_session_origin);
+ commit_lsn, replorigin_session_origin);
+ MyProc->lastCommitLSN = commit_lsn;
}
/*
@@ -5881,7 +5883,9 @@ xact_redo_commit(xl_xact_parsed_commit *parsed,
/* Set the transaction commit timestamp and metadata */
TransactionTreeSetCommitTsData(xid, parsed->nsubxacts, parsed->subxacts,
- commit_time, origin_id);
+ commit_time, lsn, origin_id);
+
+ MyProc->lastCommitLSN = lsn;
if (standbyState == STANDBY_DISABLED)
{
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index 3be6040289..2fb75a2079 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -430,6 +430,7 @@ CreateSharedProcArray(void)
procArray->replication_slot_xmin = InvalidTransactionId;
procArray->replication_slot_catalog_xmin = InvalidTransactionId;
ShmemVariableCache->xactCompletionCount = 1;
+ ShmemVariableCache->finishedProcsLastCommitLSN = InvalidXLogRecPtr;
}
allProcs = ProcGlobal->allProcs;
@@ -580,6 +581,9 @@ ProcArrayRemove(PGPROC *proc, TransactionId latestXid)
/* Same with xactCompletionCount */
ShmemVariableCache->xactCompletionCount++;
+ if (proc->lastCommitLSN > ShmemVariableCache->finishedProcsLastCommitLSN)
+ ShmemVariableCache->finishedProcsLastCommitLSN = proc->lastCommitLSN;
+
ProcGlobal->xids[myoff] = InvalidTransactionId;
ProcGlobal->subxidStates[myoff].overflowed = false;
ProcGlobal->subxidStates[myoff].count = 0;
diff --git a/src/include/access/commit_ts.h b/src/include/access/commit_ts.h
index 7662f8e1a9..c7a146ec17 100644
--- a/src/include/access/commit_ts.h
+++ b/src/include/access/commit_ts.h
@@ -21,11 +21,10 @@ extern PGDLLIMPORT bool track_commit_timestamp;
extern void TransactionTreeSetCommitTsData(TransactionId xid, int nsubxids,
TransactionId *subxids, TimestampTz timestamp,
- RepOriginId nodeid);
+ XLogRecPtr commitLsn, RepOriginId nodeid);
extern bool TransactionIdGetCommitTsData(TransactionId xid,
TimestampTz *ts, RepOriginId *nodeid);
-extern TransactionId GetLatestCommitTsData(TimestampTz *ts,
- RepOriginId *nodeid);
+extern TransactionId GetLatestCommitTsData(TimestampTz *ts, RepOriginId *nodeid);
extern Size CommitTsShmemBuffers(void);
extern Size CommitTsShmemSize(void);
diff --git a/src/include/access/transam.h b/src/include/access/transam.h
index 338dfca5a0..c53f2d3810 100644
--- a/src/include/access/transam.h
+++ b/src/include/access/transam.h
@@ -237,6 +237,8 @@ typedef struct VariableCacheData
*/
FullTransactionId latestCompletedXid; /* newest full XID that has
* committed or aborted */
+ /* */
+ XLogRecPtr finishedProcsLastCommitLSN;
/*
* Number of top-level transactions with xids (i.e. which may have
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d6bf1f3274..d5fe8f4161 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6157,11 +6157,11 @@
prosrc => 'pg_xact_commit_timestamp_origin' },
{ oid => '3583',
- descr => 'get transaction Id, commit timestamp and replication origin of latest transaction commit',
+ descr => 'get transaction Id, commit timestamp, commit lsn and replication origin of latest transaction commit',
proname => 'pg_last_committed_xact', provolatile => 'v',
prorettype => 'record', proargtypes => '',
- proallargtypes => '{xid,timestamptz,oid}', proargmodes => '{o,o,o}',
- proargnames => '{xid,timestamp,roident}',
+ proallargtypes => '{xid,timestamptz,pg_lsn,oid}', proargmodes => '{o,o,o,o}',
+ proargnames => '{xid,timestamp,lsn,roident}',
prosrc => 'pg_last_committed_xact' },
{ oid => '3537', descr => 'get identification of SQL object',
diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h
index a58888f9e9..2a026b0844 100644
--- a/src/include/storage/proc.h
+++ b/src/include/storage/proc.h
@@ -258,6 +258,11 @@ struct PGPROC
PGPROC *lockGroupLeader; /* lock group leader, if I'm a member */
dlist_head lockGroupMembers; /* list of members, if I'm a leader */
dlist_node lockGroupLink; /* my member link, if I'm a member */
+
+ /*
+ * Last transaction metadata.
+ */
+ XLogRecPtr lastCommitLSN; /* cache of last committed LSN */
};
/* NOTE: "typedef struct PGPROC PGPROC" appears in storage/lock.h. */
--
2.17.1
