Re: DROP INVALID INDEXES command

> On 1 Jul 2026, at 17:44, Roman Khapov <rkhapov(at)yandex-team(dot)ru> wrote:
>
> <v2-0001-Add-DROP-INVALID-INDEXES-ON-TABLE-command.patch>
>

Hi Roman,

Thanks for working on this - the pain is real. In our case the invalid
indexes are actually left by pg_repack: they are pre-repack indexes
whose ctids are only valid against the old heap, yet we keep inserting
into them and it looks like they can even be re-validated while those
ctids point to nowhere. That is really a pg_repack problem, but it is
what forces us to drop invalid indexes - and honestly, in that scenario
we cannot avoid an external script anyway.

So cleanup is worth having. But I think Alvaro is pointing at the right
thing: the proper fix is a mechanism that removes half-created files and
objects when a command fails or a backend crashes (the undo direction in
[0]). That is out of scope here, but it is the frame I would keep - it
would make any manual "drop the leftovers" tool unnecessary.

On the interface: a function (pg_drop_invalid_indexes(...)) is the least
intrusive surface, and I would lean that way. Its one real limit is the
one you noted - a function is a single transaction, so no DROP INDEX
CONCURRENTLY, and on a hot table you are back to an AccessExclusiveLock.
New DDL looks to me like heavy, permanent grammar to prop up something we
would rather solve systemically, so I am reluctant. The only thing that
would justify a command is a hard need for non-blocking removal, and then
I would model it on REINDEX rather than invent a statement.

One detail that is the crux, not a nice-to-have: an index being built by
a live CIC is also indisvalid=false and indistinguishable from a leftover
except by the lock. So "skip what you cannot lock" must be the default,
not an opt-in SKIP LOCKED - a default that takes AEL and deadlocks (as in
your example) is wrong. Plus a dry-run and a report of what was dropped.

So my suggestion, Roman: rather than the crutch, consider aiming at the
systemic side - cleanup of not-fully-created files and objects on
failure. It is harder, but it kills the class of problem instead of one
symptom, and I am happy to help think it through. If we keep a manual
tool meanwhile, I would keep it a plain function.

Thank you!

Best regards, Andrey Borodin.

[0] https://www.postgr.es/m/CAEepm%3D0ULqYgM2aFeOnrx6YrtBg3xUdxALoyCG%2BXpssKqmezug%40mail.gmail.com