一、前言
在原生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应用迁移将变得更加顺畅。