Index drops during phase 1 of REINDEX CONCURRENTLY
Hi all,
Some of the feedback I got after f24a08ec13eb involved the fact that
indexes could also be dropped during the phase 1 of REINDEX
CONCURRENTLY when the expressions or predicates are evaluated. The
damage is limited to cache lookup failures or assertion hits during
phase 2, thanks to the protections that f24a08ec13eb has added.
Anyway, it would be nice to fix this issue. Attached is a patch
addressing the problem with a test, for a backpatch down to v15.
Thoughts or objections?
--
Michael
Hi,
On Sat, 14 Nov 2026 at 06:02, Michael Paquier <michael@paquier.xyz> wrote:
Hi all,
Some of the feedback I got after f24a08ec13eb involved the fact that
indexes could also be dropped during the phase 1 of REINDEX
CONCURRENTLY when the expressions or predicates are evaluated. The
damage is limited to cache lookup failures or assertion hits during
phase 2, thanks to the protections that f24a08ec13eb has added.
Yea, I can see both here without the fix. Your test fails with "could not
open relation with OID", and doing a REINDEX on the _ccnew index from the
function hits the ii_ReadyForInserts assert.
Anyway, it would be nice to fix this issue. Attached is a patch
addressing the problem with a test, for a backpatch down to v15.Thoughts or objections?
The patch looks good to me, some minor comment nits:
- "then close relations" in the lockrelid comment isn't true anymore, and
parentRelationIds there is heapRelationIds now?
- IIUC ReindexUnpinRelations() isn't only closing relations from
ReindexPinRelations() anymore too..
Regards,
Ayush
Hi Michael,
Thanks for the patch. I created the expression index first in the
new test, so concur_reindex_ddl_f() drops an index that the loop in
ReindexRelationConcurrently() has not reached yet. With the patch
the index_open() of the next iteration still fails with
ERROR: could not open relation with OID 16390
Would it make sense to open all the old indexes before the loop?
Thanks,
Shihao
On Sat, Nov 14, 2026 at 11:22:59PM -0500, shihao zhong wrote:
Would it make sense to open all the old indexes before the loop?
Hmm, yeah. We could as well open the indexes when these are gathered
in the initial loop as we are in the same transaction. Would you like
to write a patch to show your idea? (I'm out most of next week so
won't have much time to look at what you have, but perhaps others
will.)
--
Michael
On Nov 15, 2026, at 14:18, Michael Paquier <michael@paquier.xyz> wrote:
On Sat, Nov 14, 2026 at 11:22:59PM -0500, shihao zhong wrote:
Would it make sense to open all the old indexes before the loop?
Hmm, yeah. We could as well open the indexes when these are gathered
in the initial loop as we are in the same transaction. Would you like
to write a patch to show your idea? (I'm out most of next week so
won't have much time to look at what you have, but perhaps others
will.)
--
Michael
Following Shihao’s comment, I swapped the order of the two CREATE INDEX statements:
```
CREATE INDEX concur_reindex_ddl_ind2 ON
concur_reindex_ddl_tab ((i + concur_reindex_ddl_im(0)));
CREATE INDEX concur_reindex_ddl_ind1 ON concur_reindex_ddl_tab (i);
```
I then reproduced the same test failure that Shihao reported.
After some debugging, I think the problem is that RelationGetIndexList() returns the indexes in OID order. Creating ind2 first therefore puts it before ind1 in indexIds. The gathering loop closes the indexes while retaining their locks. Consequently, when phase 1 processes ind2, ind1 has not yet been reopened: it is locked, but not opened.
The retained lock doesn't prevent DROP INDEX in the same transaction. The following comment in index_drop() seems to explain why only locking cannot block index dropping:
```
/*
* We might still have open queries using it in our own session, which the
* above locking won't prevent, so test explicitly.
*/
CheckTableNotInUse(userIndexRelation, "DROP INDEX");
```
So, when phase 1 evaluates concur_reindex_ddl_im(0) while processing ind2, the function can drop ind1. The next iteration then attempts to open ind1, which would fail.
To prevent this, we need to open all original target indexes before the phase 1 creation loop starts. Michael seems to suggest remaining the indexes opened after the gathering loop. Since that code has several branches, I thought it might be cleaner to add a separate loop immediately before the phase 1 loop to open all indexes in indexIds.
The attached diff takes this approach and also swaps the order of the two CREATE INDEX statements in the test. The test passes with this change. Note that, the diff is based on v1.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
nocfbot_open_index_first.diffapplication/octet-stream; name=nocfbot_open_index_first.diff; x-unix-mode=0644Download+21-3
On Mon, Nov 16, 2026 at 12:04:32PM +0800, Chao Li wrote:
Following Shihao’s comment, I swapped the order of the two CREATE
INDEX statements:
```
CREATE INDEX concur_reindex_ddl_ind2 ON
concur_reindex_ddl_tab ((i + concur_reindex_ddl_im(0)));
CREATE INDEX concur_reindex_ddl_ind1 ON concur_reindex_ddl_tab (i);
```
This shows your point, but it also impacts the test by not checking
anymore what I want it to test.
The attached diff takes this approach and also swaps the order of
the two CREATE INDEX statements in the test. The test passes with
this change. Note that, the diff is based on v1.
Hmm, no, I think that you are overcomplicating things here by
introducing a second list that serves the same purpose as the "phase
1" list. It is simpler to keep the Relation references opened based
on the ones already taken in the first loop, and we are talking about
only two places.
Another redundant piece is the addition of the heap relation to the
new list until the session locks are hold. Perhaps nicer on
consistency grounds in the long-run, but not actually required so I
have kept the table_close() for it.
The original test ordering is kept. I have reused the solution where
the relations are kept opened in the initial loop, then committed the
change down to v15.
Thanks all for the reviews.
--
Michael