From: Justin Pryzby <pryzby@telsasoft.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Petr Vejsada <pve@paymorrow.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: pg_upgrade (12->14) fails on aggregate
Date: Tue, 14 Jun 2022 18:09:49 -0500
Message-ID: <20220614230949.GX29853@telsasoft.com> (raw)
In-Reply-To: <CAKFQuwbUUwkO0ffRY4+z4Hs9-jRjAUwGHZrDKyHhRudWVhtgkw@mail.gmail.com>
References: <3383880.QJadu78ljV@vejsadalnx>
<CAKFQuwbUUwkO0ffRY4+z4Hs9-jRjAUwGHZrDKyHhRudWVhtgkw@mail.gmail.com>
On Wed, May 04, 2022 at 07:34:15AM -0700, David G. Johnston wrote:
> On Wed, May 4, 2022 at 7:29 AM Petr Vejsada <pve@paymorrow.com> wrote:
> > We solved it (in our case) dropping the aggregate before upgrade and
> > re-create in using new syntax in V14:
> >
> > but pg_upgrade shouldn't fail on this.
> >
> > I hope it can help to improve pg_upgrade process.
>
> The release notes say explicitly that one needs to drop and recreate the
> affected functions. Thus, we know about the issue and to date our best
> solution is to have the user do exactly what you did (i.e., it is not
> something pg_upgrade is going to do for you). If you have an alternative
> solution to suggest that would help.
>
> https://www.postgresql.org/docs/current/release-14.html : the first
> compatibility note
David is right that this is documented as a compatibility issue.
But Petr has a point - pg_upgrade should aspire to catch errors in --check,
rather than starting and then leaving a mess behind for the user to clean up
(remove existing dir, rerun initdb, start old cluster, having first moved the
dir back into place if you moved it out of the way as I do). This can take
extra minutes, and exacerbates any other problem one encounters.
$ ./tmp_install/usr/local/pgsql/bin/pg_upgrade -d pg95.dat -D pg15.dat -b /usr/lib/postgresql/9.5/bin
...
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
postgres
*failure*
Consult the last few lines of "pg15.dat/pg_upgrade_output.d/20220610T104419.303/log/pg_upgrade_dump_12455.log" for
the probable cause of the failure.
pg_restore: error: could not execute query: ERROR: function array_append(anyarray, anyelement) does not exist
Command was: CREATE AGGREGATE "public"."array_accum"("anyelement") (
SFUNC = "array_append",
STYPE = "anyarray",
INITCOND = '{}'
);
This patch catches the issue; the query needs to be reviewed.
SELECT pn.nspname, p.proname FROM pg_proc p
JOIN pg_aggregate a ON a.aggfnoid=p.oid
JOIN pg_proc q ON q.oid=a.aggtransfn
JOIN pg_namespace pn ON pn.oid=p.pronamespace
JOIN pg_namespace qn ON qn.oid=q.pronamespace
WHERE pn.nspname != 'pg_catalog' AND qn.nspname = 'pg_catalog'
AND 'anyelement'::regtype = ANY(q.proargtypes)
AND q.proname IN ('array_append', 'array_prepend', 'array_cat', 'array_position', 'array_positions', 'array_remove', 'array_replace', 'width_bucket');
Attachments:
[text/x-diff] 0001-WIP-pg_upgrade-check-detect-aggregates-for-pre-pg14.patch (0B, ../20220614230949.GX29853@telsasoft.com/2-0001-WIP-pg_upgrade-check-detect-aggregates-for-pre-pg14.patch)
download
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-bugs@postgresql.org
Cc: pryzby@telsasoft.com, david.g.johnston@gmail.com, pve@paymorrow.com, pgsql-hackers@lists.postgresql.org
Subject: Re: pg_upgrade (12->14) fails on aggregate
In-Reply-To: <20220614230949.GX29853@telsasoft.com>
* 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