From: Julien Rouhaud <rjuju123@gmail.com>
To: Jan Wieck <jan@wi3ck.info>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_upgrade does not upgrade pg_stat_statements properly
Date: Fri, 30 Jul 2021 10:02:57 +0800
Message-ID: <20210730020257.jpvunvworyqsaz43@nol> (raw)
In-Reply-To: <c23c3355-e7a3-4fbe-2521-32d5944b48b3@wi3ck.info>
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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: rjuju123@gmail.com, jan@wi3ck.info, bruce@momjian.us, pgsql-hackers@lists.postgresql.org
Subject: Re: pg_upgrade does not upgrade pg_stat_statements properly
In-Reply-To: <20210730020257.jpvunvworyqsaz43@nol>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox