在AWS RDS aurora postgresql上升级到14.x或更高版本时更新“anyarray”或“anyelement”多态函数

flvtvl50  于 2023-06-05  发布在  PostgreSQL
关注(0)|答案(1)|浏览(232)

当从11.17 -> 15.2升级AWS RDS aurora postgresql集群时,我在pg_upgrade日志中遇到了这个致命错误:

fatal
Your installation contains user-defined objects that refer to internal
polymorphic functions with arguments of type "anyarray" or "anyelement".
These user-defined objects must be dropped before upgrading and restored
afterwards, changing them to refer to the new corresponding functions with
arguments of type "anycompatiblearray" and "anycompatible".

AWS在升级文档中没有提到这一点,所以我认为更改可能是由系统用户引入的。经过一番挖掘,似乎聚合函数改变了类型的命名方式(在postgresql版本14中要清楚)。我该怎么更新这个?
我在目标集群中的每个DB上运行了升级失败的查询子集:

--find incompatibilites on each DB:
\c <DATABASE>

SELECT 'aggregate' AS objkind,
       p.oid::regprocedure::text AS objname
FROM pg_proc AS p
JOIN pg_aggregate AS a ON a.aggfnoid=p.oid
JOIN pg_proc AS transfn ON transfn.oid=a.aggtransfn
WHERE p.oid >= 16384
  AND a.aggtransfn = ANY(ARRAY['array_append(anyarray,anyelement)', 'array_cat(anyarray,anyarray)', 'array_prepend(anyelement,anyarray)', 'array_remove(anyarray,anyelement)', 'array_replace(anyarray,anyelement,anyelement)', 'array_position(anyarray,anyelement)', 'array_position(anyarray,anyelement,integer)', 'array_positions(anyarray,anyelement)', 'width_bucket(anyelement,anyarray)']::regprocedure[]);

  objkind  |         objname         
-----------+-------------------------
 aggregate | array_accum(anyelement)
(1 row)

好吧,那现在怎么办?

2vuwiymt

2vuwiymt1#

解决方案:

--drop aggregate from sub 14.x db
mygreatdatabase=> DROP AGGREGATE array_accum(anyelement);
DROP AGGREGATE

--upgrade to 14.x or higher, and then re-create using updated type:
mygreatdatabase=> CREATE AGGREGATE array_accum(anycompatible) (SFUNC = array_append,STYPE = anycompatiblearray,INITCOND = '{}');

我希望AWS将此添加到RDS Aurora PostgresQL升级预检查的文档中,但这将在这里,直到更清楚为止。

相关问题