On Thu, 29 Jul 2021 at 22:03, Julien Rouhaud <rjuju123@gmail.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 ?
