MIME-Version: 1.0
From: Michail Nikolaev <michail.nikolaev@gmail.com>
Date: Sun, 14 Jul 2024 20:01:50 +0200
Message-ID: <CANtu0oiktqQ2pwExoXqDpByXNCJa-KE5vQRodTRnmFHN_+qwHg@mail.gmail.com>
Subject: [BUG?] check_exclusion_or_unique_constraint false negative
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000de2b20061d38e8e7"
Archived-At: <https://www.postgresql.org/message-id/CANtu0oiktqQ2pwExoXqDpByXNCJa-KE5vQRodTRnmFHN_%2BqwHg%40mail.gmail.com>
Precedence: bulk

--000000000000de2b20061d38e8e7
Content-Type: multipart/alternative; boundary="000000000000de2b1f061d38e8e5"

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

Hello, everyone!

While reviewing [1], I noticed that check_exclusion_or_unique_constraint
occasionally returns false negatives for btree unique indexes during UPSERT
operations.
Although this doesn't cause any real issues with INSERT ON CONFLICT, I
wanted to bring it to your attention, as it might indicate an underlying
problem.

Attached is a patch to reproduce the issue.

make -C src/test/modules/test_misc/ check PROVE_TESTS='t/006_*'
....
   Failed test 'concurrent INSERTs status (got 2 vs expected 0)'
#   at t/006_concurrently_unique_fail.pl line 26.

#   Failed test 'concurrent INSERTs stderr /(?^:^$)/'
#   at t/006_concurrently_unique_fail.pl line 26.
#                   'pgbench: error: client 34 script 0 aborted in command
0 query 0: ERROR:  we know 31337 in the index!

Best regards,
Mikhail,

[1]:
https://www.postgresql.org/message-id/flat/CANtu0ogs10w%3DDgbYzZ8MswXE3PUC3J4SGDc0YEuZZeWbL0b6HA%40mail.gmail.com#8c01dcf6051e28c47d25e9471736947e

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

<div dir=3D"ltr">Hello, everyone!<div><br></div><div>While reviewing [1], I=
 noticed that check_exclusion_or_unique_constraint occasionally returns fal=
se negatives for btree unique indexes during UPSERT operations.</div><div>A=
lthough this doesn&#39;t cause any real issues with INSERT ON CONFLICT, I w=
anted to bring it to your attention, as it might indicate an underlying pro=
blem.<br><br>Attached is a patch to reproduce the issue.<br></div><div><br>=
</div><div>make -C src/test/modules/test_misc/ check PROVE_TESTS=3D&#39;t/0=
06_*&#39;<br></div><div>....</div><div>=C2=A0 =C2=A0Failed test &#39;concur=
rent INSERTs status (got 2 vs expected 0)&#39;<br># =C2=A0 at t/<a href=3D"=
http://006_concurrently_unique_fail.pl">006_concurrently_unique_fail.pl</a>=
 line 26.<br><br># =C2=A0 Failed test &#39;concurrent INSERTs stderr /(?^:^=
$)/&#39;<br># =C2=A0 at t/<a href=3D"http://006_concurrently_unique_fail.pl=
">006_concurrently_unique_fail.pl</a> line 26.<br># =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 &#39;pgbench: error: client 34 sc=
ript 0 aborted in command 0 query 0: ERROR: =C2=A0we know 31337 in the inde=
x!<br></div><div><br></div><div>Best regards,</div><div>Mikhail,</div><div>=
<br></div><div>[1]:=C2=A0<a href=3D"https://www.postgresql.org/message-id/f=
lat/CANtu0ogs10w%3DDgbYzZ8MswXE3PUC3J4SGDc0YEuZZeWbL0b6HA%40mail.gmail.com#=
8c01dcf6051e28c47d25e9471736947e">https://www.postgresql.org/message-id/fla=
t/CANtu0ogs10w%3DDgbYzZ8MswXE3PUC3J4SGDc0YEuZZeWbL0b6HA%40mail.gmail.com#8c=
01dcf6051e28c47d25e9471736947e</a></div></div>

--000000000000de2b1f061d38e8e5--
--000000000000de2b20061d38e8e7
Content-Type: text/x-patch; charset="US-ASCII"; 
	name="test_+_assert_to_reproduce_possible_issue_with_check_exclusion_or_unique_constraint.patch"
Content-Disposition: attachment; 
	filename="test_+_assert_to_reproduce_possible_issue_with_check_exclusion_or_unique_constraint.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_lylutier0>
X-Attachment-Id: f_lylutier0

U3ViamVjdDogW1BBVENIXSB0ZXN0ICsgYXNzZXJ0IHRvIHJlcHJvZHVjZSBwb3NzaWJsZSBpc3N1
ZSB3aXRoIGNoZWNrX2V4Y2x1c2lvbl9vcl91bmlxdWVfY29uc3RyYWludAotLS0KSW5kZXg6IHNy
Yy9iYWNrZW5kL2V4ZWN1dG9yL2V4ZWNJbmRleGluZy5jCklERUEgYWRkaXRpb25hbCBpbmZvOgpT
dWJzeXN0ZW06IGNvbS5pbnRlbGxpai5vcGVuYXBpLmRpZmYuaW1wbC5wYXRjaC5DaGFyc2V0RVAK
PCs+VVRGLTgKPT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09
PT09PT09PT09PT09PT09PT09PQpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvZXhlY3V0b3IvZXhl
Y0luZGV4aW5nLmMgYi9zcmMvYmFja2VuZC9leGVjdXRvci9leGVjSW5kZXhpbmcuYwotLS0gYS9z
cmMvYmFja2VuZC9leGVjdXRvci9leGVjSW5kZXhpbmcuYwkocmV2aXNpb24gZDVlNjg5MTUwMmNh
OWUzNTlhYTVmNWEzODFkOTA0ZmU5ZDYwNjMzOCkKKysrIGIvc3JjL2JhY2tlbmQvZXhlY3V0b3Iv
ZXhlY0luZGV4aW5nLmMJKGRhdGUgMTcyMDk3OTM2Nzc2NikKQEAgLTg4OSw2ICs4ODksMTEgQEAK
IAl9CiAKIAlpbmRleF9lbmRzY2FuKGluZGV4X3NjYW4pOworCWlmICghY29uZmxpY3QgJiYgdmFs
dWVzWzBdID09IDMxMzM3KSB7CisJCWVyZXBvcnQoRVJST1IsCisJCQkJKGVycmNvZGUoRVJSQ09E
RV9FWENMVVNJT05fVklPTEFUSU9OKSwKKwkJCQkJCWVycm1zZygid2Uga25vdyAzMTMzNyBpbiB0
aGUgaW5kZXghIikpKTsKKwl9CiAKIAkvKgogCSAqIE9yZGluYXJpbHksIGF0IHRoaXMgcG9pbnQg
dGhlIHNlYXJjaCBzaG91bGQgaGF2ZSBmb3VuZCB0aGUgb3JpZ2luYWxseQpJbmRleDogc3JjL3Rl
c3QvbW9kdWxlcy90ZXN0X21pc2MvdC8wMDZfY29uY3VycmVudGx5X3VuaXF1ZV9mYWlsLnBsCklE
RUEgYWRkaXRpb25hbCBpbmZvOgpTdWJzeXN0ZW06IGNvbS5pbnRlbGxpai5vcGVuYXBpLmRpZmYu
aW1wbC5wYXRjaC5DaGFyc2V0RVAKPCs+VVRGLTgKPT09PT09PT09PT09PT09PT09PT09PT09PT09
PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PQpkaWZmIC0tZ2l0IGEvc3Jj
L3Rlc3QvbW9kdWxlcy90ZXN0X21pc2MvdC8wMDZfY29uY3VycmVudGx5X3VuaXF1ZV9mYWlsLnBs
IGIvc3JjL3Rlc3QvbW9kdWxlcy90ZXN0X21pc2MvdC8wMDZfY29uY3VycmVudGx5X3VuaXF1ZV9m
YWlsLnBsCm5ldyBmaWxlIG1vZGUgMTAwNjQ0Ci0tLSAvZGV2L251bGwJKGRhdGUgMTcyMDk3OTI4
NTg0MCkKKysrIGIvc3JjL3Rlc3QvbW9kdWxlcy90ZXN0X21pc2MvdC8wMDZfY29uY3VycmVudGx5
X3VuaXF1ZV9mYWlsLnBsCShkYXRlIDE3MjA5NzkyODU4NDApCkBAIC0wLDAgKzEsMzkgQEAKKwor
IyBDb3B5cmlnaHQgKGMpIDIwMjQsIFBvc3RncmVTUUwgR2xvYmFsIERldmVsb3BtZW50IEdyb3Vw
CisKKyMgVGVzdCBSRUlOREVYIENPTkNVUlJFTlRMWSB3aXRoIGNvbmN1cnJlbnQgbW9kaWZpY2F0
aW9ucyBhbmQgSE9UIHVwZGF0ZXMKK3VzZSBzdHJpY3Q7Cit1c2Ugd2FybmluZ3M7CisKK3VzZSBD
b25maWc7Cit1c2UgRXJybm87CisKK3VzZSBQb3N0Z3JlU1FMOjpUZXN0OjpDbHVzdGVyOwordXNl
IFBvc3RncmVTUUw6OlRlc3Q6OlV0aWxzOwordXNlIFRlc3Q6Ok1vcmU7CisKKworbXkgKCRub2Rl
LCAkcmVzdWx0KTsKKyRub2RlID0gUG9zdGdyZVNRTDo6VGVzdDo6Q2x1c3Rlci0+bmV3KCdSQ190
ZXN0Jyk7Ciskbm9kZS0+aW5pdDsKKyRub2RlLT5hcHBlbmRfY29uZigncG9zdGdyZXNxbC5jb25m
JywgJ2ZzeW5jID0gb2ZmJyk7Ciskbm9kZS0+YXBwZW5kX2NvbmYoJ3Bvc3RncmVzcWwuY29uZics
ICdhdXRvdmFjdXVtID0gb2ZmJyk7Ciskbm9kZS0+c3RhcnQ7Ciskbm9kZS0+c2FmZV9wc3FsKCdw
b3N0Z3JlcycsIHEoQ1JFQVRFIFVOTE9HR0VEIFRBQkxFIHRibChpIGludCBwcmltYXJ5IGtleSwg
biBpbnQpKSk7CisKKyRub2RlLT5zYWZlX3BzcWwoJ3Bvc3RncmVzJywgcShJTlNFUlQgSU5UTyB0
YmwgVkFMVUVTKDMxMzM3LDEpKSk7CisKKyRub2RlLT5wZ2JlbmNoKAorCSctLW5vLXZhY3V1bSAt
LWNsaWVudD00MCAtLXRyYW5zYWN0aW9ucz0xMDAwJywKKwkwLAorCVtxcnthY3R1YWxseSBwcm9j
ZXNzZWR9XSwKKwlbcXJ7XiR9XSwKKwknY29uY3VycmVudCBJTlNFUlRzJywKKwl7CisJCSdvbl9j
b25mbGljdHMnID0+IHEoCisJCQlJTlNFUlQgSU5UTyB0YmwgVkFMVUVTKDMxMzM3LDEpIG9uIGNv
bmZsaWN0KGkpIGRvIHVwZGF0ZSBzZXQgbiA9IEVYQ0xVREVELm4gKyAxOworCQkpCisJfSk7CisK
KyRub2RlLT5zdG9wOworZG9uZV90ZXN0aW5nKCk7ClwgTm8gbmV3bGluZSBhdCBlbmQgb2YgZmls
ZQo=
--000000000000de2b20061d38e8e7--