Re: How to check for in-progress transactions

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(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

> On Thu, Mar 16, 2023 at 1:18 AM Tejasvi Kashi <mail(at)tejasvi(dot)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
>