背景
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;等