Re: Allowing REINDEX to have an optional name

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?

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.

-- 
Best Wishes,
Ashutosh Bapat