On Mon, Jun 17, 2024 at 07:00:51PM +0200, Michail Nikolaev wrote: > The same issue may happen in case of CREATE/DROP INDEX CONCURRENTLY as well.
While looking at all that, I've been also curious about this specific point, and it is indeed possible to finish in a state where a duplicate key would be found in one of indexes selected by the executor during an INSERT ON CONFLICT while a concurrent set of CICs and DICs are run, so you don't really need a REINDEX. See for example the attached test. -- Michael
diff --git a/src/test/modules/test_misc/t/009_cdic_concurrently_unique_fail.pl b/src/test/modules/test_misc/t/009_cdic_concurrently_unique_fail.pl
new file mode 100644
index 0000000000..bd37c797a3
--- /dev/null
+++ b/src/test/modules/test_misc/t/009_cdic_concurrently_unique_fail.pl
@@ -0,0 +1,46 @@
+
+# Copyright (c) 2024-2024, PostgreSQL Global Development Group
+
+# Test CREATE/DROP INDEX CONCURRENTLY with concurrent INSERT
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+
+use Test::More;
+
+my ($node, $result);
+
+#
+# Test set-up
+#
+$node = PostgreSQL::Test::Cluster->new('CIC_test');
+$node->init;
+$node->append_conf('postgresql.conf',
+ 'lock_timeout = ' . (1000 * $PostgreSQL::Test::Utils::timeout_default));
+$node->start;
+$node->safe_psql('postgres', q(CREATE UNLOGGED TABLE tbl(i int primary key, updated_at timestamp)));
+$node->safe_psql('postgres', q(CREATE UNIQUE INDEX tbl_pkey_2 ON tbl (i)));
+
+$node->pgbench(
+ '--no-vacuum --client=100 -j 2 --transactions=1000',
+ 0,
+ [qr{actually processed}],
+ [qr{^$}],
+ 'concurrent INSERTs, UPDATES and CIC/DIC',
+ {
+ '01_updates' => q(
+ INSERT INTO tbl VALUES(13,now()) ON CONFLICT(i) DO UPDATE SET updated_at = now();
+ ),
+ '02_reindex' => q(
+ SELECT pg_try_advisory_lock(42)::integer AS gotlock \gset
+ \if :gotlock
+ DROP INDEX CONCURRENTLY tbl_pkey_2;
+ CREATE UNIQUE INDEX CONCURRENTLY tbl_pkey_2 ON tbl (i);
+ SELECT pg_advisory_unlock(42);
+ \endif
+ ),
+ });
+$node->stop;
+done_testing();
signature.asc
Description: PGP signature
