From: 费长红 <feichanghong@qq.com>
To: zhihuifan1213 <zhihuifan1213@163.com>
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: 回复: BUG #18213: Standby's repeatable read isolation level transaction encountered a "nonrepeatable read" problem
Date: Wed, 29 Nov 2023 14:57:16 +0800
Message-ID: <tencent_2F3B410655A7D447BF092A470CC297D2DA06@qq.com> (raw)
In-Reply-To: <87cyvtkspj.fsf@163.com>
References: <18213-dbcf8d67b0e687f5@postgresql.org>
<87cyvtkspj.fsf@163.com>
起个啥名好呢
feichanghong@qq.com
Indeed, I simply implemented and verified the solution. In the above test, the "create index" command on RW will hang until the transaction on standby is committed or aborted.
In addition, even if there is no select query on the standby, RW's "create index" command may wait for a period of time, which affected by the wal_receiver_status_interval parameter.
You can see attachment for the patch.
------------------ 原始邮件 ------------------
发件人: "zhihuifan1213" <zhihuifan1213@163.com>;
发送时间: 2023年11月29日(星期三) 中午1:26
收件人: "费长红"<feichanghong@qq.com>;"pgsql-bugs"<pgsql-bugs@lists.postgresql.org>;
主题: Re: BUG #18213: Standby's repeatable read isolation level transaction encountered a "nonrepeatable read" problem
Hi,
Thanks for the report!
PG Bug reporting form <noreply@postgresql.org> writes:
> Standby executes select again (still within the existing repeatable read
> transaction). We expect that the results of the two select should be the
> same, but the second select did not get any data.
> ```
> postgres=*# select a, b from t where a = 5;
> a | b
> ---+---
> 5 | 5
> (1 row)
>
> postgres=*# set enable_seqscan to off;
> SET
> postgres=*# select a, b from t where a = 5;
> a | b
> ---+---
> (0 rows)
> ```
I can confirm this bug in the current master and continue with your test:
postgres=*# set enable_seqscan to off;
SET
postgres=*# select a, b from t where a = 5;
a | b
---+---
(0 rows)
postgres=*# set enable_seqscan to on;
SET
postgres=*# select a, b from t where a = 5;
a | b
---+---
5 | 5
(1 row)
Different plan yieds different result is bad.
> We have several ideas to fix this problem, but they all have obvious
> flaws:
> 1. In the WaitForOlderSnapshots function in the final stage of "create index
> concurrently", wait for replication_slot_xmin to exceed limitXmin. In this
> solution, when there are long transactions on Standby, "create index
> concurrently" operations may be blocked.
+1 for this one. The current code doesn't take the advice from the
comments for validate_index, where it says..
* .... Also, we index only tuples that are valid
* as of the start of the scan (see table_index_build_scan), whereas a normal
* build takes care to include recently-dead tuples. This is OK because
* we won't mark the index valid until all transactions that might be able
* to see those tuples are gone. The reason for doing that is ...
In the current code, it doesn't consider the sessiones in standby.
I guess you have worked out a fix for this, if so, could you provide
one for review / test?
--
Best Regards
Andy Fan
Attachments:wait_replication_slot_xmin.patch (application/octet-stream, 2.6K) downloaddiff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.cindex 0b3b8e98b8..cf3e1d449c 100644--- a/src/backend/commands/indexcmds.c+++ b/src/backend/commands/indexcmds.c@@ -424,6 +424,8 @@ WaitForOlderSnapshots(TransactionId limitXmin, bool progress)
int n_old_snapshots;
int i;
VirtualTransactionId *old_snapshots;
+ TransactionId replication_slot_xmin = InvalidTransactionId;+ TransactionId replication_slot_catalog_xmin = InvalidTransactionId;
old_snapshots = GetCurrentVirtualXIDs(limitXmin, true, false,
PROC_IS_AUTOVACUUM | PROC_IN_VACUUM
@@ -483,6 +485,23 @@ WaitForOlderSnapshots(TransactionId limitXmin, bool progress)
if (progress)
pgstat_progress_update_param(PROGRESS_WAITFOR_DONE, i + 1);
}
++ do+ {+ /*+ * Wait for the oldest replication slot xmin to exceed limitXmin to prevent+ * standby being unable to access old version data through the newly built+ * index.+ * It is necessary to wait only if replication_slot_xmin is "normal".+ */+ CHECK_FOR_INTERRUPTS();+ GetReplicationSlotXmin(&replication_slot_xmin, &replication_slot_catalog_xmin);+ if (!TransactionIdIsNormal(replication_slot_xmin))+ break;+ if (!NormalTransactionIdPrecedes(replication_slot_xmin, limitXmin))+ break;+ pg_usleep(1000000);+ } while (true);
}
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.cindex 80ab026bf5..e314b54fe3 100644--- a/src/backend/storage/ipc/procarray.c+++ b/src/backend/storage/ipc/procarray.c@@ -2503,6 +2503,18 @@ GetSnapshotData(Snapshot snapshot)
return snapshot;
}
+/*+ * Get replication_slot_xmin and replication_slot_catalog_xmin from ProcArrayStruct+ */+void+GetReplicationSlotXmin(TransactionId* xmin, TransactionId* catalog_xmin)+{+ LWLockAcquire(ProcArrayLock, LW_SHARED);+ *xmin = procArray->replication_slot_xmin;+ *catalog_xmin = procArray->replication_slot_catalog_xmin;+ LWLockRelease(ProcArrayLock);+}+
/*
* ProcArrayInstallImportedXmin -- install imported xmin into MyProc->xmin
*
diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.hindex d8cae3ce1c..f985aae96d 100644--- a/src/include/storage/procarray.h+++ b/src/include/storage/procarray.h@@ -45,6 +45,7 @@ extern int GetMaxSnapshotXidCount(void);
extern int GetMaxSnapshotSubxidCount(void);
extern Snapshot GetSnapshotData(Snapshot snapshot);
+extern void GetReplicationSlotXmin(TransactionId* xmin, TransactionId* catalog_xmin);
extern bool ProcArrayInstallImportedXmin(TransactionId xmin,
VirtualTransactionId *sourcevxid);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: feichanghong@qq.com, zhihuifan1213@163.com
Subject: Re: 回复: BUG #18213: Standby's repeatable read isolation level transaction encountered a "nonrepeatable read" problem
In-Reply-To: <tencent_2F3B410655A7D447BF092A470CC297D2DA06@qq.com>
References: <18213-dbcf8d67b0e687f5@postgresql.org> <87cyvtkspj.fsf@163.com> <tencent_2F3B410655A7D447BF092A470CC297D2DA06@qq.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox