On Tue, May 10, 2022 at 7:30 PM Simon Riggs <simon.ri...@enterprisedb.com> wrote:
> On Tue, 10 May 2022 at 14:47, Ashutosh Bapat > <ashutosh.bapat....@gmail.com> wrote: > > > > On Tue, May 10, 2022 at 2:43 PM Simon Riggs > > <simon.ri...@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 > IIUC REINDEX DATABASE <database name> does system catalogs as well REINDEX DATABASE does everything except system catalogs That's confusing and unintuitive. Not providing the database name leads to ignoring system catalogs. I won't expect that from this syntax. > 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. > It's more clear if we add SKIP SYSTEM CATALOGS or some such explicit syntax. -- Best Wishes, Ashutosh
