Re: REINDEX backend filtering

Hi Mark,

On Sun, Mar 14, 2021 at 05:01:20PM -0700, Mark Dilger wrote:
>
> > On Mar 14, 2021, at 12:10 AM, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> I'm coming to this patch quite late, perhaps too late to change design decision in time for version 14.

Thanks for lookint at it!

> + if (outdated && PQserverVersion(conn) < 140000)
> + {
> + PQfinish(conn);
> + pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
> + "outdated", "14");
> + exit(1);
> + }
>
> If detection of outdated indexes were performed entirely in the frontend (reindexdb) rather than in the backend (reindex command), would reindexdb be able to connect to older servers? Looking quickly that the catalogs, it appears pg_index, pg_depend, pg_collation and a call to the SQL function pg_collation_actual_version() compared against pg_depend.refobjversion would be enough to construct a list of indexes in need of reindexing. Am I missing something here?

There won't be any need to connect on older servers if the patch is committed
in this commitfest as refobjversion was also added in pg14.

> I understand that wouldn't help somebody wanting to reindex from psql. Is that the whole reason you went a different direction with this feature?

This was already discussed with Magnus and Michael. The main reason for that
are:

- no need for a whole new infrastructure to be able to process a list of
indexes in parallel which would be required if getting the list of indexes in
the client

- if done in the backend, then the ability is immediately available for all
user scripts, compared to the burden of writing the needed query (with the
usual caveats like quoting, qualifying all objects if the search_path isn't
safe and such) and looping though all the results.

> + printf(_(" --outdated only process indexes having outdated depencies\n"));
>
> typo.
>
> + bool outdated; /* depends on at least on deprected collation? */
>
> typo.

Thanks! I'll fix those.