Re: pg_upgrade (12->14) fails on aggregate

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(at)paymorrow(dot)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');