Thread: PG 14 - can't upgrade from a database using an aggregate with anyelement and anyarray
PG 14 - can't upgrade from a database using an aggregate with anyelement and anyarray
From
Thomas Kellerer
Date:
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 itused 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
Re: PG 14 - can't upgrade from a database using an aggregate with anyelement and anyarray
From
Tom Lane
Date:
Thomas Kellerer <> 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) whileit used to be (anyarray, anyelement). Yeah, you'll probably need to drop that aggregate and then recreate it after upgrading. regards, tom lane
Re: PG 14 - can't upgrade from a database using an aggregate with anyelement and anyarray
From
Thomas Kellerer
Date:
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 :(