Re: Allowing REINDEX to have an optional name

On Tue, 10 May 2022 at 14:47, Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Tue, May 10, 2022 at 2:43 PM Simon Riggs
> <simon.riggs@enterprisedb.com> wrote:
> >
> > A minor issue, and patch.
> >
> > REINDEX DATABASE currently requires you to write REINDEX DATABASE
> > dbname, which makes this a little less usable than we might like.
> >
> > REINDEX on the catalog can cause deadlocks, which also makes REINDEX
> > DATABASE not much use in practice, and is the reason there is no test
> > for REINDEX DATABASE. Another reason why it is a little less usable
> > than we might like.
> >
> > Seems we should do something about these historic issues in the name
> > of product usability.
> >
> > Attached patch allows new syntax for REINDEX DATABASE, without needing
> > to specify dbname. That version of the command skips catalog tables,
> > as a way of avoiding the known deadlocks. Patch also adds a test.
> >
>
> From the patch it looks like with the patch applied running REINDEX
> DATABASE is equivalent to running REINDEX DATABASE <current database>
> except reindexing the shared catalogs. Is that correct?

Yes

> Though the patch adds following change
> +      Indexes on shared system catalogs are also processed, unless the
> +      database name is omitted, in which case system catalog indexes
> are skipped.
>
> the syntax looks unintuitive.
>
> I think REINDEX DATABASE reindexing the current database is a good
> usability improvement in itself. But skipping the shared catalogs
> needs an explicity syntax. Not sure how feasible it is but something
> like REINDEX DATABASE skip SHARED/SYSTEM.

There are two commands:

REINDEX DATABASE does every except system catalogs
REINDEX SYSTEM does system catalogs only

So taken together, the two commands seem intuitive to me.

It is designed like this because it is dangerous to REINDEX the system
catalogs because of potential deadlocks, so we want a way to avoid
that problem.