Re: pg_upgrade does not upgrade pg_stat_statements properly

On Thu, 29 Jul 2021 at 22:03, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:

> On Thu, Jul 29, 2021 at 02:14:56PM -0400, Jan Wieck wrote:
> >
> > I presume that pg_upgrade on a database with that extension installed
> would
> > silently succeed and have the pg_catalog as well as public (or wherever)
> > version of that function present.
>
> I'll have to run a pg_upgrade with it to be 100% sure, but given that this
> is a
> plpgsql function and since the created function is part of the extension
> dependencies (and looking at pg_dump source code for binary-upgrade mode),
> I'm
> almost certain that the upgraded cluster would have the pg96- version of
> the
> function even if upgrading to pg9.6+.
>
> Note that in that case the extension would appear to work normally, but the
> only way to simulate missing_ok = true is to add a BEGIN/EXCEPTION block.
>
> Since this wrapper function is extensively used, it seems quite possible to
> lead to overflowing the snapshot subxip array, as the extension basically
> runs
> every x minutes many functions in a single trannsaction to retrieve many
> performance metrics. This can ruin the performance.
>
> This was an acceptable trade off for people still using pg96- in 2021, but
> would be silly to have on more recent versions.
>
> Unfortunately I don't see any easy way to avoid that, as there isn't any
> guarantee that a new version will be available after the upgrade. AFAICT
> the
> only way to ensure that the correct version of the function is present
> from an
> extension point of view would be to add a dedicated function to overwrite
> any
> object that depends on the servers version and document the need to call
> that
> after a pg_upgrade.
>

What would happen if subsequent to the upgrade "ALTER EXTENSION UPGRADE"
was executed ?