Hi James, There are some review in the thread that need to be addressed. it seems that we need to mark this entry "Waiting on Author" and move to the next CF [0].
Thanks [0] https://commitfest.postgresql.org/47/4355/ On Sat, 10 Jun 2023 at 05:27, James Coleman <jtc...@gmail.com> wrote: > I've previously noted in "Add last commit LSN to > pg_last_committed_xact()" [1] that it's not possible to monitor how > many bytes of WAL behind a logical replication slot is (computing such > is obviously trivial for physical slots) because the slot doesn't need > to replicate beyond the last commit. In some cases it's possible for > the current WAL location to be far beyond the last commit. A few > examples: > > - An idle server with checkout_timeout at a lower value (so lots of > empty WAL advance). > - Very large transactions: particularly insidious because committing a > 1 GB transaction after a small transaction may show almost zero time > lag even though quite a bit of data needs to be processed and sent > over the wire (so time to replay is significantly different from > current lag). > - A cluster with multiple databases complicates matters further, > because while physical replication is cluster-wide, the LSNs that > matter for logical replication are database specific. > > Since we don't expose the most recent commit's LSN there's no way to > say "the WAL is currently 1250, the last commit happened at 1000, the > slot has flushed up to 800, therefore there are at most 200 bytes > replication needs to read through to catch up. > > In the aforementioned thread [1] I'd proposed a patch that added a SQL > function pg_last_commit_lsn() to expose the most recent commit's LSN. > Robert Haas didn't think the initial version's modifications to > commit_ts made sense, and a subsequent approach adding the value to > PGPROC didn't have strong objections, from what I can see, but it also > didn't generate any enthusiasm. > > As I was thinking about how to improve things, I realized that this > information (since it's for monitoring anyway) fits more naturally > into the stats system. I'd originally thought of exposing it in > pg_stat_wal, but that's per-cluster rather than per-database (indeed, > this is a flaw I hadn't considered in the original patch), so I think > pg_stat_database is the correct location. > > I've attached a patch to track the latest commit's LSN in pg_stat_database. > > Regards, > James Coleman > > 1: > https://www.postgresql.org/message-id/flat/caaaqye9qbiau+j8rbun_jkbre-3hekluhfvvsyfsxqg0vql...@mail.gmail.com >
