Re: REINDEX INDEX results in a crash for an index of pg_class since 9.6

On 2019-05-06 00:00:04 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On May 5, 2019 8:56:58 PM PDT, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> On this coast, "tonight" is running into "tomorrow" ... you planning
> >> to do that soon?
>
> > I'd planned to finish cooking and eating, and then doing it. Seems like that'd be plenty early?
>
> Sure, dinner can take priority.

And pushed.

I've not done so for 12. For one, because there's no imminent release,
and there's plenty reindexing related changes in 12. But also because I
have two vague ideas that might allow us to keep the test in the regular
schedule.

1) Is there a way that we could just use the type of logic we use for
CREATE INDEX CONCURRENTLY to force waiting for previously started
sessions, before doing the REINDEXing of pg_class et al?

I think it'd work to just add a CREATE INDEX CONCURRENTLY in a
transaction, after taking an exclusive lock on pg_class - but I
suspect that'd be just as deadlock prone, just for different reasons?

2) Couldn't we just add a simple loop in plpgsql that checks that the
previous session ended? A simple DO loop around SELECT pid FROM
pg_stat_activity WHERE datname = current_database() AND pid <>
pg_backend_pid(); doesn't sound like it'd be too complicated? That
wouldn't work in older releases, because we e.g. wouldn't see
autoanalyze anywhere conveniently.

I'm afraid there'd still be the issue that an autoanalyze could spin
up concurrently? And we can't just prevent that by locking pg_class,
because we'd otherwise just have the same deadlock?

I for sure thought I earlier had an idea that'd actually work. But
either I've lost it, or it didn't actually work. But perhaps somebody
else can come up with something based on the above strawman ideas?

Greetings,

Andres Freund