Thread: 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



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



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 :(