Обсуждение: How to check for in-progress transactions
Hi everyone,
I'm Tej, a grad student poking around postgres for a project.
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)
The first way to do this would be to check the WalSndCtl->lsn[] array to see if the current max lsn of the system has replicated or not. This works well when postgres is running and being actively used. However, when postgres has just started up, WalSndCtl->lsn[] values could be 0, but there could still be transactions waiting to replicate.
The second way to do it would be to scan ProcGlobal to check for active xids. However, the issue is that I'm calling ProcArrayEndTransaction() before calling SyncRepWaitForLSN() to ensure that the transaction becomes visible to other transactions before it begins to wait in the SyncRep queue.
So, with this change, if I scan ProcGlobal, I would not see transactions that have been committed locally but are yet to be replicated to synchronous standbys because ProcArrayEndTransaction() would have marked the transaction as completed.
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 would appreciate it if someone could point me in the right direction!
Sincerely,
Tej Kashi
MMath CS, University of Waterloo
Waterloo, ON, CA
On Thu, Mar 16, 2023 at 1:18 AM Tejasvi Kashi <mail@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 synchronousstandbys (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 visibilitychange added above, is there a way for me to check for transactions that have been committed locally but are waitingfor 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
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.rupireddyforpostgres@gmail.com> wrote:
On Thu, Mar 16, 2023 at 1:18 AM Tejasvi Kashi <mail@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
On Thu, Mar 16, 2023 at 4:43 PM Tejasvi Kashi <mail@tejasvi.dev> wrote: > 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 transactionsto wait for the replication of prior writes. can't you use remote_apply? https://www.postgresql.org/docs/15/runtime-config-wal.html
On Thu, Mar 16, 2023 at 17:01, Melanie Plageman <melanieplageman@gmail.com> wrote:
On Thu, Mar 16, 2023 at 4:43 PM Tejasvi Kashi <mail@tejasvi.dev> wrote:
> 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.
can't you use remote_apply?
https://www.postgresql.org/docs/15/runtime-config-wal.html
That will ensure that the writes are acknowledged only after remote application. But, in my case, I’m trying to get read transactions to wait if they have seen a write that is yet to be replicated.