> On Nov 15, 2026, at 14:18, Michael Paquier <[email protected]> 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/




Attachment: nocfbot_open_index_first.diff
Description: Binary data

Reply via email to