前言

• Postgresql使用子事务来实现EXCEPTION的功能，即在进入EXCEPTION的存储过程前，会自动起一个子事务，如果发生了异常，则自动回滚子事务，达成EXCEPTION的效果。
• 那么如果在事务块内本身就带子事务（SAVEPOINT），在调用有EXCEPTION的存储过程，处理流程会有一些复杂。
• 目前下面代码中的rollback会直接报错不支持，但报错被exception掩盖了，所以后续的行为不再分析了。只是做一些记录。

案例

drop table if exists tbl221;
create table tbl221(a int);CREATE or replace PROCEDURE p_transaction()
LANGUAGE plpgsql
AS $$
DECLAREcarry float;
BEGININSERT INTO tbl221 (a) VALUES (1);BEGINrollback;EXCEPTION WHEN others THENRAISE NOTICE 'in exception exception';INSERT INTO tbl221 (a) VALUES (100);END;INSERT INTO tbl221 (a) VALUES (4);
EXCEPTION WHEN others THENRAISE NOTICE 'in exception';INSERT INTO tbl221 (a) VALUES (10);INSERT INTO tbl221 (a) VALUES (30);INSERT INTO tbl221 (a) VALUES (40);
END;
$$;CREATE or replace PROCEDURE p_transaction_caller()
LANGUAGE plpgsql
AS $$
DECLAREcarry float;
BEGINcall p_transaction();
EXCEPTION WHEN others THENRAISE NOTICE 'in caller exception';
END;
$$;begin;
INSERT INTO tbl221 (a) VALUES (100);
savepoint sp1;
savepoint sp2;
CALL p_transaction_caller();
select * from tbl221;

1 回滚前SPI的connectSubid与子事务subTransactionId对应关系

分析CALL p_transaction();的执行流程：

进入exec_stmt_rollback时：：重要：：

_SPI_stack[0]->connectSubid = 3
_SPI_stack[1]->connectSubid = 4CurrentTransactionState->subTransactionId = [1,2,        3,        4,     5,     6]| |         |         |      |      |base savepoint savepoint except except except|      |       |p_transaction_caller     p_transaction

• connectSubid：1是基础事务，2、3是两个检查点的事务，4是p_transaction_caller产生的子事务。第一次CALL在事务块内，所以使用子事务ID3，第二次CALL在p_transaction_caller的子事务内，所以使用子事务ID4。

当前堆栈

#0  exec_stmt_rollback (estate=0x7ffd0f874a40, stmt=0x28bc3d0) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:4964
#1  0x00007f29adcff1b5 in exec_stmts (estate=0x7ffd0f874a40, stmts=0x28bc3f0) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:2109
#2  0x00007f29adcfe8a8 in exec_stmt_block (estate=0x7ffd0f874a40, block=0x28e73a0) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:1783
#3  0x00007f29adcfeefa in exec_stmts (estate=0x7ffd0f874a40, stmts=0x28bc330) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:2005
#4  0x00007f29adcfe8a8 in exec_stmt_block (estate=0x7ffd0f874a40, block=0x28e7eb0) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:1783
#5  0x00007f29adcfeefa in exec_stmts (estate=0x7ffd0f874a40, stmts=0x28e7f50) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:2005
#6  0x00007f29adcfecd1 in exec_stmt_block (estate=0x7ffd0f874a40, block=0x28e7f00) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:1932
#7  0x00007f29adcfe4d6 in exec_toplevel_block (estate=0x7ffd0f874a40, block=0x28e7f00) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:1623
#8  0x00007f29adcfc45d in plpgsql_exec_function (func=0x27fda28, fcinfo=0x7ffd0f874d70, simple_eval_estate=0x0, simple_eval_resowner=0x0,  procedure_resowner=0x0, atomic=true) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:612
#9  0x00007f29add0e995 in plpgsql_call_handler (fcinfo=0x7ffd0f874d70) at ../pgsrc/src/pl/plpgsql/src/pl_handler.c:277
#10 0x000000000063331e in ExecuteCallStmt (stmt=0x28c0d78, params=0x0, atomic=true, dest=0xef0600 ) at ../pgsrc/src/backend/commands/functioncmds.c:2286
#11 0x000000000091aa4b in standard_ProcessUtility (pstmt=0x28c0c68, queryString=0x28e0398 "call p_transaction()", readOnlyTree=true,  context=PROCESS_UTILITY_QUERY, params=0x0, queryEnv=0x0, dest=0xef0600 , qc=0x7ffd0f875630) at ../pgsrc/src/backend/tcop/utility.c:858
#12 0x000000000091a1c5 in ProcessUtility (pstmt=0x28e0df8, queryString=0x28e0398 "call p_transaction()", readOnlyTree=true, context=PROCESS_UTILITY_QUERY,  params=0x0, queryEnv=0x0, dest=0xef0600 , qc=0x7ffd0f875630) at ../pgsrc/src/backend/tcop/utility.c:530
#13 0x0000000000728dab in _SPI_execute_plan (plan=0x28df928, options=0x7ffd0f875790, snapshot=0x0, crosscheck_snapshot=0x0, fire_triggers=true) at ../pgsrc/src/backend/executor/spi.c:2693
#14 0x0000000000725a76 in SPI_execute_plan_extended (plan=0x28df928, options=0x7ffd0f875790) at ../pgsrc/src/backend/executor/spi.c:721
#15 0x00007f29adcff4cd in exec_stmt_call (estate=0x7ffd0f875c60, stmt=0x28b81d8) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:2219
#16 0x00007f29adcfef4b in exec_stmts (estate=0x7ffd0f875c60, stmts=0x28b82e8) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:2017
#17 0x00007f29adcfe8a8 in exec_stmt_block (estate=0x7ffd0f875c60, block=0x28f20f8) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:1783
#18 0x00007f29adcfeefa in exec_stmts (estate=0x7ffd0f875c60, stmts=0x28f2198) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:2005
#19 0x00007f29adcfecd1 in exec_stmt_block (estate=0x7ffd0f875c60, block=0x28f2148) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:1932
#20 0x00007f29adcfe4d6 in exec_toplevel_block (estate=0x7ffd0f875c60, block=0x28f2148) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:1623
#21 0x00007f29adcfc45d in plpgsql_exec_function (func=0x27fe5a8, fcinfo=0x7ffd0f875f90, simple_eval_estate=0x0, simple_eval_resowner=0x0,  procedure_resowner=0x0, atomic=true) at ../pgsrc/src/pl/plpgsql/src/pl_exec.c:612
#22 0x00007f29add0e995 in plpgsql_call_handler (fcinfo=0x7ffd0f875f90) at ../pgsrc/src/pl/plpgsql/src/pl_handler.c:277
#23 0x000000000063331e in ExecuteCallStmt (stmt=0x27ae170, params=0x0, atomic=true, dest=0x27ae6a0) at ../pgsrc/src/backend/commands/functioncmds.c:2286
#24 0x000000000091aa4b in standard_ProcessUtility (pstmt=0x27ae220, queryString=0x27ad678 "CALL p_transaction_caller();", readOnlyTree=false,  context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x27ae6a0, qc=0x7ffd0f876a80) at ../pgsrc/src/backend/tcop/utility.c:858
#25 0x000000000091a1c5 in ProcessUtility (pstmt=0x27ae220, queryString=0x27ad678 "CALL p_transaction_caller();", readOnlyTree=false,  context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x27ae6a0, qc=0x7ffd0f876a80) at ../pgsrc/src/backend/tcop/utility.c:530
#26 0x0000000000918d0e in PortalRunUtility (portal=0x2859548, pstmt=0x27ae220, isTopLevel=true, setHoldSnapshot=false, dest=0x27ae6a0, qc=0x7ffd0f876a80) at ../pgsrc/src/backend/tcop/pquery.c:1158
#27 0x0000000000918f84 in PortalRunMulti (portal=0x2859548, isTopLevel=true, setHoldSnapshot=false, dest=0x27ae6a0, altdest=0x27ae6a0, qc=0x7ffd0f876a80) at ../pgsrc/src/backend/tcop/pquery.c:1315
#28 0x000000000091844b in PortalRun (portal=0x2859548, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x27ae6a0, altdest=0x27ae6a0,  qc=0x7ffd0f876a80) at ../pgsrc/src/backend/tcop/pquery.c:791
#29 0x000000000091180a in exec_simple_query (query_string=0x27ad678 "CALL p_transaction_caller();") at ../pgsrc/src/backend/tcop/postgres.c:1238
#30 0x0000000000916447 in PostgresMain (dbname=0x27e5308 "postgres", username=0x27aa1d8 "mingjie") at ../pgsrc/src/backend/tcop/postgres.c:4563
#31 0x00000000008523f0 in BackendRun (port=0x27db380) at ../pgsrc/src/backend/postmaster/postmaster.c:4396
#32 0x0000000000851d06 in BackendStartup (port=0x27db380) at ../pgsrc/src/backend/postmaster/postmaster.c:4124
#33 0x000000000084e444 in ServerLoop () at ../pgsrc/src/backend/postmaster/postmaster.c:1791
#34 0x000000000084dc6b in PostmasterMain (argc=1, argv=0x27a8180) at ../pgsrc/src/backend/postmaster/postmaster.c:1463
#35 0x000000000074a761 in main (argc=1, argv=0x27a8180) at ../pgsrc/src/backend/main/main.c:200

2 回滚前后estate->eval_econtext的状态变化

注意到SPI_rollback后，使用plpgsql_create_econtext重建了eval_econtext：

static int
exec_stmt_rollback(PLpgSQL_execstate *estate, PLpgSQL_stmt_rollback *stmt)
{if (stmt->chain)SPI_rollback_and_chain();elseSPI_rollback();estate->simple_eval_estate = NULL;estate->simple_eval_resowner = NULL;plpgsql_create_econtext(estate);return PLPGSQL_RC_OK;
}

这里还是在深层exec_stmt_rollback位置，在执行真的rollback前：

现在有两层CALL：
ExecuteCallStmt          exec_stmt_block         （Caller的外层block直接进入exec_stmts）不调用plpgsql_create_econtextexec_stmtsexec_stmt_block     （Caller的内层block走try catch进入exec_stmts）调用plpgsql_create_econtextexec_stmt_callExecuteCallStmt    exec_stmt_block          （Callee的外层block直接进入exec_stmts）不调用plpgsql_create_econtextexec_stmtsexec_stmt_block      （Callee的内层block走try catch进入exec_stmts）调用plpgsql_create_econtextexec_stmt_rollback

• 注意：eval_econtext生成函数plpgsql_create_econtext是在try catch的exec_stmt_block里面才会代用的：
• exec_stmt_block函数：

ROLLBACK前estate->eval_econtext的状态

当前代码位置：

外层Caller的estate：

estate = 0x7ffd0f875c60
estate->eval_econtext = 0x28d4b88

内层Callee的estate：

estate = 0x7ffd0f874a40
estate->eval_econtext = 0x28d4d38

simple_econtext_stack状态（这是一个eval_econtext链表，记录了所有申请的eval_econtext）

{stack_econtext = 0x28d4d38, xact_subxid = 6, next = 0x27d9b38} callee执行exec_stmts前新建的
{stack_econtext = 0x28d4ca8, xact_subxid = 5, next = 0x27d9b08} callee执行exec_stmts前新建的
{stack_econtext = 0x28d4c18, xact_subxid = 4, next = 0x27d9a48} ExecuteCallStmt里面新建的
{stack_econtext = 0x28d4b88, xact_subxid = 4, next = 0x27d9988} caller执行exec_stmt_call前新建的
{stack_econtext = 0x28d4aa8, xact_subxid = 3, next = 0x0}       plpgsql_estate_setup建的对应事务
CurrentTransactionState->subTransactionId = [1,2,        3,        4,     5,     6]| |         |         |      |      |base savepoint savepoint except except except|      |       |p_transaction_caller     p_transaction

和堆栈的关系

exec_stmt_block(trycatch)    old_eval_econtext=0x28d4ca8
exec_stmt_block(trycatch)    old_eval_econtext=0x28d4c18
exec_stmt_block(无)
ExecuteCallStmt
exec_stmt_block(trycatch)    old_eval_econtext=0x28d4aa8
exec_stmt_block(无)
ExecuteCallStmt