1

I am trying to port old PostgreSQL v9.5 database (or family of databases) to PostgreSQL v14.3, with pg_upgrade utility.

After many hassles with old v9.5 binaries, source files (had to be recompiled to be runnable in the much newer OS and libraries, fedora 24->fedora 36), adding several extensions from postgresql-contrib and from GIT; I finally got pg_upgrade --check completed saying the databases are compatible.

postgres$ postgresql-setup --initdb
postgres$ pg_upgrade --check -b postgresql-9.5.7/bin -B /usr/bin  -d data.old -D data 
    Performing Consistency Checks
    -----------------------------
    Checking cluster versions                                   ok
    Checking database user is the install user                  ok
    Checking database connection settings                       ok
    Checking for prepared transactions                          ok
    Checking for system-defined composite types in user tables  ok
    Checking for reg* data types in user tables                 ok
    Checking for contrib/isn with bigint-passing mismatch       ok
    Checking for user-defined encoding conversions              ok
    Checking for user-defined postfix operators                 ok
    Checking for tables WITH OIDS                               ok
    Checking for invalid "sql_identifier" user columns          ok
    Checking for invalid "unknown" user columns                 ok
    Checking for hash indexes                                   ok
    Checking for roles starting with "pg_"                      ok
    Checking for presence of required libraries                 ok
    Checking database user is the install user                  ok
    Checking for prepared transactions                          ok
    Checking for new cluster tablespace directories             ok
    
    *Clusters are compatible*

But then running pg_upgrade without --check it stops here:

$ tail -12 pg_upgrade_dump_308771.log
pg_restore: creating AGGREGATE "foobardb.ads("anyelement")"
pg_restore: while PROCESSING TOC:

    pg_restore: from TOC entry 1800; 1255 514033 AGGREGATE ads("anyelement") dbuser
    pg_restore: error: could not execute query: ERROR:  function array_append(anyarray, anyelement) does not exist
    Command was: CREATE AGGREGATE "foobar"."ads"("anyelement") (
        SFUNC = "array_append",
        STYPE = "anyarray",
        INITCOND = '{}',
        FINALFUNC = "foobardb"."array_sort_unique"
    );

The aggregate function file which has created the aggregate: ads.sql, which have function array_sort_unique(ANYARRAY), which takes any array, finds unique values in it and orders them. And ads(ANYELEMENT) aggregate function, which can be used to aggregate items in GROUP BY queries and returning an array of unique items ordered.

-- Array_agg + unique/Distinct 'sort'

CREATE OR REPLACE FUNCTION array_sort_unique (ANYARRAY) RETURNS ANYARRAY
LANGUAGE SQL
AS $body$
  SELECT ARRAY(
    SELECT DISTINCT $1[s.i]
    FROM generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY 1
  );
$body$;

--https://www.postgresql.org/docs/9.5/xaggr.html
CREATE aggregate ads (ANYELEMENT) --RETURNS ANYARRAY
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}',
    finalfunc = array_sort_unique
)

pg_upgrade seems to claim, there is no array_append(anyarray, anyelement) function, but there is: PGSQL: array_append(anyarray, anyelement)

And it was working without problems in PGSQL v9.5

Any idea, why there is an error and how to fix it? Why PGSQL v9.5 is happy, but porting to PGSQL 14.3 fails?

Edit: Is it because in newer PGSQL array_append(anyarray, anyelement) is now array_append(anycompatiblearray, anycompatible)? I guess.

PGSQL type system - polymorphic types

How to tell pg_upgrade to handle with that? Or how to tackle that problem?

1
  • 3
    Unfortunately this is a limitation (or bug if you want) in Postgres 14. The only options you have is to use pg_dump/pg_restore or drop the aggregate, then use pg_upgrade, then re-create the aggregate. This is mentioned in the release notes: postgresql.org/docs/release/14.0/#id-1.11.6.9.4 Commented Aug 10, 2022 at 9:36

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.