Обсуждение: 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
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

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