1 背景

PostgreSQL中的存储过程不支持使用savepoint、rollback to。原因是PG的存储过程中，异常处理使用子事务来实现的，也就是一旦发生异常，当前procedure的begin块中执行过的所有语句都会直接回滚：

procedurebegininsert into tbl values (1);x = 1 / 0;   -- 异常发生insert into tbl values (2);exception....end;

当异常发生后，第二条insert没有执行到就跳转了，比较容易理解；但是第一条insert会被回滚，这种行为是PG特有的，和Oracle是有区别的，Oracle中异常发生只会跳转，不会回滚也不存在子事务。

2 PLpgSQL中实现检查点的困难

1. 由于PG异常处理本身会启动子事务，就等于启动检查点了，那么如果在begin块中再执行savepoint，会把PG的异常检查点从 事务堆栈顶层 向下压一层， 那么如果异常没发生、或发生了，都需结束异常检查点，但异常检查点现在不在事务堆栈顶层，回滚或提交都会比较困难。
2. 造成困难的原因主要是PG的大部分资源都会绑定事务ID，跟随事务释放，调整事务堆栈的位置需要一并调整资源与事务的绑定关系。

3 openGauss如何解决？

总结

• 场景一：对于正常结束的block，如果执行过savepoint，则异常子事务在savepoint子事务下面一层，高斯的处理是不提交异常子事务，就放在事务堆栈中。
• 场景二：对于正常结果的block，如果执行rollback to函数外层savepoint，则已经把异常子事务提交了，高斯的处理是不提交异常子事务，异常子事务已经被rollback to路过回滚了。

还有一个最最重要的区别：openGassdb的子事务重新生成后，子事务ID不变，和nestinglevel基本是保持一致的。

4 分析（可忽略）

问题一：正常执行结果如何跨过savepoint提交异常子事务。

CASE1

CREATE or replace PROCEDURE p_outter()
ascarry float;
beginINSERT 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 THENRAISE 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;

STEP1：创建sp4前事务状态

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}

STEP2：创建完sp4、rollback to sp4前事务状态

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}

STEP3：rollback to sp4

回滚流程

SPI_savepoint_rollbackRollbackToSavepointCommitTransactionCommand(STP_commit == true)case TBLOCK_SUBRESTARTAbortSubTransaction(STP_commit)AtSubAbort_XXXCleanupSubTransaction(STP_commit)PopTransactionBeginInternalSubTransaction    // 重启子事务

rollback分两步：

1. RollbackToSavepoint改事务状态
2. CommitTransactionCommand完成回滚

回滚完成后事务状态

subTransactionId = 4.    name = "sp4"
subTransactionId = 3     name = ""      →   异常子事务（内层函数自动创建）
subTransactionId = 2     name = "sp3"
subTransactionId = 1     name = ""          基础事务

STEP4: 正常执行结束，提交异常子事务，注意如执行过检查点，则异常子事务不做提交！

exec_stmt_blockexec_exception_begin(estate, &excptContext)PG_TRYexec_stmtsexec_exception_end

exec_exception_end函数

exec_exception_end
// 如果没检查点在block中if (context->curExceptionCounter == u_sess->SPI_cxt.portal_stp_exception_counter && GetCurrentTransactionName() == NULL)// 可以直接提交SPI_savepoint_releasestp_cleanup_subxact_resourceelse// 不提交！

事务堆栈

subTransactionId = 4.    name = "sp4"
subTransactionId = 3     name = ""      →   异常子事务（内层函数自动创建）
subTransactionId = 2     name = "sp3"
subTransactionId = 1     name = ""          基础事务

问题二：回滚到最外层的savepoint，把路过的异常子事务回滚了怎么办？

CASE1

CREATE or replace PROCEDURE p_outter()
ascarry float;
beginINSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (4);rollback to sp3;
EXCEPTION WHEN others THENRAISE 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;

STEP1：rollback to sp3

执行前

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_releasestp_cleanup_subxact_resourceelse// 不提交！

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不需要清理了。

5 复杂场景（有问题，不在测试）

drop table if exists t_plpgsql_transaction_20221222_01;
create table t_plpgsql_transaction_20221222_01(a int);CREATE or replace PROCEDURE p_outter()
ascarry float;
beginINSERT INTO t_plpgsql_transaction_20221222_01 (a) VALUES (4);savepoint sp4;call p_inner();
EXCEPTION WHEN others THENRAISE NOTICE 'in exception';
end;
/CREATE or replace PROCEDURE p_inner()
ascarry float;
beginINSERT 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 THENRAISE 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()异常子事务回滚流程：

3.1 断点分析：内层函数刚进入PG_TRY

exec_stmt_blockexec_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

3.2 断点分析：内层函数发生异常开始回滚，进入exec_exception_cleanup：第一步XactCleanExceptionSubTransaction释放子事务资源

总结：把顶层的sp5的子事务和异常子事务的资源都释放了。

exec_stmt_blockexec_exception_begin(estate, &excptContext)		// 起一层子事务PG_TRYplpgsql_create_econtextexec_stmtsPG_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。

注意这里只释放子事务资源，不调整事务堆栈！！

XactCleanExceptionSubTransactionAbortSubTxnRuntimeContext			// 传入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

3.3 断点分析：进入exec_exception_cleanup：第二步主动回滚顶层用户检查点exec_savepoint_rollback

exec_stmt_blockexec_exception_begin(estate, &excptContext)		// 起一层子事务PG_TRYplpgsql_create_econtextexec_stmtsPG_CATCH										// 出错，跳转到catch...exec_exception_cleanup						// 开始回滚XactCleanExceptionSubTransaction...exec_savepoint_rollback(estate, txnName "sp5")

这一步把sp5执行了rollbackto，然后又把sp5重建出来了，事务堆栈不变。