Re: pgsql: Fix search_path to a safe value during maintenance operations.

On Mon, Jul 03, 2023 at 11:19:14AM -0700, Nathan Bossart wrote:
> On Sun, Jul 02, 2023 at 08:57:31PM -0700, Noah Misch wrote:
> > Another dimension of compromise could be to make MAINTAIN affect fewer
> > commands in v16. Un-revert the part of commit 05e1737 affecting just the
> > commands it still affects. For example, limit MAINTAIN and the 05e1737
> > behavior change to VACUUM, ANALYZE, and REINDEX. Don't worry about VACUUM or
> > ANALYZE failing under commit 05e1737, since they would have been failing under
> > autovacuum since 2018. A problem index expression breaks both autoanalyze and
> > REINDEX, hence the inclusion of REINDEX. The already-failing argument doesn't
> > apply to CLUSTER or REFRESH MATERIALIZED VIEW, so those commands could join
> > MAINTAIN in v17.
>
> I'm open to compromise if others are, but I'm skeptical that folks will be
> okay with too much fancy footwork this late in the game.

Got it.

> Anyway, IMO your argument could extend to CLUSTER and REFRESH, too. If
> we're willing to change behavior under the assumption that autovacuum
> would've been failing since 2018, then why wouldn't we be willing to change
> it everywhere? I suppose someone could have been manually vacuuming with a
> special search_path for 5 years to avoid needing to schema-qualify their
> index expressions (and would then be surprised that CLUSTER/REFRESH no
> longer work), but limiting MAINTAIN to VACUUM, etc. would still break their
> use-case, right?

Yes, limiting MAINTAIN to VACUUM would still break a site that has used manual
VACUUM to work around associated loss of autovacuum. I'm not sympathetic to a
user who neglected to benefit from the last five years of prep time on this
issue as it affects VACUUM and ANALYZE. REFRESH runs more than index
expressions, e.g. function calls in the targetlist of the materialized view
query. Those targetlist expressions haven't been putting ERRORs in the log
during autovacuum, so REFRESH hasn't had the sort of advance warning that
VACUUM and ANALYZE got.