Postgresql中不支持事务块中调用plpgsql回滚（多层exception、事务块有检查点）
postgresql事务 支持 调用 回滚 Exception 多层 检查点
2023-06-13 09:15:23 时间
前言
- 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 $$
DECLARE
carry float;
BEGIN
INSERT INTO tbl221 (a) VALUES (1);
BEGIN
rollback;
EXCEPTION WHEN others THEN
RAISE NOTICE 'in exception exception';
INSERT INTO tbl221 (a) VALUES (100);
END;
INSERT INTO tbl221 (a) VALUES (4);
EXCEPTION WHEN others THEN
RAISE 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 $$
DECLARE
carry float;
BEGIN
call p_transaction();
EXCEPTION WHEN others THEN
RAISE 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 = 4
CurrentTransactionState->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 <spi_printtupDR>) 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 <spi_printtupDR>, 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 <spi_printtupDR>, 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:2002 回滚前后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();
else
SPI_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_econtext
exec_stmts
exec_stmt_block （Caller的内层block走try catch进入exec_stmts）调用plpgsql_create_econtext
exec_stmt_call
ExecuteCallStmt
exec_stmt_block （Callee的外层block直接进入exec_stmts）不调用plpgsql_create_econtext
exec_stmts
exec_stmt_block （Callee的内层block走try catch进入exec_stmts）调用plpgsql_create_econtext
exec_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 = 0x28d4d38simple_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相关文章
- Oracle与Postgresql在PLSQL内事务回滚的重大差异
- Postgresql源码（69）常规锁简单分析
- Windows 系统 PostgreSQL 手工安装配置方法
- PostgreSQL如何判断字符串是否包含目标字符串的方法
- PostgreSQL DBA 面试宝典 面试题 52道（含10大常见题）
- PostgreSQL查看版本信息的操作
- Postgresql ALTER语句常用操作小结
- Windows下PostgreSQL安装图解
- PostgreSQL 2F000: sql_routine_exception 报错 故障修复 远程处理
- PostgreSQL 38001: containing_sql_not_permitted 报错 故障修复 远程处理
- 『PostgreSQL：强大而友好的数据库』（postgresql特点）
- 比较PostgreSQL与Oracle的异同（postgresql和oracle）
- 招聘PostgreSQL攻城狮：让你的技能大放异彩（postgresql招聘）
- 的性能优势Postgresql在性能方面的优势——德哥的深度解读（德哥postgresql）
- 中跳出跳出PostgreSQL循环的实践方法（postgresql循环）
- 解决postgresql软件卸载问题（postgresql卸载）
- PostgreSQL启动指南：快速学会如何启动PostgreSQL数据库。（postgresql启动）
- PostgreSQL测试：深入了解数据库运行状况（postgresql测试）
- Postgresql参数优化：提升性能的技巧（postgresql参数）
- PostgreSQL外键：更好的数据库关联性（postgresql外键）
- 如何在PostgreSQL中创建新用户（postgresql创建用户）
- PostgreSQL连接的实践与思考（postgresql链接）
- PostgreSQL的传奇发展历史（postgresql历史）
- Postgresql数据库实现事务回滚技术（postgresql回滚）
- php连接与操作PostgreSQL数据库的方法