Re: DROP INVALID INDEXES command

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.

--
Best regards,
Kirill Reshke