Hi Bharath, Thanks a lot for your reply. It looks like this is exactly what I need. For my use case, I'm trying to get read-only transactions to wait for the replication of prior writes.
Sincerely, Tej Kashi MMath CS, University of Waterloo Waterloo, ON, CA On Thu, 16 Mar 2023 at 01:36, Bharath Rupireddy < bharath.rupireddyforpostg...@gmail.com> wrote: > 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 >
