MIME-Version: 1.0
References: <CANtu0oiktqQ2pwExoXqDpByXNCJa-KE5vQRodTRnmFHN_+qwHg@mail.gmail.com>
 <CANtu0ohU2XRV9shtu14CffLPDS1x10q7ebOGf-vX0p+45_L8jw@mail.gmail.com>
 <CANtu0oh0tspW-xWzDGWP9ehz96KPt9aUP1c9JYhdBYxKsB0jpA@mail.gmail.com>
 <CANtu0ohUB9ky45iiMAYN1fGyt82+cg=+UYBom=P7drb+=97G9w@mail.gmail.com> <TYAPR01MB56921C9C3D21B0D62FF76330F5B22@TYAPR01MB5692.jpnprd01.prod.outlook.com>
In-Reply-To: <TYAPR01MB56921C9C3D21B0D62FF76330F5B22@TYAPR01MB5692.jpnprd01.prod.outlook.com>
From: Michail Nikolaev <michail.nikolaev@gmail.com>
Date: Thu, 1 Aug 2024 11:25:36 +0200
Message-ID: <CANtu0og=5v4j8onS4nyJ4zMPdh-EPFxmiEi5PLoyZrmqHA6RKw@mail.gmail.com>
Subject: Re: [BUG?] check_exclusion_or_unique_constraint false negative
To: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Andres Freund <andres@anarazel.de>, 
	Amit Kapila <amit.kapila16@gmail.com>
Content-Type: multipart/alternative; boundary="000000000000ce5232061e9bcbf1"
Archived-At: <https://www.postgresql.org/message-id/CANtu0og%3D5v4j8onS4nyJ4zMPdh-EPFxmiEi5PLoyZrmqHA6RKw%40mail.gmail.com>
Precedence: bulk

--000000000000ce5232061e9bcbf1
Content-Type: text/plain; charset="UTF-8"

Hello, Hayato!

> Thanks for pointing out the issue!

Thanks for your attention!

> IIUC, the issue can happen when two concurrent transactions using
DirtySnapshot access
> the same tuples, which is not specific to the parallel apply

Not exactly, it happens for any DirtySnapshot scan over a B-tree index with
some other transaction updating the same index page (even using the MVCC
snapshot).

So, logical replication related scenario looks like this:

* subscriber worker receives a tuple update\delete from the publisher
* it calls RelationFindReplTupleByIndex to find the tuple in the local table
* some other transaction updates the tuple in the local table (on
subscriber side) in parallel
* RelationFindReplTupleByIndex may not find the tuple because it uses
DirtySnapshot
* update\delete is lost

Parallel apply mode looks like more dangerous because it uses multiple
workers on the subscriber side, so the probability of the issue is higher.
In that case, "some other transaction" is just another worker applying
changes of different transaction in parallel.

Best regards,
Mikhail.

--000000000000ce5232061e9bcbf1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello, Hayato!<br><br>&gt; Thanks for pointing out the iss=
ue!<br><br>Thanks for your attention!<br><br>&gt; IIUC, the issue can happe=
n when two concurrent transactions using DirtySnapshot access<br>&gt; the s=
ame tuples, which is not specific to the parallel apply<br><br>Not exactly,=
 it happens for any DirtySnapshot scan over a B-tree index with some other =
transaction updating the same index page (even using the MVCC snapshot).<br=
><br>So, logical replication related scenario looks like this:<br><br>* sub=
scriber worker receives a tuple update\delete from the publisher<br>* it ca=
lls RelationFindReplTupleByIndex to find the tuple in the local table<br>* =
some other transaction updates the tuple in the local table (on subscriber =
side) in parallel<br>* RelationFindReplTupleByIndex may not find the tuple =
because it uses DirtySnapshot<br>* update\delete is lost<br><br>Parallel ap=
ply=C2=A0mode looks like more dangerous because it uses multiple workers on=
 the subscriber=C2=A0side, so the probability of the issue is higher.<br>In=
 that case, &quot;some other transaction&quot; is just another worker apply=
ing changes of different transaction in parallel.<br><br>Best regards,<br>M=
ikhail.<br>=C2=A0</div>

--000000000000ce5232061e9bcbf1--