pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedStruggling with 13->14 and anyarray -> anycompatiblearray
4+ messages / 2 participants
[nested] [flat]
* Struggling with 13->14 and anyarray -> anycompatiblearray
@ 2022-11-04 18:47 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Wells Oliver @ 2022-11-04 18:47 UTC (permalink / raw)
To: pgsql-admin
I've inherited a DB with a lot of legacy functions and aggregates that have
an SFUNC of array_append and an STYPE of anyarray.
Upgrading to 14 fails as these need an STYPE of anycompatiblearray.
I'm only able to discover these by failing an upgrade and seeing the log.
Is there some way I can query for these aggregates/functions and see how
many I need to drop and have available to replace after a
successful upgrade?
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Struggling with 13->14 and anyarray -> anycompatiblearray
@ 2022-11-04 19:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2022-11-04 19:12 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
Wells Oliver <wells.oliver@gmail.com> writes:
> I've inherited a DB with a lot of legacy functions and aggregates that have
> an SFUNC of array_append and an STYPE of anyarray.
> Is there some way I can query for these aggregates/functions and see how
> many I need to drop and have available to replace after a
> successful upgrade?
Something like
select aggfnoid::regprocedure from pg_aggregate where aggtransfn = 'array_append'::regproc;
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Struggling with 13->14 and anyarray -> anycompatiblearray
@ 2022-11-04 20:51 Wells Oliver <wells.oliver@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Wells Oliver @ 2022-11-04 20:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thanks. This seems to return aggregates where array_append is used with
anyarray, but I now see aggregates where e.g. array_cat is used with
anyarray. Is there some way to generally query aggregates where any array
function might be looking for anyarray and need to be dropped/re-created
with anycompatiblearray?
On Fri, Nov 4, 2022 at 12:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Wells Oliver <wells.oliver@gmail.com> writes:
> > I've inherited a DB with a lot of legacy functions and aggregates that
> have
> > an SFUNC of array_append and an STYPE of anyarray.
> > Is there some way I can query for these aggregates/functions and see how
> > many I need to drop and have available to replace after a
> > successful upgrade?
>
> Something like
>
> select aggfnoid::regprocedure from pg_aggregate where aggtransfn =
> 'array_append'::regproc;
>
> regards, tom lane
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Struggling with 13->14 and anyarray -> anycompatiblearray
@ 2022-11-04 21:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2022-11-04 21:31 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
Wells Oliver <wells.oliver@gmail.com> writes:
> Thanks. This seems to return aggregates where array_append is used with
> anyarray, but I now see aggregates where e.g. array_cat is used with
> anyarray. Is there some way to generally query aggregates where any array
> function might be looking for anyarray and need to be dropped/re-created
> with anycompatiblearray?
Looking for aggtranstype = 'anyarray'::regtype would give you a hit list,
but beware that not every one of those was changed. Looking at HEAD,
I see
postgres=# select aggfnoid::regprocedure from pg_aggregate where aggtranstype = 'anyarray'::regtype;
aggfnoid
---------------
max(anyarray)
min(anyarray)
(2 rows)
so you'd want to exclude those. (I guess just skipping ones with oid <
9999 would be enough to skip built-ins.)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-11-04 21:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-04 18:47 Struggling with 13->14 and anyarray -> anycompatiblearray Wells Oliver <wells.oliver@gmail.com>
2022-11-04 19:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-04 20:51 ` Wells Oliver <wells.oliver@gmail.com>
2022-11-04 21:31 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox