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 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]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. 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]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 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
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:
Show quoted text
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 transactions to wait for the replication of prior writes.
can't you use remote_apply?
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?
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.
<https://www.postgresql.org/docs/15/runtime-config-wal.html>