回复： BUG #18213: Standby's repeatable read isolation level transaction encountered a "nonrepeatable read" problem

起个啥名好呢
feichanghong(at)qq(dot)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(at)163(dot)com&gt;;
发送时间:&nbsp;2023年11月29日(星期三) 中午1:26
收件人:&nbsp;"费长红"<feichanghong(at)qq(dot)com&gt;;"pgsql-bugs"<pgsql-bugs(at)lists(dot)postgresql(dot)org&gt;;

主题:&nbsp;Re: BUG #18213: Standby's repeatable read isolation level transaction encountered a "nonrepeatable read" problem

Hi,

&nbsp;Thanks for the report!
&nbsp;
PG Bug reporting form <noreply(at)postgresql(dot)org&gt; writes:

&gt; Standby executes select again (still within the existing repeatable read
&gt; transaction). We expect that the results of the two select should be the
&gt; same, but the second select did not get any data.
&gt; ```
&gt; postgres=*# select a, b from t where a = 5;
&gt;&nbsp;&nbsp; a | b
&gt; ---+---
&gt;&nbsp;&nbsp; 5 | 5
&gt; (1 row)
&gt;
&gt; postgres=*# set enable_seqscan to off;
&gt; SET
&gt; postgres=*# select a, b from t where a = 5;
&gt;&nbsp;&nbsp; a | b
&gt; ---+---
&gt; (0 rows)
&gt; ```

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;
&nbsp;a | b
---+---
(0 rows)

postgres=*# set enable_seqscan to&nbsp; on;
SET
postgres=*# select a, b from t where a = 5;
&nbsp;a | b
---+---
&nbsp;5 | 5
(1 row)

Different plan yieds different result is bad.

&gt; We have several ideas to fix this problem, but they all have obvious
&gt; flaws:
&gt; 1. In the WaitForOlderSnapshots function in the final stage of "create index
&gt; concurrently", wait for replication_slot_xmin to exceed limitXmin. In this
&gt; solution, when there are long transactions on Standby, "create index
&gt; 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..

&nbsp;* ....&nbsp; Also, we index only tuples that are valid
&nbsp;* as of the start of the scan (see table_index_build_scan), whereas a normal
&nbsp;* build takes care to include recently-dead tuples.&nbsp; This is OK because
&nbsp;* we won't mark the index valid until all transactions that might be able
&nbsp;* to see those tuples are gone.&nbsp; 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