Обсуждение: Struggling with 13->14 and anyarray -> anycompatiblearray
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
wells.oliver@gmail.com
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
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
wells.oliver@gmail.com
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