On Wed, 1 Jul 2026 at 10:20, Kirill Reshke <[email protected]> wrote: > > Hi! > > On Tue, 30 Jun 2026 at 18:23, Álvaro Herrera <[email protected]> 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
