Re: pg_upgrade (12->14) fails on aggregate

On Fri, Jun 24, 2022 at 11:43:18PM +0500, Andrey Borodin wrote:
> > On 23 Jun 2022, at 04:58, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
> >
> > On Fri, Jun 17, 2022 at 10:14:13AM -0400, Tom Lane wrote:
> >> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >>> On Thu, Jun 16, 2022 at 10:01 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
> >>>> To me, oid>=16384 seems more hard-wired than namespace!='pg_catalog'.
> >>
> >>> Extensions can be installed into pg_catalog, but they can't get
> >>> low-numbered OIDs.
> >>
> >> Exactly. (To be clear, I had in mind writing something involving
> >> FirstNormalObjectId, not that you should put literal "16384" in the
> >> code.)
> >
> > Actually, 16384 is already used in two other places in check.c, so ...
>
> Yes, but it's a third copy of the comment ("* The query below hardcodes FirstNormalObjectId as 16384 rather than") across the file.
>
> Also, we can return slightly more information about found objects. For example, operator will look like "operator: ||". At least we can get nspname and oid. And, maybe return type for aggregator and leftarg\rightarg types for operator?

But what I wrote already shows what you want.

In database: postgres
aggregate: public.array_accum(anyelement)
operator: public(dot)!(at)#(anyarray,anyelement)

In my testing, this works great - it shows what you need to put in your DROP
command. If you try it and still wanted the OID, I'll add it for consistency
with check_for_user_defined_{encoding_conversions,postfix_ops}

> BTW comment /* Before v11, used proisagg=true, and afterwards uses prokind='a' */ seems interesting, but irrelevant. We join with pg_aggregate anyway.

Yes, that's why the query doesn't need to include that.

Something is broken in my old clusters and I can't test all the upgrades right
now, but this is my latest.