PG 14 - can't upgrade from a database using an aggregate with anyelement and anyarray
Hello,
this aggregate can be created without problems on PG 13 and before:
CREATE AGGREGATE array_accum(anyelement) (
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
However, that fails with PG 14beta1 because array_append's parameter are now (anycompatiblearray, anycompatible) while it used to be (anyarray, anyelement).
I guess this is a side-effect of:
Allow some array functions to operate on a mix of compatible data
types (Tom Lane)The functions are array_append() array_prepend(), array_cat(),
array_position(), array_positions(), array_remove(), array_replace(),
and width_bucket(). Previously only identical data types could be
used.
On a database with the above aggregate, pg_upgrade fails with the following error:
pg_restore: creating AGGREGATE "public.array_accum("anyelement")"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 2646; 1255 16552 AGGREGATE array_accum("anyelement") thomas
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 = '{}'
);
The problem is, that the version that works in PG 14:
CREATE AGGREGATE array_accum(anycompatible) (
SFUNC = array_append,
STYPE = anycompatiblearray,
INITCOND = '{}'
);
can't be created in PG 13 in preparation of the upgrade.
So the database can't be upgraded using pg_upgrade.
I am using PG14beta1 on Windows 10
Any ideas?
Thomas
Thomas Kellerer <shammat@gmx.net> writes:
this aggregate can be created without problems on PG 13 and before:
CREATE AGGREGATE array_accum(anyelement) (
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
However, that fails with PG 14beta1 because array_append's parameter are now (anycompatiblearray, anycompatible) while it used to be (anyarray, anyelement).
Yeah, you'll probably need to drop that aggregate and then recreate it
after upgrading.
regards, tom lane
Tom Lane schrieb am 22.05.2021 um 15:25:>> this aggregate can be created without problems on PG 13 and before:
CREATE AGGREGATE array_accum(anyelement) (
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);However, that fails with PG 14beta1 because array_append's
parameter are now (anycompatiblearray, anycompatible) while it used
to be (anyarray, anyelement).
Yeah, you'll probably need to drop that aggregate and then recreate it
after upgrading.
Hmm, that means dropping all views and functions that use that aggregate as well.
Quite cumbersome :(