PostgreSQL中的存储过程不支持使用savepoint、rollback to。原因是PG的存储过程中,异常处理使用子事务来实现的,也就是一旦发生异常,当前procedure的begin块中执行过的所有语句都会直接回滚:
procedure
begin
insert into tbl values (1);
x = 1 / 0; -- 异常发生
insert into tbl values (2);
exception
..
..
end;
当异常发生后,第二条insert没有执行到就跳转了,比较容易理解;但是第一条insert会被回滚,这种行为是PG特有的,和Oracle是有区别的,Oracle中异常发生只会跳转,不会回滚也不存在子事务。
总结
还有一个最最重要的区别:openGassdb的子事务重新生成后,子事务ID不变,和nestinglevel基本是保持一致的。
为什么呢?因为在PopTransaction时,将父事务的值set到计数器上了:
PushTransaction
t_thrd.xact_cxt.currentSubTransactionId = s->parent->subTransactionId;
而PG中这个计数器是一直递增的,不能减小。
CASE1
CREATE or replace PROCEDURE p_outter()
as
carry float;
begin
INSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (4);
savepoint sp4;
INSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (5);
rollback to sp4;
EXCEPTION WHEN others THEN
RAISE NOTICE 'in exception %', sqlerrm;
end;
/
truncate t_plpgsql_transaction_20221222_01;
begin;
INSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (3);
savepoint sp3;
call p_outter();
select * from t_plpgsql_transaction_20221222_01;
subTransactionId = 3 name = "" → 异常子事务(内层函数自动创建)
subTransactionId = 2 name = "sp3"
subTransactionId = 1 name = "" 基础事务
econtext两个挂在3、2各一个
p *u_sess->plsql_cxt.simple_econtext_stack
$9 = {stack_econtext = 0x7fc1baefe560, xact_subxid = 3, statckEntryId = 2, next = 0x7fc1bb00d840}
$10 = {stack_econtext = 0x7fc1baefe288, xact_subxid = 2, statckEntryId = 1, next = 0x0}
subTransactionId = 4. name = "sp4"
subTransactionId = 3 name = "" → 异常子事务(内层函数自动创建)
subTransactionId = 2 name = "sp3"
subTransactionId = 1 name = "" 基础事务
econtext三个挂在4、3、2各一个。
p *u_sess->plsql_cxt.simple_econtext_stack
$13 = {stack_econtext = 0x7fc1baefe7d0, xact_subxid = 4, statckEntryId = 3, next = 0x7fc1bb00db08}
$14 = {stack_econtext = 0x7fc1baefe560, xact_subxid = 3, statckEntryId = 2, next = 0x7fc1bb00d840}
$15 = {stack_econtext = 0x7fc1baefe288, xact_subxid = 2, statckEntryId = 1, next = 0x0}
回滚流程
SPI_savepoint_rollback
RollbackToSavepoint
CommitTransactionCommand(STP_commit == true)
case TBLOCK_SUBRESTART
AbortSubTransaction(STP_commit)
AtSubAbort_XXX
CleanupSubTransaction(STP_commit)
PopTransaction
BeginInternalSubTransaction // 重启子事务
rollback分两步:
回滚完成后事务状态
subTransactionId = 4. name = "sp4"
subTransactionId = 3 name = "" → 异常子事务(内层函数自动创建)
subTransactionId = 2 name = "sp3"
subTransactionId = 1 name = "" 基础事务
exec_stmt_block
exec_exception_begin(estate, &excptContext)
PG_TRY
exec_stmts
exec_exception_end
exec_exception_end函数
exec_exception_end
// 如果没检查点在block中
if (context->curExceptionCounter == u_sess->SPI_cxt.portal_stp_exception_counter && GetCurrentTransactionName() == NULL)
// 可以直接提交
SPI_savepoint_release
stp_cleanup_subxact_resource
else
// 不提交!
事务堆栈
subTransactionId = 4. name = "sp4"
subTransactionId = 3 name = "" → 异常子事务(内层函数自动创建)
subTransactionId = 2 name = "sp3"
subTransactionId = 1 name = "" 基础事务
CASE1
CREATE or replace PROCEDURE p_outter()
as
carry float;
begin
INSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (4);
rollback to sp3;
EXCEPTION WHEN others THEN
RAISE NOTICE 'in exception %', sqlerrm;
end;
/
truncate t_plpgsql_transaction_20221222_01;
begin;
INSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (3);
savepoint sp3;
call p_outter();
select * from t_plpgsql_transaction_20221222_01;
执行前
subTransactionId = 3 name = "" → 异常子事务(内层函数自动创建)
subTransactionId = 2 name = "sp3"
subTransactionId = 1 name = "" 基础事务
回滚完成
subTransactionId = 2 name = "sp3"
subTransactionId = 1 name = "" 基础事务
exec_exception_end函数
exec_exception_end
// 如果没检查点在block中
if (context->curExceptionCounter == u_sess->SPI_cxt.portal_stp_exception_counter && GetCurrentTransactionName() == NULL)
// 可以直接提交
SPI_savepoint_release
stp_cleanup_subxact_resource
else
// 不提交!
context->curExceptionCounter = 1 u_sess->SPI_cxt.portal_stp_exception_counter = 0 GetCurrentTransactionName() = “sp3”
走不提交分支!
curExceptionCounter含义:在启动异常子事务的时候记录一下portal_stp_exception_counter的值。 portal_stp_exception_counter含义:记录有几个异常子事务。
exec_exception_end函数的判断逻辑是,启用异常子事务的时候记录下有几个异常子事务,清理异常子事务的时候看下这个数量有没有变化,有变化说明被里面的操作减少了,不可能增加只能减少,所以exec_exception_end不需要清理了。
CASE1
CREATE or replace PROCEDURE p_outter()
as
carry float;
begin
INSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (4);
rollback to sp3;
EXCEPTION WHEN others THEN
RAISE NOTICE 'in exception %', sqlerrm;
end;
/
truncate t_plpgsql_transaction_20221222_01;
begin;
INSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (3);
savepoint sp3;
INSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (4);
savepoint sp4;
call p_outter();
select * from t_plpgsql_transaction_20221222_01;
结论:不会调整,会出现connectSubid=3
*u_sess->SPI_cxt._current = {processed = 1, connectSubid = 3 }
但事务堆栈只有两层的情况
subTransactionId = 2
subTransactionId = 1
SPI变成了野孩子。
drop table if exists t_plpgsql_transaction_20221222_01;
create table t_plpgsql_transaction_20221222_01(a int);
CREATE or replace PROCEDURE p_outter()
as
carry float;
begin
INSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (4);
savepoint sp4;
call p_inner();
EXCEPTION WHEN others THEN
RAISE NOTICE 'in exception';
end;
/
CREATE or replace PROCEDURE p_inner()
as
carry float;
begin
INSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (50);
savepoint sp5;
INSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (60);
carry = 1 / 0;
EXCEPTION WHEN others THEN
RAISE NOTICE 'in exception exception';
end;
/
truncate t_plpgsql_transaction_20221222_01;
begin;
INSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (3);
savepoint sp3;
call p_outter();
select * from t_plpgsql_transaction_20221222_01;
rollback to sp4;
select * from t_plpgsql_transaction_20221222_01;
commit;
下面调试call p_outter_base()异常子事务回滚流程:
exec_stmt_block
exec_exception_begin(estate, &excptContext) // 起一层子事务
PG_TRY
// 这里的事务状态??
事务堆栈:
subTransactionId = 6 name = "sp5"
subTransactionId = 5 name = "" → 异常子事务(内层函数自动创建)
subTransactionId = 4 name = "sp4"
subTransactionId = 3 name = "" → 异常子事务(外层函数自动创建)
subTransactionId = 2 name = "sp3"
subTransactionId = 1 name = "" 基础事务
econtext堆栈
(gdb) p *u_sess->plsql_cxt.simple_econtext_stack
$17 = {stack_econtext = 0x7f17085dccb0, xact_subxid = 5, statckEntryId = 5, next = 0x7f17056e41f0}
$18 = {stack_econtext = 0x7f17085dca40, xact_subxid = 4, statckEntryId = 4, next = 0x7f17085e3b70}
$19 = {stack_econtext = 0x7f17085dc7d0, xact_subxid = 4, statckEntryId = 3, next = 0x7f17085e3b08}
$20 = {stack_econtext = 0x7f17085dc560, xact_subxid = 3, statckEntryId = 2, next = 0x7f17085e3840}
$21 = {stack_econtext = 0x7f17085dc288, xact_subxid = 2, statckEntryId = 1, next = 0x0}
SPI堆栈
p u_sess->SPI_cxt._stack[0]
$25 = {processed = 0, connectSubid = 2}
$26 = {processed = 0, connectSubid = 4}
资源归属关系:s=savepoint subtransaction、e=exception subtransaction
subTransaction 1o 2s 3e 4s 5e 6s
econtext 5
econtext 4
econtext 4
econtext 3
econtext 2
SPI_cxt 4
SPI_cxt 2
总结:把顶层的sp5的子事务和异常子事务的资源都释放了。
exec_stmt_block
exec_exception_begin(estate, &excptContext) // 起一层子事务
PG_TRY
plpgsql_create_econtext
exec_stmts
PG_CATCH // 出错,跳转到catch
...
exec_exception_cleanup // 开始回滚
XactCleanExceptionSubTransaction
进入exec_exception_cleanup时事务堆栈:
subTransactionId = 6 name = "sp5"
subTransactionId = 5 name = "" → 异常子事务(内层函数自动创建)
subTransactionId = 4 name = "sp4"
subTransactionId = 3 name = "" → 异常子事务(外层函数自动创建)
subTransactionId = 2 name = "sp3"
subTransactionId = 1 name = "" 基础事务
进入XactCleanExceptionSubTransaction函数,函数入参为5。
注意这里只释放子事务资源,不调整事务堆栈!!
XactCleanExceptionSubTransaction
AbortSubTxnRuntimeContext // 传入5 释放运行时资源(不关注)
while (s->subTransactionId >= head && s->parent != NULL)
// 注意第一次循环s是第5层:内层函数的异常子事务
// 注意第二次循环s是第6层:sp5的子事务
// 开始正常释放子事务资源
// 注意这里只释放子事务资源,不调整事务堆栈!!
// 释放完 资源状态??
释放完事务状态
subTransactionId = 6 name = "sp5"
subTransactionId = 5 name = "" → 异常子事务(内层函数自动创建)
subTransactionId = 4 name = "sp4"
subTransactionId = 3 name = "" → 异常子事务(外层函数自动创建)
subTransactionId = 2 name = "sp3"
subTransactionId = 1 name = "" 基础事务
econtext堆栈
(gdb) p *u_sess->plsql_cxt.simple_econtext_stack
// xact_subxid = 5 的被释放了,剩下四条
$18 = {stack_econtext = 0x7f17085dca40, xact_subxid = 4, statckEntryId = 4, next = 0x7f17085e3b70}
$19 = {stack_econtext = 0x7f17085dc7d0, xact_subxid = 4, statckEntryId = 3, next = 0x7f17085e3b08}
$20 = {stack_econtext = 0x7f17085dc560, xact_subxid = 3, statckEntryId = 2, next = 0x7f17085e3840}
$21 = {stack_econtext = 0x7f17085dc288, xact_subxid = 2, statckEntryId = 1, next = 0x0}
SPI堆栈
p u_sess->SPI_cxt._stack[0]
$25 = {processed = 0, connectSubid = 2}
$26 = {processed = 0, connectSubid = 4}
资源归属关系:s=savepoint subtransaction、e=exception subtransaction
subTransaction 1o 2s 3e 4s 5e 6s
econtext 4
econtext 4
econtext 3
econtext 2
SPI_cxt 4
SPI_cxt 2
exec_stmt_block
exec_exception_begin(estate, &excptContext) // 起一层子事务
PG_TRY
plpgsql_create_econtext
exec_stmts
PG_CATCH // 出错,跳转到catch
...
exec_exception_cleanup // 开始回滚
XactCleanExceptionSubTransaction
...
exec_savepoint_rollback(estate, txnName "sp5")
这一步把sp5执行了rollbackto,然后又把sp5重建出来了,事务堆栈不变。