MIME-Version: 1.0
References: <CANtu0oiktqQ2pwExoXqDpByXNCJa-KE5vQRodTRnmFHN_+qwHg@mail.gmail.com>
In-Reply-To: <CANtu0oiktqQ2pwExoXqDpByXNCJa-KE5vQRodTRnmFHN_+qwHg@mail.gmail.com>
From: Michail Nikolaev <michail.nikolaev@gmail.com>
Date: Sun, 21 Jul 2024 17:27:01 +0200
Message-ID: <CANtu0ohU2XRV9shtu14CffLPDS1x10q7ebOGf-vX0p+45_L8jw@mail.gmail.com>
Subject: Re: [BUG?] check_exclusion_or_unique_constraint false negative
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Andres Freund <andres@anarazel.de>
Content-Type: multipart/alternative; boundary="0000000000001532f2061dc39096"
Archived-At: <https://www.postgresql.org/message-id/CANtu0ohU2XRV9shtu14CffLPDS1x10q7ebOGf-vX0p%2B45_L8jw%40mail.gmail.com>
Precedence: bulk

--0000000000001532f2061dc39096
Content-Type: text/plain; charset="UTF-8"

Hello, Andres.

Sorry to bother you, but I feel it's necessary to validate the possible
issue regarding someone who can decide whether it is okay or not.
The issue is reproducible with the first UPSERT implementation (your commit
168d5805e4c08bed7b95d351bf097cff7c07dd65 from 2015) and up to now.

The problem appears as follows:
* A unique index contains a specific value (in the test, it is the only
value for the entire index).
* check_exclusion_or_unique_constraint returns FALSE for that value in some
random cases.
* Technically, this means index_getnext finds 0 records, even though we
know the value exists in the index.

I was able to reproduce this only with an UNLOGGED table.
I can't find any scenarios that are actually broken (since the issue is
resolved by speculative insertion later), but this looks suspicious to me.
It could be a symptom of some tricky race condition in the btree.

Best regards,
Mikhail

>

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

<div dir=3D"ltr"><div dir=3D"ltr">Hello, Andres.<br><br>Sorry to bother you=
, but I feel it&#39;s necessary to validate the possible issue regarding so=
meone who can decide whether it is okay or not.<br>The issue is reproducibl=
e with the first UPSERT implementation (your commit 168d5805e4c08bed7b95d35=
1bf097cff7c07dd65 from 2015) and up to now.<br><br>The problem appears as f=
ollows:<br>* A unique index contains a specific value (in the test, it is t=
he only value for the entire index).<br>* check_exclusion_or_unique_constra=
int returns FALSE for that value in some random cases.<br>* Technically, th=
is means index_getnext finds 0 records, even though we know the value exist=
s in the index.<br><br>I was able to reproduce this only with an UNLOGGED t=
able.<br>I can&#39;t find any scenarios that are actually broken (since the=
 issue is resolved by speculative insertion later), but this looks suspicio=
us to me. It could be a symptom of some tricky race condition in the btree.=
<br><br>Best regards,<br>Mikhail<br></div><div class=3D"gmail_quote"><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"></div>
</blockquote></div></div>

--0000000000001532f2061dc39096--