Content-Type: multipart/mixed; boundary="------------ResUv04Zo2pgTjI4ENBTPpt0"
Message-ID: <3e302c96-cdd2-45ec-af84-03dbcdccde4a@gmail.com>
Date: Sun, 30 Nov 2025 19:00:01 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Issues with ON CONFLICT UPDATE and REINDEX CONCURRENTLY
To: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>,
 Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>, Noah Misch <noah@leadboat.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
References: <202511271229.zkwkjepzcean@alvherre.pgsql>
Content-Language: en-US
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <202511271229.zkwkjepzcean@alvherre.pgsql>
Archived-At: 
 <https://www.postgresql.org/message-id/3e302c96-cdd2-45ec-af84-03dbcdccde4a%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------ResUv04Zo2pgTjI4ENBTPpt0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hello Alvaro,

27.11.2025 14:32, Álvaro Herrera wrote:
> Thanks for reviewing.  I have pushed it now.  Looking at the next one.

I've noticed two failures from skink you could find interesting: [1], [2].
The difference from [2]:
ok 3         - syscache-update-pruned                  94198 ms
not ok 4     - index-concurrently-upsert               14008 ms
ok 5         - reindex-concurrently-upsert             14379 ms

--- /home/bf/bf-build/skink-master/HEAD/pgsql/src/test/modules/injection_points/expected/index-concurrently-upsert.out 
2025-11-27 13:38:19.513528475 +0100
+++ 
/home/bf/bf-build/skink-master/HEAD/pgsql.build/testrun/injection_points/isolation/results/index-concurrently-upsert.out 
2025-11-30 00:10:01.697938769 +0100
@@ -107,6 +107,7 @@
  (1 row)

  s1: NOTICE:  notice triggered for injection point pre-invalidate-catalog-snapshot-end
+s1: NOTICE:  notice triggered for injection point pre-invalidate-catalog-snapshot-end
  step s1_start_upsert: <... completed>
  step s2_start_upsert: <... completed>
  step s3_start_create_index: <... completed>

I've managed to reproduce something similar to this diff when running
multiple test instances under Valgrind with parallel. With the attached
patch applied:
for i in {1..40}; do cp -r src/test/modules/injection_points/ src/test/modules/injection_points_$i/; sed -i.bak 
"s|src/test/modules/injection_points|src/test/modules/injection_points_$i|" 
src/test/modules/injection_points_$i/Makefile; done
make -s check -C src/test/modules/injection_points

and/tmp/extra.config containing:
log_min_messages = INFO
backtrace_functions = 'injection_notice'

for i in {1..10}; do np=5; echo "ITERATION $i"; parallel -j40 --linebuffer --tag PROVE_TESTS="t/099*" NO_TEMP_INSTALL=1 
TEMP_CONFIG=/tmp/extra.config make -s check -C src/test/modules/injection_points_{}  ::: `seq $np` || break; done

gives me:
ITERATION 1
...
4       #   Failed test 'regression tests pass'
4       #   at t/099_isolation_regress.pl line 52.
4       #          got: '256'
4       #     expected: '0'

src/test/modules/injection_points_4/tmp_check/log/regress_log_099_isolation_regress contains:
...
ok 11        - index-concurrently-upsert                5282 ms
not ok 12    - index-concurrently-upsert                6347 ms
ok 13        - index-concurrently-upsert                5723 ms
...
--- .../src/test/modules/injection_points_4/expected/index-concurrently-upsert.out 2025-11-30 14:24:29.385133831 +0200
+++ .../src/test/modules/injection_points_4/tmp_check/results/index-concurrently-upsert.out 2025-11-30 
16:22:29.168920744 +0200
@@ -78,6 +78,7 @@

  (1 row)

+s1: NOTICE:  notice triggered for injection point pre-invalidate-catalog-snapshot-end
  step s4_wakeup_s2:
     SELECT injection_points_detach('exec-insert-before-insert-speculative');
     SELECT injection_points_wakeup('exec-insert-before-insert-speculative');
=== EOF ===

I can see in the following stack trace for this extra notice:
2025-11-30 16:22:28.465 EET|law|isolation_regression|692c531f.bb652|NOTICE:  notice triggered for injection point 
pre-invalidate-catalog-snapshot-end
2025-11-30 16:22:28.465 EET|law|isolation_regression|692c531f.bb652|BACKTRACE:
injection_notice at injection_points.c:278:3
InjectionPointRun at injection_point.c:555:1
InvalidateCatalogSnapshot at snapmgr.c:463:3
LocalExecuteInvalidationMessage at inval.c:831:4
ReceiveSharedInvalidMessages at sinval.c:113:18
AcceptInvalidationMessages at inval.c:970:23
LockRelationOid at lmgr.c:137:3
relation_open at relation.c:58:6
table_open at table.c:44:6
SearchCatCacheMiss at catcache.c:1550:13
SearchCatCacheInternal at catcache.c:1495:9
SearchCatCache1 at catcache.c:1368:1
SearchSysCache1 at syscache.c:227:1
build_coercion_expression at parse_coerce.c:852:8
coerce_type at parse_coerce.c:433:13
coerce_to_target_type at parse_coerce.c:105:11
transformAssignedExpr at parse_target.c:580:4
transformInsertRow at analyze.c:1121:10
transformInsertStmt at analyze.c:988:14
transformStmt at analyze.c:364:13
transformOptionalSelectInto at analyze.c:317:1
transformTopLevelStmt at analyze.c:266:11
parse_analyze_fixedparams at analyze.c:134:10
pg_analyze_and_rewrite_fixedparams at postgres.c:687:10
exec_simple_query at postgres.c:1195:20
PostgresMain at postgres.c:4777:6
BackendInitialize at backend_startup.c:142:1
postmaster_child_launch at launch_backend.c:269:3
BackendStartup at postmaster.c:3598:8
ServerLoop at postmaster.c:1716:10
PostmasterMain at postmaster.c:1403:11
main at main.c:236:2

I also observed:
2025-11-30 15:33:45.746 EET|law|isolation_regression|692c47b4.675e7|NOTICE:  notice triggered for injection point 
pre-invalidate-catalog-snapshot-end
2025-11-30 15:33:45.746 EET|law|isolation_regression|692c47b4.675e7|BACKTRACE:
injection_notice at injection_points.c:278:3
InjectionPointRun at injection_point.c:555:1
InvalidateCatalogSnapshot at snapmgr.c:463:3
LocalExecuteInvalidationMessage at inval.c:831:4
ReceiveSharedInvalidMessages at sinval.c:113:18
AcceptInvalidationMessages at inval.c:970:23
LockRelationOid at lmgr.c:137:3
relation_open at relation.c:58:6
table_open at table.c:44:6
CatalogCacheInitializeCache at catcache.c:1131:13
ConditionalCatalogCacheInitializeCache at catcache.c:1092:1
SearchCatCacheInternal at catcache.c:1424:15
SearchCatCache1 at catcache.c:1368:1
SearchSysCache1 at syscache.c:227:1
check_enable_rls at rls.c:66:10
get_row_security_policies at rowsecurity.c:130:15
fireRIRrules at rewriteHandler.c:2218:3
QueryRewrite at rewriteHandler.c:4581:11
pg_rewrite_query at postgres.c:822:20
pg_analyze_and_rewrite_fixedparams at postgres.c:696:19
exec_simple_query at postgres.c:1195:20
PostgresMain at postgres.c:4777:6
BackendInitialize at backend_startup.c:142:1
postmaster_child_launch at launch_backend.c:269:3
BackendStartup at postmaster.c:3598:8
ServerLoop at postmaster.c:1716:10
PostmasterMain at postmaster.c:1403:11
main at main.c:236:2

Could you please look if this can be fixed?

[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2025-11-25%2021%3A55%3A00
[2] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2025-11-29%2021%3A51%3A13

Best regards,
Alexander
--------------ResUv04Zo2pgTjI4ENBTPpt0
Content-Type: text/x-patch; charset=UTF-8;
 name="injection_points-tests-as-TAP.patch"
Content-Disposition: attachment;
 filename="injection_points-tests-as-TAP.patch"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy90ZXN0L21vZHVsZXMvaW5qZWN0aW9uX3BvaW50cy9NYWtlZmls
ZSBiL3NyYy90ZXN0L21vZHVsZXMvaW5qZWN0aW9uX3BvaW50cy9NYWtlZmlsZQppbmRleCA3
YjNjMGM0YjcxNi4uNDY1NjY5MzdkODEgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L21vZHVsZXMv
aW5qZWN0aW9uX3BvaW50cy9NYWtlZmlsZQorKysgYi9zcmMvdGVzdC9tb2R1bGVzL2luamVj
dGlvbl9wb2ludHMvTWFrZWZpbGUKQEAgLTExLDEwICsxMSwxMCBAQCBFWFRFTlNJT04gPSBp
bmplY3Rpb25fcG9pbnRzCiBEQVRBID0gaW5qZWN0aW9uX3BvaW50cy0tMS4wLnNxbAogUEdG
SUxFREVTQyA9ICJpbmplY3Rpb25fcG9pbnRzIC0gZmFjaWxpdHkgZm9yIGluamVjdGlvbiBw
b2ludHMiCiAKLVJFR1JFU1MgPSBpbmplY3Rpb25fcG9pbnRzIGhhc2hhZ2cgcmVpbmRleF9j
b25jIHZhY3V1bQorWFJFR1JFU1MgPSBpbmplY3Rpb25fcG9pbnRzIGhhc2hhZ2cgcmVpbmRl
eF9jb25jIHZhY3V1bQogUkVHUkVTU19PUFRTID0gLS1kbHBhdGg9JCh0b3BfYnVpbGRkaXIp
L3NyYy90ZXN0L3JlZ3Jlc3MKIAotSVNPTEFUSU9OID0gYmFzaWMgXAorWElTT0xBVElPTiA9
IGJhc2ljIFwKIAkgICAgaW5wbGFjZSBcCiAJICAgIHN5c2NhY2hlLXVwZGF0ZS1wcnVuZWQg
XAogCSAgICBpbmRleC1jb25jdXJyZW50bHktdXBzZXJ0IFwKQEAgLTI4LDYgKzI4LDkgQEAg
Tk9fSU5TVEFMTENIRUNLID0gMQogCiBleHBvcnQgZW5hYmxlX2luamVjdGlvbl9wb2ludHMK
IAorUkVHUkVTU19TSExJQj0kKGFic190b3BfYnVpbGRkaXIpL3NyYy90ZXN0L3JlZ3Jlc3Mv
cmVncmVzcyQoRExTVUZGSVgpCitleHBvcnQgUkVHUkVTU19TSExJQgorCiBpZmRlZiBVU0Vf
UEdYUwogUEdfQ09ORklHID0gcGdfY29uZmlnCiBQR1hTIDo9ICQoc2hlbGwgJChQR19DT05G
SUcpIC0tcGd4cykKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L21vZHVsZXMvaW5qZWN0aW9uX3Bv
aW50cy9pc29sYXRpb25fc2NoZWR1bGUgYi9zcmMvdGVzdC9tb2R1bGVzL2luamVjdGlvbl9w
b2ludHMvaXNvbGF0aW9uX3NjaGVkdWxlCm5ldyBmaWxlIG1vZGUgMTAwNjQ0CmluZGV4IDAw
MDAwMDAwMDAwLi45YjcxZDA2NTMwOQotLS0gL2Rldi9udWxsCisrKyBiL3NyYy90ZXN0L21v
ZHVsZXMvaW5qZWN0aW9uX3BvaW50cy9pc29sYXRpb25fc2NoZWR1bGUKQEAgLTAsMCArMSwy
MyBAQAordGVzdDogYmFzaWMKK3Rlc3Q6IGlucGxhY2UKK3Rlc3Q6IHN5c2NhY2hlLXVwZGF0
ZS1wcnVuZWQKK3Rlc3Q6IGluZGV4LWNvbmN1cnJlbnRseS11cHNlcnQKK3Rlc3Q6IGluZGV4
LWNvbmN1cnJlbnRseS11cHNlcnQKK3Rlc3Q6IGluZGV4LWNvbmN1cnJlbnRseS11cHNlcnQK
K3Rlc3Q6IGluZGV4LWNvbmN1cnJlbnRseS11cHNlcnQKK3Rlc3Q6IGluZGV4LWNvbmN1cnJl
bnRseS11cHNlcnQKK3Rlc3Q6IGluZGV4LWNvbmN1cnJlbnRseS11cHNlcnQKK3Rlc3Q6IGlu
ZGV4LWNvbmN1cnJlbnRseS11cHNlcnQKK3Rlc3Q6IGluZGV4LWNvbmN1cnJlbnRseS11cHNl
cnQKK3Rlc3Q6IGluZGV4LWNvbmN1cnJlbnRseS11cHNlcnQKK3Rlc3Q6IGluZGV4LWNvbmN1
cnJlbnRseS11cHNlcnQKK3Rlc3Q6IHJlaW5kZXgtY29uY3VycmVudGx5LXVwc2VydAordGVz
dDogcmVpbmRleC1jb25jdXJyZW50bHktdXBzZXJ0Cit0ZXN0OiByZWluZGV4LWNvbmN1cnJl
bnRseS11cHNlcnQKK3Rlc3Q6IHJlaW5kZXgtY29uY3VycmVudGx5LXVwc2VydAordGVzdDog
cmVpbmRleC1jb25jdXJyZW50bHktdXBzZXJ0Cit0ZXN0OiByZWluZGV4LWNvbmN1cnJlbnRs
eS11cHNlcnQKK3Rlc3Q6IHJlaW5kZXgtY29uY3VycmVudGx5LXVwc2VydAordGVzdDogcmVp
bmRleC1jb25jdXJyZW50bHktdXBzZXJ0Cit0ZXN0OiByZWluZGV4LWNvbmN1cnJlbnRseS11
cHNlcnQKK3Rlc3Q6IHJlaW5kZXgtY29uY3VycmVudGx5LXVwc2VydApkaWZmIC0tZ2l0IGEv
c3JjL3Rlc3QvbW9kdWxlcy9pbmplY3Rpb25fcG9pbnRzL3QvMDk5X2lzb2xhdGlvbl9yZWdy
ZXNzLnBsIGIvc3JjL3Rlc3QvbW9kdWxlcy9pbmplY3Rpb25fcG9pbnRzL3QvMDk5X2lzb2xh
dGlvbl9yZWdyZXNzLnBsCm5ldyBmaWxlIG1vZGUgMTAwNjQ0CmluZGV4IDAwMDAwMDAwMDAw
Li4wNDc1MzBjM2Q2YQotLS0gL2Rldi9udWxsCisrKyBiL3NyYy90ZXN0L21vZHVsZXMvaW5q
ZWN0aW9uX3BvaW50cy90LzA5OV9pc29sYXRpb25fcmVncmVzcy5wbApAQCAtMCwwICsxLDU1
IEBACisKKyMgQ29weXJpZ2h0IChjKSAyMDI0LCBQb3N0Z3JlU1FMIEdsb2JhbCBEZXZlbG9w
bWVudCBHcm91cAorCisjIFJ1biB0aGUgc3RhbmRhcmQgcmVncmVzc2lvbiB0ZXN0cyB3aXRo
IHN0cmVhbWluZyByZXBsaWNhdGlvbgordXNlIHN0cmljdDsKK3VzZSB3YXJuaW5ncyBGQVRB
TCA9PiAnYWxsJzsKK3VzZSBQb3N0Z3JlU1FMOjpUZXN0OjpDbHVzdGVyOwordXNlIFBvc3Rn
cmVTUUw6OlRlc3Q6OlV0aWxzOwordXNlIFRlc3Q6Ok1vcmU7Cit1c2UgRmlsZTo6QmFzZW5h
bWU7CisKKyMgSW5pdGlhbGl6ZSBwcmltYXJ5IG5vZGUKK215ICRub2RlX3ByaW1hcnkgPSBQ
b3N0Z3JlU1FMOjpUZXN0OjpDbHVzdGVyLT5uZXcoJ3ByaW1hcnknKTsKKyRub2RlX3ByaW1h
cnktPmluaXQoKTsKKworIyBJbmNyZWFzZSBzb21lIHNldHRpbmdzIHRoYXQgQ2x1c3Rlci0+
bmV3IG1ha2VzIHRvbyBsb3cgYnkgZGVmYXVsdC4KKyRub2RlX3ByaW1hcnktPmFkanVzdF9j
b25mKCdwb3N0Z3Jlc3FsLmNvbmYnLCAnbWF4X2Nvbm5lY3Rpb25zJywgJzI1Jyk7Ciskbm9k
ZV9wcmltYXJ5LT5hcHBlbmRfY29uZigncG9zdGdyZXNxbC5jb25mJywKKwknbWF4X3ByZXBh
cmVkX3RyYW5zYWN0aW9ucyA9IDEwJyk7CisKKyRub2RlX3ByaW1hcnktPnN0YXJ0OworCitt
eSAkZGxwYXRoID0gZGlybmFtZSgkRU5We1JFR1JFU1NfU0hMSUJ9KTsKK215ICRvdXRwdXRk
aXIgPSAkUG9zdGdyZVNRTDo6VGVzdDo6VXRpbHM6OnRtcF9jaGVjazsKKworIyBSdW4gdGhl
IHJlZ3Jlc3Npb24gdGVzdHMgYWdhaW5zdCB0aGUgcHJpbWFyeS4KK215ICRleHRyYV9vcHRz
ID0gJEVOVntFWFRSQV9SRUdSRVNTX09QVFN9IHx8ICIiOworCitteSAkcmMgPQorICBzeXN0
ZW0oICgkRU5We1BHX1JFR1JFU1N9ID1+IHMvcmVncmVzc1wvcGdfcmVncmVzcy9pc29sYXRp
b25cL3BnX2lzb2xhdGlvbl9yZWdyZXNzL3IpCisJICAuICIgJGV4dHJhX29wdHMgIgorCSAg
LiAiLS1kbHBhdGg9XCIkZGxwYXRoXCIgIgorCSAgLiAiLS1iaW5kaXI9ICIKKwkgIC4gIi0t
aG9zdD0iCisJICAuICRub2RlX3ByaW1hcnktPmhvc3QgLiAiICIKKwkgIC4gIi0tcG9ydD0i
CisJICAuICRub2RlX3ByaW1hcnktPnBvcnQgLiAiICIKKwkgIC4gIi0tc2NoZWR1bGU9Li9p
c29sYXRpb25fc2NoZWR1bGUgIgorCSAgLiAiLS1pbnB1dGRpcj0uLyAiCisJICAuICItLW91
dHB1dGRpcj1cIiRvdXRwdXRkaXJcIiIpOworaWYgKCRyYyAhPSAwKQoreworCSMgRHVtcCBv
dXQgdGhlIHJlZ3Jlc3Npb24gZGlmZnMgZmlsZSwgaWYgdGhlcmUgaXMgb25lCisJbXkgJGRp
ZmZzID0gIiRvdXRwdXRkaXIvcmVncmVzc2lvbi5kaWZmcyI7CisJaWYgKC1lICRkaWZmcykK
Kwl7CisJCXByaW50ICI9PT0gZHVtcGluZyAkZGlmZnMgPT09XG4iOworCQlwcmludCBzbHVy
cF9maWxlKCRkaWZmcyk7CisJCXByaW50ICI9PT0gRU9GID09PVxuIjsKKwl9Cit9Citpcygk
cmMsIDAsICdyZWdyZXNzaW9uIHRlc3RzIHBhc3MnKTsKKworCitkb25lX3Rlc3RpbmcoKTsK


--------------ResUv04Zo2pgTjI4ENBTPpt0--