public inbox for [email protected]
help / color / mirror / Atom feedFrom: Michail Nikolaev <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: [BUG?] check_exclusion_or_unique_constraint false negative
Date: Sun, 14 Jul 2024 20:01:50 +0200
Message-ID: <CANtu0oiktqQ2pwExoXqDpByXNCJa-KE5vQRodTRnmFHN_+qwHg@mail.gmail.com> (raw)
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%40m...
Attachments:
[text/x-patch] test_+_assert_to_reproduce_possible_issue_with_check_exclusion_or_unique_constraint.patch (2.3K, 3-test_+_assert_to_reproduce_possible_issue_with_check_exclusion_or_unique_constraint.patch)
download | inline diff:
Subject: [PATCH] test + assert to reproduce possible issue with check_exclusion_or_unique_constraint
---
Index: src/backend/executor/execIndexing.c
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
--- a/src/backend/executor/execIndexing.c (revision d5e6891502ca9e359aa5f5a381d904fe9d606338)
+++ b/src/backend/executor/execIndexing.c (date 1720979367766)
@@ -889,6 +889,11 @@
}
index_endscan(index_scan);
+ if (!conflict && values[0] == 31337) {
+ ereport(ERROR,
+ (errcode(ERRCODE_EXCLUSION_VIOLATION),
+ errmsg("we know 31337 in the index!")));
+ }
/*
* Ordinarily, at this point the search should have found the originally
Index: src/test/modules/test_misc/t/006_concurrently_unique_fail.pl
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/test/modules/test_misc/t/006_concurrently_unique_fail.pl b/src/test/modules/test_misc/t/006_concurrently_unique_fail.pl
new file mode 100644
--- /dev/null (date 1720979285840)
+++ b/src/test/modules/test_misc/t/006_concurrently_unique_fail.pl (date 1720979285840)
@@ -0,0 +1,39 @@
+
+# Copyright (c) 2024, PostgreSQL Global Development Group
+
+# Test REINDEX CONCURRENTLY with concurrent modifications and HOT updates
+use strict;
+use warnings;
+
+use Config;
+use Errno;
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+
+my ($node, $result);
+$node = PostgreSQL::Test::Cluster->new('RC_test');
+$node->init;
+$node->append_conf('postgresql.conf', 'fsync = off');
+$node->append_conf('postgresql.conf', 'autovacuum = off');
+$node->start;
+$node->safe_psql('postgres', q(CREATE UNLOGGED TABLE tbl(i int primary key, n int)));
+
+$node->safe_psql('postgres', q(INSERT INTO tbl VALUES(31337,1)));
+
+$node->pgbench(
+ '--no-vacuum --client=40 --transactions=1000',
+ 0,
+ [qr{actually processed}],
+ [qr{^$}],
+ 'concurrent INSERTs',
+ {
+ 'on_conflicts' => q(
+ INSERT INTO tbl VALUES(31337,1) on conflict(i) do update set n = EXCLUDED.n + 1;
+ )
+ });
+
+$node->stop;
+done_testing();
\ No newline at end of file
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [BUG?] check_exclusion_or_unique_constraint false negative
In-Reply-To: <CANtu0oiktqQ2pwExoXqDpByXNCJa-KE5vQRodTRnmFHN_+qwHg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox