| From: | Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
|---|---|
| To: | Michael Paquier <michael(at)paquier(dot)xyz> |
| Cc: | shihao zhong <zhong950419(at)gmail(dot)com>, Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)com>, Postgres hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: Index drops during phase 1 of REINDEX CONCURRENTLY |
| Date: | 2026-11-16 04:04:32 |
| Message-ID: | DD6D3C3B-0BB6-46B8-9F1B-311A73F0A243@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
> On Nov 15, 2026, at 14:18, Michael Paquier <michael(at)paquier(dot)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/
| Attachment | Content-Type | Size |
|---|---|---|
| nocfbot_open_index_first.diff | application/octet-stream | 3.4 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Zhijie Hou | 2026-11-16 04:20:31 | Re: Assertion failure in update_local_synced_slot(), slotsync.c:364 |
| Previous Message | Jeevan Chalke | 2026-11-16 03:34:11 | Re: PL/pgSQL: apply the too_many_rows extra check to EXECUTE ... INTO |