Re: Issues with ON CONFLICT UPDATE and REINDEX CONCURRENTLY

Mikhail Nikalayeu <mihailnikalayeu@gmail.com>

From: Mihail Nikalayeu <mihailnikalayeu@gmail.com>

To: Noah Misch <noah@leadboat.com>

Cc: Michael Paquier <michael@paquier.xyz>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>

Date: 2025-11-03T20:41:00Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Replace flaky CIC/RI isolation tests with a TAP test
- e1c971945d62 19 (unreleased) landed
Disable recently added CIC/RI isolation tests
- 77038d6d0b49 19 (unreleased) landed
Fix infer_arbiter_index for partitioned tables
- 81f72115cf18 19 (unreleased) landed
Stabilize tests some more
- a4a0fa0c7587 19 (unreleased) landed
Put back alternative-output expected files
- be25c7767728 19 (unreleased) landed
Remove doc and code comments about ON CONFLICT deficiencies
- 758479213d57 19 (unreleased) landed
Avoid use of NOTICE to wait for snapshot invalidation
- 5dee7a603f66 19 (unreleased) landed
Fix ON CONFLICT with REINDEX CONCURRENTLY and partitions
- 90eae926abbb 19 (unreleased) landed
Fix ON CONFLICT ON CONSTRAINT during REINDEX CONCURRENTLY
- 2bc7e886fc1b 19 (unreleased) landed
Fix new test for CATCACHE_FORCE_RELEASE builds
- 9e8fa05d3412 19 (unreleased) landed
Improve test case stability
- 417ac9c1eeeb 19 (unreleased) landed
Fix infer_arbiter_index during concurrent index operations
- bc32a12e0db2 19 (unreleased) landed
Doc: cover index CONCURRENTLY causing errors in INSERT ... ON CONFLICT.
- 438ccd33a37a 13.23 landed
- faba2595d619 14.20 landed
- e1dd1f924e19 15.15 landed
- 4c2895629f6f 16.11 landed
- 46524d519a2d 18.1 landed
- 27f714b5dad7 17.7 landed
- 8b18ed6dfbb8 19 (unreleased) landed
Fix infer_arbiter_indexes() to not assume resultRelation is 1.
- 915de706d28c 17.0 cited
Revert temporal primary keys and foreign keys
- 8aee330af55d 17.0 cited

Attachments

v11-0002-Specs-to-reproduce-the-issues-with-CREATE-INDEX-.patch (application/octet-stream) patch v11-0002
v11-0005-Modify-the-ExecInitPartitionInfo-function-to-con.patch (application/octet-stream) patch v11-0005
v11-0003-Modify-the-infer_arbiter_indexes-function-to-con.patch (application/octet-stream) patch v11-0003
v11-0006-Revert-Doc-cover-index-CONCURRENTLY-causing-erro.patch (application/octet-stream) patch v11-0006
v11-0004-Modify-the-infer_arbiter_indexes-function-to-als.patch (application/octet-stream) patch v11-0004
v11-0001-Doc-cover-index-CONCURRENTLY-causing-errors-in-I.patch (application/octet-stream) patch v11-0001

Hello!

On Mon, Nov 3, 2025 at 12:21 AM Noah Misch <noah@leadboat.com> wrote:
> Thanks.  Does "ON CONFLICT ON CONSTRAINT constraint_name" avoid the problem w/
> concurrent REINDEX CONCURRENTLY?  A search of the thread found no mention of
> "ON CONSTRAINT".  It seems safe to assume that clause would avoid problems w/
> CREATE INDEX CONCURRENTLY, but that's less certain for REINDEX.

It is also affected. There is a special
reindex_concurrently_upsert_on_constraint spec in the patch.
And even a special commit (0004) to fix it :)

But yes, it happens only in the case of REINDEX.

I removed the mention of  "ON CONSTRAINT" and added a small comment
near infer_arbiter_indexes.

Doc patch is 0001, other - specs and fixes for future.

Best regards,
Mikhail.