Postgresql中procedure支持事务语法（实例&分析）

mingjie

发布于 2022-07-14 13:53:50

5880

发布于 2022-07-14 13:53:50

相关《Postgresql源码（60）事务系统总结》 https://www.postgresql.org/docs/current/plpgsql-transactions.html

实例1：PROCEDURE内部可以使用提交、回滚语句

drop table test1;
create table test1 (a int);
CREATE or replace PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test1 (a) VALUES (2);
    COMMIT;
    INSERT INTO test1 (a) VALUES (3);
    ROLLBACK;
END;
$$;

CALL transaction_test1();
select * from test1;
 a 
---
 2

commit语句都做了什么？

执行上层的事务状态流转函数：

执行CommitTransactionCommand
执行StartTransactionCommand

static int
exec_stmt_commit(PLpgSQL_execstate *estate, PLpgSQL_stmt_commit *stmt)
{
	if (stmt->chain)
		SPI_commit_and_chain();
	else
	{
		SPI_commit();
		SPI_start_transaction();
	}
  ...
	return PLPGSQL_RC_OK;
}

rollback语句都做了什么？

执行上层的事务状态流转函数：

执行AbortCurrentTransaction
执行StartTransactionCommand

static int
exec_stmt_rollback(PLpgSQL_execstate *estate, PLpgSQL_stmt_rollback *stmt)
{
	if (stmt->chain)
		SPI_rollback_and_chain();
	else
	{
		SPI_rollback();
		SPI_start_transaction();
	}
  ...
	return PLPGSQL_RC_OK;
}

实例2：PROCEDURE内报错自动回滚已执行的语句

drop table test1;
create table test1 (a int);
CREATE or replace PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test1 (a) VALUES (2);
    INSERT INTO test1 (a) VALUES (3);
    RAISE division_by_zero;
END;
$$;

CALL transaction_test1();
ERROR:  division_by_zero
CONTEXT:  PL/pgSQL function transaction_test1() line 5 at RAISE

select * from test1;
 a 
---
(0 rows)

事务是如何回滚的？

// 触发ereport ERROR
RAISE division_by_zero;  

// jump 到：
PostgresMain
  if (sigsetjmp(local_sigjmp_buf, 1) != 0)
    AbortCurrentTransaction()

走AbortCurrentTransaction触发回滚动作

实例3：PROCEDURE内报错不会滚已经提交的语句

drop table test1;
create table test1 (a int);
CREATE or replace PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test1 (a) VALUES (2);
    COMMIT;
    INSERT INTO test1 (a) VALUES (3);
    RAISE division_by_zero;
END;
$$;

CALL transaction_test1();
select * from test1;
 a 
---
 2
(1 row)

参考实例1的分析结果，commit执行完了会新起一个事务，后面的保存不影响前面已经提交的事务了。

实例4：PROCEDURE包含EXCEPTION的语句块不支持COMMIT

drop table test1;
create table test1 (a int);
CREATE or replace PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test1 (a) VALUES (2);
    COMMIT;
    INSERT INTO test1 (a) VALUES (3);
    RAISE division_by_zero;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
END;
$$;

CALL transaction_test1();
ERROR:  cannot commit while a subtransaction is active
CONTEXT:  PL/pgSQL function transaction_test1() line 4 at COMMIT

select * from test1;
 a 
---
(0 rows)

如果走EXCEPTION语句块的话，会把整个block包在一个子事务里面，子事务里面不支持执行commit。

exec_stmt_block
  ...
  if (block->exceptions)
    // 启了一个子事务
    BeginInternalSubTransaction
    PG_TRY()
      exec_stmts
    PG_CATCH()
      // 如果有异常，把整个子事务结束掉
      RollbackAndReleaseCurrentSubTransaction

实例5：function是原子的不支持部分提交

drop table test1;
create table test1 (a int);
CREATE or replace function transaction_test1()
returns void
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test1 (a) VALUES (2);
    COMMIT;
    INSERT INTO test1 (a) VALUES (3);
    ROLLBACK;
END;
$$;

select transaction_test1();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function transaction_test1() line 4 at COMMIT

原因：

执行函数前，初始化SPI系统

如果传入的fcinfo->context是一个call context就配置nonatomic

plpgsql_call_handler
  nonatomic = fcinfo->context 
                && IsA(fcinfo->context, CallContext) 
                && !castNode(CallContext, fcinfo->context)->atomic;
  SPI_connect_ext(nonatomic ? SPI_OPT_NONATOMIC : 0))
    _SPI_current->atomic = (options & SPI_OPT_NONATOMIC ? false : true);

如果是call procedure语句

_SPI_current->atomic = false;

所以在执行exec_stmt_commit时，不会报错。

exec_stmt_commit
  SPI_commit
    _SPI_commit
      	if (_SPI_current->atomic)
		      ereport(ERROR,
				     (errcode(ERRCODE_INVALID_TRANSACTION_TERMINATION),
				     errmsg("invalid transaction termination")));

如果是function会直接报错退出。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-07-06，如有侵权请联系 cloudcommunity@tencent.com 删除

postgresql