一、前言
在原生PG中，创建一个自定义函数或者存储过程，如果出入参的类型不存在，那么创建将会报错；但在ORACLE中仍然可以创建，只是状态为失效，后续补上缺少的类型即可。而原生PG则必须依照特定的顺序来创建数据库对象。openGauss2.1版本起新增了package这种可以内建类型、函数、过程的对象，整个依赖关系变得更加复杂，自动化迁移工具已经无法一次性自动将ORACLE的全部对象在openGauss中进行一次性创建。
在某客户项目中，MogDB联合openGauss社区，由社区提供初版代码，MogDB侧进行大范围（真实应用系统中数百万行存储过程代码的各种混合场景）的测试验证及BUG修复，完成了PLSQL编译依赖这一重要功能；另外，MogDB5.2.0版本中，相较于openGauss 6.0版本，还额外增加了PLSQL增量编译这个特性。
注意，本文以MogDB5.2.0版本进行演示，部分实现和openGauss6.0中的PLSQL编译依赖存在区别。
二、常见场景
1.创建一个package ，其中有一个function的入参是一个自定义类型，但该自定义类型还没创建
在不开启编译依赖功能时，这个场景会报错。“ERROR:”，意味着该语句执行不成功，因此这个包也没有创建到数据库中,所以也无法再接着创建package body
openGauss=# create package pkg_test1 is
openGauss$# function f1(i ty_xyz) return int;
openGauss$# end pkg_test1;
openGauss$# /
ERROR: type ty_xyz does not exist
openGauss=#
如果开启了编译依赖功能，则可以创建成功，并同时发出警告“WARNNING:”
MogDB=> create package pkg_test1 is
MogDB$> function f1(i ty_xyz) return int;
MogDB$> end pkg_test1;
MogDB$> /
WARNING: Type ty_xyz does not exist.
WARNING: The header information of function f1 is not defined.
CONTEXT: compilation of PL/pgSQL package or object near line 1
WARNING: Package or object created with compilation errors.
CREATE PACKAGE
MogDB=>
继续创建package body和缺少的类型
MogDB=> create package body pkg_test1 is
MogDB$> function f1(i ty_xyz) return int is
MogDB$> begin
MogDB$> return 1;
MogDB$> end;
MogDB$> end pkg_test1;
MogDB$> /
WARNING: Type ty_xyz does not exist.
WARNING: The header information of function f1 is not defined.
CONTEXT: compilation of PL/pgSQL package or object near line 1
WARNING: The header information of function f1 is not defined.
CONTEXT: compilation of PL/pgSQL package or object near line 2
WARNING: Package or object Body created with compilation errors.
CREATE PACKAGE BODY
MogDB=> create type ty_xyz is object(a int);
MogDB$> /
CREATE OBJECT TYPE
MogDB=>
就可以正常调用函数了
MogDB=> call pkg_test1.f1(ty_xyz(1));
f1
----
1
(1 row)
到这里，可能会有人说，制定一个开发规范，必须先建自定义类型，再建依赖这个自定义类型的包不就好了嘛！
且不谈如果对象出现了互相依赖怎么办，如果这个时候开发人员要修改已经创建的自定义类型怎么办？
在openGauss5.0版本中，不支持create or replace type（OG6.0支持了，但有一些功能约束），因此想要修改一个自定义类型，必须先把它drop掉，然后再创建。但是由于这个类型被函数所依赖了，无法drop，提示要加cascade
openGauss=# drop type ty_xyz;
ERROR: cannot drop type ty_xyz because other objects depend on it
DETAIL: function public.f1(ty_xyz) depends on type ty_xyz
HINT: Use DROP ... CASCADE to drop the dependent objects too.
如果加cascade去drop
openGauss=# drop type ty_xyz cascade;
NOTICE: drop cascades to function public.f1(ty_xyz)
DROP TYPE
openGauss=# select pkgname from gs_package where pkgname='pkg_test1';
pkgname
-----------
pkg_test1
(1 row)
openGauss=# select count(1) from pg_proc where proname='f1';
count
-------
0
(1 row)
会发现包还在，但里面的函数不见了，引起元数据缺失。就算此时再把这个类型补上去，丢失的函数也不会自动补回来。如果启用了PLSQL编译依赖功能，则不会存在此问题，因为删除类型的时候就不会去删除依赖这个类型的对象了。
2.package A中的function入参类型是package B中定义的类型，修改package B并replace
在openGauss5.0版本中，先按顺序创建两个package
openGauss=# create package pkg_test_b is
openGauss$# type sty_123 is record(a int);
openGauss$# end pkg_test_b;
openGauss$# /
CREATE PACKAGE
openGauss=# create package pkg_test_a is
openGauss$# function f2( i pkg_test_b.sty_123) return int;
openGauss$# end pkg_test_a;
openGauss$# /
CREATE PACKAGE
openGauss=# create package body pkg_test_a is
openGauss$# function f2( i pkg_test_b.sty_123) return int is
openGauss$# begin
openGauss$# return 1;
openGauss$# end;
openGauss$# end pkg_test_a;
openGauss$# /
CREATE PACKAGE BODY
openGauss=#
此时调用函数是正常的
openGauss=# call pkg_test_a.f2(null);
f2
----
1
(1 row)
然后修改pkg_test_b，原本的代码不动，在里面新增一个类型
openGauss=# create or replace package pkg_test_b is
openGauss$# type sty_123 is record(a int);
openGauss$# type sty_456 is record(b int);
openGauss$# end pkg_test_b;
openGauss$# /
CREATE PACKAGE
openGauss$#
再去调用这个函数
openGauss=# call pkg_test_a.f2(null);
ERROR: function "f2" doesn't exist
结果函数不存在了，但这个操作明明没有对pkg_test_a进行直接操作。这是因为replace pkg_test_b的时候，对pkg_test_b里所有的对象做了自动的级联删除，依赖了这个包的其他对象，都被自动删了，和前一个场景中提到的问题一样。
如果开启了PLSQL编译依赖功能，则该问题也不再存在
三、原理说明
在openGauss6.0.0版本中，通过设置behavior_compat_options=plpgsql_dependency来开启此功能；
在MogDB 5.2.0版本中，通过设置enable_plsql_compiledepend=on来开启此功能。
对于每一个创建成功的function，必须明确头部的参数类型，在pg_proc中会记录对应这些类型的oid。当引用的类型不存在时，则无法获取其oid。因此该功能引入了一个undefined类型（oid=4408）,只要根据类型名称找不到类型，就先将该函数的该参数类型设置为undefined；在gs_dependencies_obj、gs_dependencies中记录引用的依赖关系；并且pg_object中记录该procedure为失效状态
MogDB=> create procedure f_test_1 (l_abc abc,l_def out def)
MogDB-> is
MogDB$> begin
MogDB$> null;
MogDB$> end;
MogDB$> /
WARNING: The header information of function f_test_1 is not defined.
CONTEXT: compilation of PL/pgSQL function "f_test_1" near line 2
WARNING: Procedure created with compilation errors.
CREATE PROCEDURE
MogDB=>
MogDB=> \x
Expanded display is on.
MogDB=> select oid,* from pg_proc where proname='f_test_1';
-[ RECORD 1 ]-------+------------------------
oid | 26632
proname | f_test_1
pronamespace | 16783
proowner | 16781
prolang | 12563
procost | 100
prorows | 0
provariadic | 0
protransform | -
proisagg | f
proiswindow | f
prosecdef | t
proleakproof | f
proisstrict | f
proretset | f
provolatile | v
pronargs | 1
pronargdefaults | 0
prorettype | 4408
proargtypes | 4408
proallargtypes | {4408,4408}
proargmodes | {i,o}
proargnames | {l_abc,l_def}
proargdefaults |
prosrc | DECLARE
| begin
| null;
| end
probin |
proconfig |
proacl | {system=X/system}
prodefaultargpos |
fencedmode | f
proshippable | f
propackage | f
prokind | p
proargsrc | l_abc abc,l_def out def
propackageid | 0
proisprivate | f
proargtypesext |
prodefaultargposext |
allargtypes | 4408 4408
allargtypesext |
objecttypeoid |
methodtype |
isfinal |
instantiable |
overriding |
inherited |
MogDB=>
MogDB=> select oid,* from pg_catalog.gs_dependencies_obj where name like 'f_test_1%' or name in ('abc','def');
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid | 26630
schemaname | system
packagename | null
type | 3
name | abc
objnode | {DependenciesUndefined}
-[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid | 26631
schemaname | system
packagename | null
type | 3
name | def
objnode | {DependenciesUndefined}
-[ RECORD 3 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid | 26633
schemaname | system
packagename | null
type | 5
name | f_test_1(pg_catalog.undefined,pg_catalog.undefined)
objnode | {DependenciesProchead :undefined true :proName f_test_1 :proArgSrc l_abc\ abc,l_def\ out\ def :funcHeadSrc create\ procedure\ f_test_1\ \(l_abc\ abc,l_def\ out\ def\)}
MogDB=> select * from pg_catalog.gs_dependencies where objectname like 'f_test_1%' ;
-[ RECORD 1 ]----------------------------------------------------
schemaname | system
packagename | null
refobjpos | 4
refobjoid | 26630
objectname | f_test_1(pg_catalog.undefined,pg_catalog.undefined)
-[ RECORD 2 ]----------------------------------------------------
schemaname | system
packagename | null
refobjpos | 4
refobjoid | 26631
objectname | f_test_1(pg_catalog.undefined,pg_catalog.undefined)
MogDB=>
MogDB=> select * from pg_object where object_oid=26632;
-[ RECORD 1 ]------------------------------
object_oid | 26632
object_type | P
creator | 16781
ctime | 2024-11-06 14:12:06.944254+08
mtime | 2024-11-06 14:12:06.944254+08
createcsn |
changecsn |
valid | f
MogDB=>
此时如果将不存在的类型补上,则procedure会自动根据类型名称更新相关表。
比如我们先把abc这个类型建上，可以发现procedure的Undefined类型只有一个了，但此时pg_object 里仍然是失效状态
MogDB=> create type abc is (a int);
CREATE TYPE
MogDB=> select oid from pg_type where typname='abc';
-[ RECORD 1 ]
oid | 26636
MogDB=> select oid,* from pg_catalog.gs_dependencies_obj where name like 'f_test_1%' or name in ('abc','def');
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid | 26631
schemaname | system
packagename | null
type | 3
name | def
objnode | {DependenciesUndefined}
-[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid | 26630
schemaname | system
packagename | null
type | 3
name | abc
objnode | {DependenciesType :typType c :typCategory C :attrInfo a:pg_catalog.int4, :isRel false :elemTypName <> :idxByTypName <> :baseTypeOid 0}
-[ RECORD 3 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid | 26633
schemaname | system
packagename | null
type | 5
name | f_test_1(system.abc,pg_catalog.undefined)
objnode | {DependenciesProchead :undefined true :proName f_test_1 :proArgSrc l_abc\ abc,l_def\ out\ def :funcHeadSrc create\ procedure\ f_test_1\ \(l_abc\ abc,l_def\ out\ def\)}
MogDB=> select * from pg_catalog.gs_dependencies where objectname like 'f_test_1%' ;
-[ RECORD 1 ]------------------------------------------
schemaname | system
packagename | null
refobjpos | 4
refobjoid | 26630
objectname | f_test_1(system.abc,pg_catalog.undefined)
-[ RECORD 2 ]------------------------------------------
schemaname | system
packagename | null
refobjpos | 4
refobjoid | 26631
objectname | f_test_1(system.abc,pg_catalog.undefined)
MogDB=> select oid,* from pg_proc where proname='f_test_1';
-[ RECORD 1 ]-------+------------------------
oid | 26632
proname | f_test_1
pronamespace | 16783
proowner | 16781
prolang | 12563
procost | 100
prorows | 0
provariadic | 0
protransform | -
proisagg | f
proiswindow | f
prosecdef | t
proleakproof | f
proisstrict | f
proretset | f
provolatile | v
pronargs | 1
pronargdefaults | 0
prorettype | 4408
proargtypes | 26636
proallargtypes | {26636,4408}
proargmodes | {i,o}
proargnames | {l_abc,l_def}
proargdefaults |
prosrc | DECLARE
| begin
| null;
| end
probin |
proconfig |
proacl | {system=X/system}
prodefaultargpos |
fencedmode | f
proshippable | f
propackage | f
prokind | p
proargsrc | l_abc abc,l_def out def
propackageid | 0
proisprivate | f
proargtypesext |
prodefaultargposext |
allargtypes | 26636 4408
allargtypesext |
objecttypeoid |
methodtype |
isfinal |
instantiable |
overriding |
inherited |
MogDB=>
MogDB=> select * from pg_object where object_oid=26632;
-[ RECORD 1 ]------------------------------
object_oid | 26632
object_type | P
creator | 16781
ctime | 2024-11-06 14:12:06.944254+08
mtime | 2024-11-06 14:12:06.944254+08
createcsn |
changecsn |
valid | f
MogDB=>
我们接着把另一个类型补上
MogDB=> create type def is (a int);
CREATE TYPE
MogDB=> select oid from pg_type where typname='def';
-[ RECORD 1 ]
oid | 26643
MogDB=> select oid,* from pg_catalog.gs_dependencies_obj where name like 'f_test_1%' or name in ('abc','def');
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid | 26630
schemaname | system
packagename | null
type | 3
name | abc
objnode | {DependenciesType :typType c :typCategory C :attrInfo a:pg_catalog.int4, :isRel false :elemTypName <> :idxByTypName <> :baseTypeOid 0}
-[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid | 26631
schemaname | system
packagename | null
type | 3
name | def
objnode | {DependenciesType :typType c :typCategory C :attrInfo a:pg_catalog.int4, :isRel false :elemTypName <> :idxByTypName <> :baseTypeOid 0}
-[ RECORD 3 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid | 26633
schemaname | system
packagename | null
type | 5
name | f_test_1(system.abc,system.def)
objnode | {DependenciesProchead :undefined false :proName f_test_1 :proArgSrc l_abc\ abc,l_def\ out\ def :funcHeadSrc create\ procedure\ f_test_1\ \(l_abc\ abc,l_def\ out\ def\)}
MogDB=> select * from pg_catalog.gs_dependencies where objectname like 'f_test_1%' ;
-[ RECORD 1 ]--------------------------------
schemaname | system
packagename | null
refobjpos | 4
refobjoid | 26630
objectname | f_test_1(system.abc,system.def)
-[ RECORD 2 ]--------------------------------
schemaname | system
packagename | null
refobjpos | 4
refobjoid | 26631
objectname | f_test_1(system.abc,system.def)
MogDB=> select oid,* from pg_proc where proname='f_test_1';
-[ RECORD 1 ]-------+------------------------
oid | 26632
proname | f_test_1
pronamespace | 16783
proowner | 16781
prolang | 12563
procost | 100
prorows | 0
provariadic | 0
protransform | -
proisagg | f
proiswindow | f
prosecdef | t
proleakproof | f
proisstrict | f
proretset | f
provolatile | v
pronargs | 1
pronargdefaults | 0
prorettype | 26643
proargtypes | 26636
proallargtypes | {26636,26643}
proargmodes | {i,o}
proargnames | {l_abc,l_def}
proargdefaults |
prosrc | DECLARE
| begin
| null;
| end
probin |
proconfig |
proacl | {system=X/system}
prodefaultargpos |
fencedmode | f
proshippable | f
propackage | f
prokind | p
proargsrc | l_abc abc,l_def out def
propackageid | 0
proisprivate | f
proargtypesext |
prodefaultargposext |
allargtypes | 26636 26643
allargtypesext |
objecttypeoid |
methodtype |
isfinal |
instantiable |
overriding |
inherited |
MogDB=> select * from pg_object where object_oid=26632;
-[ RECORD 1 ]------------------------------
object_oid | 26632
object_type | P
creator | 16781
ctime | 2024-11-06 14:12:06.944254+08
mtime | 2024-11-06 14:12:06.944254+08
createcsn |
changecsn |
valid | f
MogDB=>
可以看到这个procedure的参数没有undefined类型了，但是pg_object中仍然为失效状态，此时可以通过执行alter procedure {procedure_name} compile命令来让它重新编译，编译通过即更新为有效状态；或者直接执行这个procedure，也会触发自动编译。
MogDB=> alter procedure f_test_1 compile;
ALTER PROCEDURE
MogDB=> select * from pg_object where object_oid=26632;
-[ RECORD 1 ]------------------------------
object_oid | 26632
object_type | P
creator | 16781
ctime | 2024-11-06 14:12:06.944254+08
mtime | 2024-11-06 15:31:58.694414+08
createcsn |
changecsn | 24098
valid | t
MogDB=>
如果此时再drop掉其中一个type,可以发现procedure里那个类型又变成了undefined，并且procedure也变成了失效状态
MogDB=> drop type abc;
DROP TYPE
MogDB=> select oid,* from pg_proc where proname='f_test_1';
-[ RECORD 1 ]-------+------------------------
oid | 26632
proname | f_test_1
pronamespace | 16783
proowner | 16781
prolang | 12563
procost | 100
prorows | 0
provariadic | 0
protransform | -
proisagg | f
proiswindow | f
prosecdef | t
proleakproof | f
proisstrict | f
proretset | f
provolatile | v
pronargs | 1
pronargdefaults | 0
prorettype | 26643
proargtypes | 4408
proallargtypes | {4408,26643}
proargmodes | {i,o}
proargnames | {l_abc,l_def}
proargdefaults |
prosrc | DECLARE
| begin
| null;
| end
probin |
proconfig |
proacl | {system=X/system}
prodefaultargpos |
fencedmode | f
proshippable | f
propackage | f
prokind | p
proargsrc | l_abc abc,l_def out def
propackageid | 0
proisprivate | f
proargtypesext |
prodefaultargposext |
allargtypes | 4408 26643
allargtypesext |
objecttypeoid |
methodtype |
isfinal |
instantiable |
overriding |
inherited |
MogDB=>select oid,* from pg_catalog.gs_dependencies_obj where name like 'f_test_1%' or name in ('abc','def');
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid | 26631
schemaname | system
packagename | null
type | 3
name | def
objnode | {DependenciesType :typType c :typCategory C :attrInfo a:pg_catalog.int4, :isRel false :elemTypName <> :idxByTypName <> :baseTypeOid 0}
-[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid | 26630
schemaname | system
packagename | null
type | 3
name | abc
objnode | {DependenciesUndefined}
-[ RECORD 3 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid | 26644
schemaname | system
packagename | null
type | 5
name | f_test_1(pg_catalog.undefined,system.def)
objnode | {DependenciesProchead :undefined true :proName f_test_1 :proArgSrc l_abc\ abc,l_def\ out\ def :funcHeadSrc create\ or\ replace\ procedure\ f_test_1\ \(l_abc\ abc,l_def\ out\ def\)}
MogDB=> select * from pg_catalog.gs_dependencies where objectname like 'f_test_1%' ;
-[ RECORD 1 ]------------------------------------------
schemaname | system
packagename | null
refobjpos | 4
refobjoid | 26631
objectname | f_test_1(pg_catalog.undefined,system.def)
-[ RECORD 2 ]------------------------------------------
schemaname | system
packagename | null
refobjpos | 4
refobjoid | 26630
objectname | f_test_1(pg_catalog.undefined,system.def)
MogDB=> select * from pg_object where object_oid=26632;
-[ RECORD 1 ]------------------------------
object_oid | 26632
object_type | P
creator | 16781
ctime | 2024-11-06 14:12:06.944254+08
mtime | 2024-11-06 15:31:58.694414+08
createcsn |
changecsn | 24098
valid | f
以上即为这个功能的基本原理。
但MogDB将这个功能运用于真实的应用场景中，还遇到了不少问题。
四、初版方案的问题
1.并发编译
当有procedure失效时，此时如果同时有多个会话去执行这个procedure，则每个会话都会自动触发编译，并且去更新系统表，而并发更新系统表时，会出现tuple concurrently updated的报错。
https://gitee.com/opengauss/openGauss-server/issues/I9AZ7T
另外，这个自动编译功会尝试进行级联编译，则可能会出现在一个事务中，更新系统表中的多行记录，此时如果存储过程复杂，各种嵌套，那么很有可能出现有两个会话对系统表相同行的更新顺序不一样，从而触发deadlock。
https://gitee.com/opengauss/openGauss-server/issues/I991AN
有开发经验的自然会想到，要不要在每次编译单个对象时，先加行锁，然后抛个自治事务去编译，再回来释放这个行锁？我们对比了Oracle执行存储过程时的自动编译，发现存储过程有效状态变化与主事务之间没有关系，就算存储过程没执行完，事务没提交，存储过程依然从无效变成了有效。
最后openGauss/MogDB的确也采取了自动使用自治事务去编译这种方式去解决这个问题。
2.依赖关系过多的情况下，replace package非常慢
某客户自研了一些应用系统，将所有存储过程里会用到的类型，均在一个package里建了subtype，此时所有存储过程都不再使用基本类型，而是统一使用这个package里的subtype。某天需要对这个package新增一个subtype，就会执行create or replace package 。
但openGauss对replace package的操作为，先逐个drop掉这个package里的所有type/function/procedure，然后再重新执行create package，逐个建立package里的type/function/procedure。试想，如果有上万个存储过程使用了这个package里的subtype作为参数类型，那么把type给drop后，则需要把这上万个存储过程的系统元数据全部更新一次，中间还会触发到级联编译，整个处理就变得非常慢。当时第一版代码进行这个场景的验证时，执行了1小时没执行完。
3.replace package会导致使用了这个package内函数的自定义视图，查询报错
openGauss=# show behavior_compat_options;
behavior_compat_options
-------------------------
plpgsql_dependency
(1 row)
openGauss=# create package pkg_test_view is
openGauss$# function f1 return int;
openGauss$# end pkg_test_view;
openGauss$# /
CREATE PACKAGE
openGauss=#
openGauss=# create package body pkg_test_view is
openGauss$# function f1 return int is
openGauss$# begin
openGauss$# return 1;
openGauss$# end;
openGauss$# end pkg_test_view;
openGauss$# /
CREATE PACKAGE BODY
openGauss=# create view view_test as
openGauss-# select pkg_test_view.f1() as a;
CREATE VIEW
openGauss=# select * from view_test;
a
---
1
(1 row)
openGauss=# create or replace package pkg_test_view is
openGauss$# v1 int;
openGauss$# function f1 return int;
openGauss$# end pkg_test_view;
openGauss$# /
CREATE PACKAGE
openGauss=#
openGauss=# create or replace package body pkg_test_view is
openGauss$# function f1 return int is
openGauss$# begin
openGauss$# return 1;
openGauss$# end;
openGauss$# end pkg_test_view;
openGauss$# /
CREATE PACKAGE BODY
openGauss=# select * from view_test;
ERROR: cache lookup failed for function 23296
openGauss=# select * from pg_views where viewname='view_test';
ERROR: cache lookup failed for function 23296
CONTEXT: referenced column: definition
openGauss=# select pg_get_viewdef('view_test');
ERROR: cache lookup failed for function 23296
CONTEXT: referenced column: pg_get_viewdef
openGauss=#
[og600@kylinv10sp3-node1 ~]$ gs_dump postgres -t view_test
gs_dump[port='21200'][postgres][2024-11-07 09:55:33]: Begin scanning database.
Progress: [==================================================] 100% (38/37, cur_step/total_step). finish scanning database
gs_dump[port='21200'][postgres][2024-11-07 09:55:33]: Finish scanning database.
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] WARNING: could not resolve dependency loop among these items:
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] FUNCTION test_f3 (ID 442 OID 16420)
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] object type 21 (ID 4065 OID 16419)
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] PRE-DATA BOUNDARY (ID 4069)
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] WARNING: could not resolve dependency loop among these items:
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] FUNCTION test_f3 (ID 444 OID 16424)
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] object type 21 (ID 4066 OID 16423)
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] PRE-DATA BOUNDARY (ID 4069)
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] WARNING: could not resolve dependency loop among these items:
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] FUNCTION x (ID 445 OID 23291)
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] object type 21 (ID 4067 OID 23290)
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] PRE-DATA BOUNDARY (ID 4069)
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] WARNING: could not resolve dependency loop among these items:
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] FUNCTION f1 (ID 447 OID 23301)
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] object type 21 (ID 4068 OID 23295)
gs_dump: [port='21200'] [postgres] [sorter] [2024-11-07 09:55:33] PRE-DATA BOUNDARY (ID 4069)
gs_dump[port='21200'][postgres][2024-11-07 09:55:33]: Start dumping objects
gs_dump: [port='21200'] [postgres] [archiver (db)] [2024-11-07 09:55:33] query failed: ERROR: cache lookup failed for function 23296
CONTEXT: referenced column: viewdef
gs_dump: [port='21200'] [postgres] [archiver (db)] [2024-11-07 09:55:33] query was: SELECT pg_catalog.pg_get_viewdef('23297'::pg_catalog.oid) AS viewdef
[og600@kylinv10sp3-node1 ~]$
问题2中说明了openGauss对replace package的处理方式，这个处理方式同时也会导致自定义视图无法查询ERROR: cache lookup failed for function 23296，因为函数被自动drop重建了，哪怕这个函数本身没有任何变化，这个函数的oid也不是之前的值了。而且出现这个问题的视图还无法查询视图的定义SQL，逻辑备份也会被中断。如果没有备份视图的原始SQL，那么这个问题将无法修复。MogDB在之前的版本中，相较openGauss，额外有多一个获取视图原始定义的函数pg_get_ori_viewdef，在遇到这个问题时，还有办法补救。
另外，openGauss这种处理方式，意味着修改package时，哪怕package body没有任何变化，也必须把package和package body的创建代码都执行一次。
五、MogDB对PLSQL编译依赖功能的额外增强
MogDB 5.2.0版本 ,在PLSQL编译依赖这个功能的基础上，额外增加了对replace package的增量处理逻辑–PLSQL增量编译。也就是说，在MogDB 5.2.0版本中，replace package，只会对系统表进行最低限度的变更，不再全部drop包内的全部对象，而是对type/function/procedure进行replace操作(replace 操作一般不变oid)；如果function head没变，则不会触发plsql编译依赖冗长的处理逻辑，如果function body也没变，则replace的操作也会直接忽略。简单来说，就是处理速度变快，而且不会丢东西了。
MogDB=> create view view_test as
MogDB$> select pkg_test_view.f1() as a;
CREATE VIEW
MogDB=> select * from view_test;
a
---
1
(1 row)
MogDB=> create or replace package pkg_test_view is
MogDB$> function f1 return int;
MogDB$> end pkg_test_view;
MogDB$> /
CREATE PACKAGE
MogDB=>
MogDB=> create or replace package body pkg_test_view is
MogDB$> function f1 return int is
MogDB$> begin
MogDB$> return 1;
MogDB$> end;
MogDB$> end pkg_test_view;
MogDB$> /
CREATE PACKAGE BODY
MogDB=> create or replace package pkg_test_view is
MogDB$> i int;
MogDB$> function f1 return int;
MogDB$> end pkg_test_view;
MogDB$> /
CREATE PACKAGE
MogDB=> select * from view_test;--package头里加东西也不需要重建body了
a
---
1
(1 row)
MogDB=>create or replace package body pkg_test_view is
MogDB$> function f1 return int is
MogDB$> begin
MogDB$> return 1;
MogDB$> end;
MogDB$> end pkg_test_view;
MogDB$> /
CREATE PACKAGE BODY
MogDB=> select * from view_test;
a
---
1
(1 row)
MogDB=>
MogDB在openGauss的基础上进行创新，加入了PLSQL增量编译逻辑，一次性解决了历史遗留的多个问题。
六、总结
对于拥有非常多存储过程的应用系统，尤其是那些有非常多package及自定义类型的应用来说，PLSQL编译依赖是必须要有的一个功能，否则根本无法实现从ORACLE到国产库的顺利迁移。对于PG/OG系数据库来说，这也是一个非常复杂的功能，因为存在既定框架的限制，需要相当大的研发测试投入，以及大量的真实测试场景输入，才可能把这个功能做完善做稳定。
MogDB 5.2.0支持这个功能后，对ORACLE应用迁移将变得更加顺畅。
- 本文作者： DarkAthena
- 本文链接： https://www.darkathena.top/archives/mogdb-5.2.0-support-plsql-dependencies
- 版权声明： 本博客所有文章除特别声明外，均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
