Date: Tue, 14 Jun 2022 18:09:49 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Petr Vejsada <pve@paymorrow.com>, pgsql-hackers@lists.postgresql.org
Subject: Re: pg_upgrade (12->14) fails on aggregate
Message-ID: <20220614230949.GX29853@telsasoft.com>
References: <3383880.QJadu78ljV@vejsadalnx>
 <CAKFQuwbUUwkO0ffRY4+z4Hs9-jRjAUwGHZrDKyHhRudWVhtgkw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="98e8jtXdkpgskNou"
Content-Disposition: inline
In-Reply-To: 
 <CAKFQuwbUUwkO0ffRY4+z4Hs9-jRjAUwGHZrDKyHhRudWVhtgkw@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20220614230949.GX29853%40telsasoft.com>
Precedence: bulk


--98e8jtXdkpgskNou
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Wed, May 04, 2022 at 07:34:15AM -0700, David G. Johnston wrote:
> On Wed, May 4, 2022 at 7:29 AM Petr Vejsada <pve@paymorrow.com> wrote:
> > We solved it (in our case) dropping the aggregate before upgrade and
> > re-create in using new syntax in V14:
> >
> > but pg_upgrade shouldn't fail on this.
> >
> > I hope it can help to improve pg_upgrade process.
>
> The release notes say explicitly that one needs to drop and recreate the
> affected functions.  Thus, we know about the issue and to date our best
> solution is to have the user do exactly what you did (i.e., it is not
> something pg_upgrade is going to do for you).  If you have an alternative
> solution to suggest that would help.
> 
> https://www.postgresql.org/docs/current/release-14.html : the first
> compatibility note

David is right that this is documented as a compatibility issue.

But Petr has a point - pg_upgrade should aspire to catch errors in --check,
rather than starting and then leaving a mess behind for the user to clean up
(remove existing dir, rerun initdb, start old cluster, having first moved the
dir back into place if you moved it out of the way as I do).  This can take
extra minutes, and exacerbates any other problem one encounters.

$ ./tmp_install/usr/local/pgsql/bin/pg_upgrade -d pg95.dat -D pg15.dat -b /usr/lib/postgresql/9.5/bin
...
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
  postgres
*failure*

Consult the last few lines of "pg15.dat/pg_upgrade_output.d/20220610T104419.303/log/pg_upgrade_dump_12455.log" for
the probable cause of the failure.

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 = '{}'
);

This patch catches the issue; the query needs to be reviewed.

      SELECT pn.nspname, p.proname FROM pg_proc p
      JOIN pg_aggregate a ON a.aggfnoid=p.oid 
      JOIN pg_proc q ON q.oid=a.aggtransfn 
      JOIN pg_namespace pn ON pn.oid=p.pronamespace 
      JOIN pg_namespace qn ON qn.oid=q.pronamespace 
      WHERE pn.nspname != 'pg_catalog' AND qn.nspname = 'pg_catalog' 
      AND 'anyelement'::regtype = ANY(q.proargtypes) 
      AND q.proname IN ('array_append', 'array_prepend', 'array_cat', 'array_position', 'array_positions', 'array_remove', 'array_replace', 'width_bucket');


--98e8jtXdkpgskNou
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="0001-WIP-pg_upgrade-check-detect-aggregates-for-pre-pg14.patch"