英文:
Updating "anyarray" or "anyelement" polymorphic functions when upgrading to 14.x or higher on AWS RDS aurora postgresql
问题
在将AWS RDS Aurora PostgreSQL集群从11.17升级到15.2时，在pg_upgrade日志中遇到了以下致命错误：
致命错误
您的安装包含引用内部多态函数的用户定义对象，其参数类型为"anyarray"或"anyelement"。
在升级之前必须删除这些用户定义对象，然后在之后恢复它们，将它们更改为引用新的相应函数，其参数类型为"anycompatiblearray"和"anycompatible"。
AWS在升级文档中没有提到这一点，因此我认为这些更改可能是由系统用户引入的。经过一些调查，似乎聚合函数已更改了类型的命名方式（在明确指出的情况下，是在PostgreSQL版本14中）。那么我该如何更新呢？
我在目标集群中的每个数据库上运行了一部分升级失败的查询：
--在每个数据库上查找不兼容项：
\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)
好的，现在怎么办？
英文:
When upgrading AWS RDS aurora postgresql cluster from 11.17 -> 15.2, I was met with this fatal error in the pg_upgrade logs:
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 does not mention this in the upgrade docs, so I thought the changed may have been introduced by a system user. After a bit of digging, it seems that the aggregate functions changed the way the types are named (in postgresql version 14 to be clear). So how do I update this?
I ran a subset the query that the upgrade failed on, on each DB in the target cluster:
--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)
Okay, so now what?
答案1
得分: 2
解决方案：
--从子 14.x 数据库中删除聚合函数
mygreatdatabase=> DROP AGGREGATE array_accum(anyelement);
DROP AGGREGATE
--升级到 14.x 或更高版本，然后使用更新后的类型重新创建：
mygreatdatabase=> CREATE AGGREGATE array_accum(anycompatible) (SFUNC = array_append,STYPE = anycompatiblearray,INITCOND = '{}');
希望 AWS 将此内容添加到 RDS Aurora PostgresQL 升级预检文档中，但在那方面更加明确之前，这里将保留这一信息。
英文:
Solution:
--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 = '{}');
My hope is that AWS adds this to the documentation on RDS Aurora PostgresQL Upgrade Pre-Checks, but this will be here until that is more clear.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论