References: <18213-dbcf8d67b0e687f5@postgresql.org> <87cyvtkspj.fsf@163.com>
 <tencent_2F3B410655A7D447BF092A470CC297D2DA06@qq.com>
User-agent: mu4e 1.10.7; emacs 29.1
From: zhihuifan1213@163.com
To: =?utf-8?B?6LS56ZW/57qi?= <feichanghong@qq.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: =?utf-8?B?5Zue5aSN77ya?= BUG #18213: Standby's repeatable read
 isolation level
 transaction encountered a "nonrepeatable read" problem
Date: Wed, 29 Nov 2023 15:50:00 +0800
In-reply-to: <tencent_2F3B410655A7D447BF092A470CC297D2DA06@qq.com>
Message-ID: <87zfyxj88c.fsf@163.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/87zfyxj88c.fsf%40163.com>
Precedence: bulk


"=E8=B4=B9=E9=95=BF=E7=BA=A2" <feichanghong@qq.com> writes:

> -------------------------------------------------------------------------=
------------------------------------------------
>
>  *  =E8=B5=B7=E4=B8=AA=E5=95=A5=E5=90=8D=E5=A5=BD=E5=91=A2=20=20
>    feichanghong@qq.com=20=20
>=20=20
> 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.

I think this is acceptable since its behavior is same as we wait for the
transactions in primary.

> In addition, even if there is no select query on the standby, RW's "creat=
e index" command may wait for a period of time,
> which affected by the wal_receiver_status_interval parameter.

The "wait" happens at the very last of index building, so the building
stages gives an enough time for standby to exceeds the xmin, so in a
real case, I think the overhead will be pretty low.=20

> You can see attachment for the patch.

I take a look at the that, function GetReplicationSlotXmin should be not
needed. You can use ProcArrayGetReplicationSlotXmin directly.

After searching the caller of WaitForOlderSnapshots, I think this bug
probably has impact on "deteach partition concurrently" / "reindex
concurrently" features.  All of them needs the same fix.=20

--=20
Best Regards
Andy Fan