Re: Allowing REINDEX to have an optional name

From: Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
To: Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>
Subject: Re: Allowing REINDEX to have an optional name
Date: 2022-05-10 13:47:35
Message-ID: CAExHW5vXX-u3d3F3fFmfe__abXV0b=jLrFOnA+Kf8tYHaoJhyQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, May 10, 2022 at 2:43 PM Simon Riggs
<simon(dot)riggs(at)enterprisedb(dot)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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jonathan S. Katz 2022-05-10 13:51:38 Re: JSON constructors and window functions
Previous Message Fabien COELHO 2022-05-10 13:42:41 Re: psql now shows zero elapsed time after an error