Re: DROP INVALID INDEXES command

On Wed, 1 Jul 2026 at 10:20, Kirill Reshke <reshkekirill(at)gmail(dot)com> wrote:
>
> Hi!
>
> On Tue, 30 Jun 2026 at 18:23, Álvaro Herrera <alvherre(at)kurilemu(dot)de> wrote:
> >
> > Hello,
> >
> > On the other hand, I wouldn't be opposed to having the feature you want,
> > using a straight function as user interface (SELECT
> > pg_drop_invalid_indexes()).
>
> straight function as user interface is good also. I think separate
> syntax for DROP INVALID INDEXES is handy for psql session <tab>
> completions.
> But I see that syntax extensions are much less preferable.
>
> >
> > Are you sure this works reasonably when invoked concurrently with CREATE
> > INDEX CONCURRENTLY or other concurrent DDL commands? I think the patch
> > should include some tests. (Probably the behavior we want is that the
> > code simply ignores any indexes it cannot obtain an AEL on.)
> >
>
> Yep, it doesn't apparently, we know about this issue, but did not yet
> design a solution here.
>
> ```
> reshke=# reindex index concurrently z_pkey ;
> ERROR: deadlock detected
> DETAIL: Process 139744 waits for ShareLock on virtual transaction
> 1/30; blocked by process 139581.
> Process 139581 waits for ShareUpdateExclusiveLock on relation 16388 of
> database 16384; blocked by process 139744.
> HINT: See server log for query details.
> Time: 3901.507 ms (00:03.902)
> reshke=#
> ```
>
> We didn't think much about what behaviour is needed here, but ignoring
> invalid indexes looks like a reasonable workaround for me.

Also, we can add a SKIP LOCKED clause to this DDL (or function
argument of pg_drop_invalid_indexes) to make this explicit.

For not SKIP LOCKED case, maybe we should behave exactly like DROP
INDEX CONCURRENTLY which is called is the same time as REINDEX/CIC?

As for test - Roman will soon post patch with basic test for this

--
Best regards,
Kirill Reshke