Date: Fri, 30 Jul 2021 10:02:57 +0800
From: Julien Rouhaud <rjuju123@gmail.com>
To: Jan Wieck <jan@wi3ck.info>
Cc: Bruce Momjian <bruce@momjian.us>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_upgrade does not upgrade pg_stat_statements properly
Message-ID: <20210730020257.jpvunvworyqsaz43@nol>
References: 
 <CAKFQuwZhCVAhsjBX7vnBxAkK1XvzHci=J_-ibZyLpNtrt2fMcg@mail.gmail.com>
 <20210729015236.GD9600@momjian.us>
 <CAKFQuwaZ_OQw4f_x3RM2hN5_XnM6NT2xW7sT-6a5R5=Qz+7Udw@mail.gmail.com>
 <20210729145640.GE9600@momjian.us>
 <CADK3HHJXMMtGBhAgDw7NhqUmzSN78Wu5Us9VoXzS2y8mZH0Lyw@mail.gmail.com>
 <20210729151017.GG9600@momjian.us>
 <c4fc0792-1736-63e2-2447-ee2ed6e96b5b@wi3ck.info>
 <20210729161432.GN9600@momjian.us>
 <CAOBaU_ZRhXZN9qfWfyO+8pSLX6McFVwLYoDcsMtpvhd0pdiz3g@mail.gmail.com>
 <c23c3355-e7a3-4fbe-2521-32d5944b48b3@wi3ck.info>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <c23c3355-e7a3-4fbe-2521-32d5944b48b3@wi3ck.info>
Archived-At: 
 <https://www.postgresql.org/message-id/20210730020257.jpvunvworyqsaz43%40nol>
Precedence: bulk

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.