作者
digoal
日期
2021-10-02
标签
PostgreSQL , 存储过程 , 函数 , 事务 , 自治 , begin , commit , rollback , savepoint , release savepoint , rollback to savepoint
1、产品的问题点
- PG 存储过程和函数内对自治事务支持不完整
2、问题点背后涉及的技术原理
- PG 的1个函数为1个原子操作, 要么全部回滚, 要么全部失败. (注意: exception里算一个新事务, 触发exception时, 函数体内的操作全部回滚, exception体内的执行如果正常则这个exception体内的变更操作可以提交.)
- 在函数内不能使用commit, rollback, savepoint等事务控制语句.
- 在存储过程中只能使用commit, rollback事务控制语句, 不能使用savepoint, rollback to savepoint, release savepoint等语句.
3、这个问题将影响哪些行业以及业务场景
- 使用function, procedure进行复杂业务逻辑处理的场景, 例如分析业务, 报表业务等
4、会导致什么问题?
- 无法灵活的处理事务控制
5、业务上应该如何避免这个坑
- 暂时没有很好的解决方案, 一些类似的替代方案, 都非常难操作
- 《PostgreSQL 10.0 preview 功能增强 - 匿名、自治事务(Oracle 兼容性)(background session)》
- 《PostgreSQL Oracle 兼容性之 - plpgsql 自治事务(autonomous_transaction)补丁》
- 《PostgreSQL Oracle 兼容性之 - 函数 自治事务 的写法和实现》
- 《PostgreSQL Oracle 兼容性 自治事务 - partial/sub commit within function》
- 《Compare dblink module Within One Transaction in PostgreSQL - dblink, 自治事务(Oracle兼容性)》
使用exception也很难满足, 如下, 使用变量、exception 来模拟savepoint和rollback to savepoint的功能, 也很复杂 :
create or replace procedure p (int, int) as $$
declare
v int := $1;
retris int := $2;
text_var1 text;
text_var2 text;
text_var3 text;
text_var4 text;
begin
if retris >= 3 then
raise notice 'retris: %', retris;
return;
end if;
if v = 0 then
v := 1;
insert into a values (1);
commit;
end if;
if v = 1 then
v := 2;
insert into a values (2);
commit;
end if;
if v = 2 then
v := 3;
insert into a values (3);
commit;
end if;
if v = 3 then
v := 4;
insert into a values (4);
commit;
end if;
exception when others then
GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
text_var2 = PG_EXCEPTION_DETAIL,
text_var3 = PG_EXCEPTION_HINT,
text_var4 = PG_EXCEPTION_CONTEXT;
raise notice '%,%,%,%', text_var1, text_var2, text_var3, text_var4;
commit;
call p(v-1, retris+1) ;
end;
$$ language plpgsql;
postgres=# \set VERBOSITY verbose
postgres=# call p (0,0);
NOTICE: 00000: cannot commit while a subtransaction is active,,,PL/pgSQL function p(integer,integer) line 18 at COMMIT
LOCATION: exec_stmt_raise, pl_exec.c:3906
NOTICE: 00000: cannot commit while a subtransaction is active,,,PL/pgSQL function p(integer,integer) line 18 at COMMIT
SQL statement "call p(v-1, retris+1)"
PL/pgSQL function p(integer,integer) line 45 at CALL
LOCATION: exec_stmt_raise, pl_exec.c:3906
NOTICE: 00000: cannot commit while a subtransaction is active,,,PL/pgSQL function p(integer,integer) line 18 at COMMIT
SQL statement "call p(v-1, retris+1)"
PL/pgSQL function p(integer,integer) line 45 at CALL
SQL statement "call p(v-1, retris+1)"
PL/pgSQL function p(integer,integer) line 45 at CALL
LOCATION: exec_stmt_raise, pl_exec.c:3906
NOTICE: 00000: retris: 3
LOCATION: exec_stmt_raise, pl_exec.c:3906
CALL
通过dblink去调用p(int,int), 开启一个新会话是可以的. 复杂度又增加了.
6、业务上避免这个坑牺牲了什么, 会引入什么新的问题
- 开发门槛非常高.
7、数据库未来产品迭代如何修复这个坑
- 希望在函数、存储过程中实现完整的事务控制逻辑. 包括begin;end;savepoint;rollback;commit;release savepoint;rollback to savepoint;等
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
