On Thu, Mar 16, 2023 at 1:18 AM Tejasvi Kashi <m...@tejasvi.dev> wrote: > > For my use case, I'm trying to ascertain if there are any in-flight > transactions that are yet to be replicated to synchronous standbys (in a > synchronous streaming replication setting) > > I've been looking at sent_lsn, write_lsn, flush_lsn etc., of the walsender, > but with no success. Considering the visibility change added above, is there > a way for me to check for transactions that have been committed locally but > are waiting for replication?
I think you can look for SyncRep wait_event from pg_stat_activity, something like [1]. The backends will wait indefinitely until latch is set (postmaster death or an ack is received from sync standbys) in SyncRepWaitForLSN(). backend_xid is your locally-committed-but-not-yet-replicated txn id. Will this help? Well, if you're planning to know all locally-committed-but-not-yet-replicated txns from an extension or any other source code, you may run the full query [1] or if running a query seems costly, you can look at what pg_stat_get_activity() does to get each backend's wait_event_info and have your code do that. BTW, what exactly is the use-case that'd want locally-committed-but-not-yet-replicated txns info? [1] postgres=# select * from pg_stat_activity where backend_type = 'client backend' and wait_event = 'SyncRep'; -[ RECORD 1 ]----+------------------------------ datid | 5 datname | postgres pid | 4187907 leader_pid | usesysid | 10 usename | ubuntu application_name | psql client_addr | client_hostname | client_port | -1 backend_start | 2023-03-16 05:16:56.917124+00 xact_start | 2023-03-16 05:17:09.472092+00 query_start | 2023-03-16 05:17:09.472092+00 state_change | 2023-03-16 05:17:09.472095+00 wait_event_type | IPC wait_event | SyncRep state | active backend_xid | 731 backend_xmin | 731 query_id | query | create table foo(col1 int); backend_type | client backend -- Bharath Rupireddy PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
