Re: Add last commit LSN to pg_last_committed_xact()

Hi,

On 2022-01-17 18:34:16 -0300, Alvaro Herrera wrote:
> Maybe it would work to have a single LSN in shared memory, as an atomic
> variable, which uses monotonic advance[1] to be updated.

That could be a reasonable approach.

> Whether this is updated or not would depend on a new GUC, maybe
> track_latest_commit_lsn. Causing performance pain during transaction commit
> is not great, but at least this way it shouldn't be *too* a large hit.

What kind of consistency are we expecting from this new bit of information?
Does it have to be perfectly aligned with visibility? If so, it'd need to
happen in ProcArrayEndTransaction(), with ProcArrayLock held - which I'd
consider a complete no-go, that's way too contended.

If it's "just" another piece of work happening "sometime around" transaction
commit, it'd be a bit less concerning.

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.

Greetings,

Andres Freund