From: | Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> |
---|---|
To: | Tejasvi Kashi <mail(at)tejasvi(dot)dev> |
Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to check for in-progress transactions |
Date: | 2023-03-16 05:36:37 |
Message-ID: | CALj2ACW=x9jbE1E1r4bS5gAioEmEZsUWkYny0Cc1Qq_Zof47_g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2023-03-16 06:29:31 | Re: Reconcile stats in find_tabstat_entry() and get rid of PgStat_BackendFunctionEntry |
Previous Message | Michael Paquier | 2023-03-16 05:25:18 | Re: Get rid of PgStat_BackendFunctionEntry |