函数rollback导致变量赋值结果丢失问题分析

在postgresql基础上以开启子事务的方式来实现函数的commit/rollback,经测试发现函数exception块中的变量复制在rollback之后会失效，函数demo如下：

create or replace function func_test_001() return varchar2 as
    l_result varchar2(900);
begin
    raise exception no_data_found;
    exception
        when no_data_found then
            l_result := 'record 1st not found';
            rollback;
            l_result := 'record 2nd not found';
            rollback;
            l_result := 'record 3rd not found';
            rollback;
            return l_result;
        when others then
            l_result := 'error occurred: ' || sqlerrm;
            rollback;
            return l_result;

    return l_result;
end;
/

首次测试运行结果OK,如下

declare
    ret varchar2(900);
begin
    ret := func_test_001();
    dbms_output.put_line('result: ' || ret);
end;
/
result: record 3rd not found

之后的测试运行结果返回值失效，如下

 \x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F~\x7F\x7F\x7F\x7F\x7F
\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x
7F\x7F\x7F\x7F\x7F\x7F\x7F
(1 row)

分析事务源代码流程如下：

......
RollbackAndReleaseCurrentSubTransaction()
|__ CleanupSubTransaction()
	|__ AtSubCleanup_Memory()
		|__ MemoryContextDelete(s->curTransactionContext)

分析可知，l_result 在exception中的复制实在subxact local memory contexts中的，而ROLLBACK会在AtSubCleanup_Memory函数中清理子事务上下文导致变量值失效，因RollbackAndReleaseCurrentSubTransaction()广泛应用在postgresql的各个地方，所以复制一份如上四个函数出来重新修改逻辑来适配函数回滚需求。
去掉MemoryContextDelete(s->curTransactionContext)逻辑，回滚子事务时不释放子事务内存上下文，待到最终主事务结束一并释放父子内存上下文。
编译测试通过，暴力测试案例如下：

create or replace function func_test_002(i int) return varchar2 as
    l_result varchar2(900);
begin
    raise exception no_data_found;
    exception
        when no_data_found then
            l_result := 'record 1st not found ' || i;
            rollback;
            return l_result;
        when others then
            l_result := 'error occurred: ' || sqlerrm;
            rollback;
            return l_result;

    return l_result;
end;
/

declare
    ret varchar2(900);
begin
    for i in 1 .. 10000
    loop
        ret := func_test_002(i);
        if mod(i,1000) = 0 then
            dbms_output.put_line('result: ' || ret);
        end if;
    end loop;
end;
/
--10000测测试,运行结果
result: record 1st not found 1000
result: record 1st not found 2000
result: record 1st not found 3000
result: record 1st not found 4000
result: record 1st not found 5000
result: record 1st not found 6000
result: record 1st not found 7000
result: record 1st not found 8000
result: record 1st not found 9000
result: record 1st not found 10000