plpgsql中的隐式子事务

在事务块中调用存在异常的函数

CREATE TABLE tmp(id int);

CREATE OR REPLACE FUNCTION demo_plpgsql_subxact()
RETURNS void AS $$
BEGIN                        
    INSERT INTO tmp VALUES (-2);
    INSERT INTO tmp VALUES (1/0);  -- error
EXCEPTION
    WHEN division_by_zero THEN
        RAISE INFO '%', SQLERRM;
        INSERT INTO tmp VALUES (-3);            
END;
$$ LANGUAGE plpgsql;

BEGIN; -- 开启事务
INSERT INTO tmp VALUES (-1);
ELECT demo_plpgsql_subxact(); -- 调用函数
select * from tmp;
COMMIT;

truncate tmp;

-- 等价于
BEGIN; -- 开启事务
INSERT INTO tmp VALUES (-1);
SAVEPOINT exception;  -- 保存点
INSERT INTO tmp VALUES (-2);
INSERT INTO tmp VALUES (1/0);  -- error
ROLLBACK TO SAVEPOINT exception; -- 异常回滚到保存点
INSERT INTO tmp VALUES (-3); 
SELECT * FROM tmp;
COMMIT;

运行结果

postgres@zxm-VMware-Virtual-Platform:~$ psql
psql (16.10)
Type "help" for help.

postgres=# CREATE TABLE tmp(id int);
CREATE TABLE
postgres=# CREATE OR REPLACE FUNCTION demo_plpgsql_subxact()
postgres-# RETURNS void AS $$
postgres$# BEGIN  
postgres$#     INSERT INTO tmp VALUES (-2);
postgres$#     INSERT INTO tmp VALUES (1/0);  -- error
postgres$# EXCEPTION
postgres$#     WHEN division_by_zero THEN
postgres$#                 RAISE INFO '%', SQLERRM;
postgres$#         INSERT INTO tmp VALUES (-3); 
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# BEGIN;
BEGIN
postgres=*# INSERT INTO tmp VALUES (-1);
INSERT 0 1
postgres=*# SELECT demo_plpgsql_subxact(); -- 调用函数
INFO:  division by zero
 demo_plpgsql_subxact 
----------------------
 
(1 row)

postgres=*# select * from tmp;
 id 
----
 -1
 -3
(2 rows)

postgres=*# COMMIT;
COMMIT
postgres=# truncate tmp;
TRUNCATE TABLE
postgres=# BEGIN;
BEGIN
postgres=*# INSERT INTO tmp VALUES (-1);
INSERT 0 1
postgres=*# SAVEPOINT exception;
SAVEPOINT
postgres=*# INSERT INTO tmp VALUES (-2);
INSERT 0 1
postgres=*# INSERT INTO tmp VALUES (1/0);  -- error
ERROR:  division by zero
postgres=!# ROLLBACK TO SAVEPOINT exception;
ROLLBACK
postgres=*# INSERT INTO tmp VALUES (-3); 
INSERT 0 1
postgres=*# COMMIT;
COMMIT
postgres=# SELECT * FROM tmp;
 id 
----
 -1
 -3
(2 rows)

postgres=#

在事务块中，调用不存在异常的函数

TRUNCATE tmp;

CREATE OR REPLACE FUNCTION demo_plpgsql_subxact()
RETURNS void AS $$
BEGIN                        
    INSERT INTO tmp VALUES (-2);
    INSERT INTO tmp VALUES (-3);
EXCEPTION
    WHEN division_by_zero THEN
        RAISE INFO '%', SQLERRM;        
END;
$$ LANGUAGE plpgsql;

BEGIN; -- 开启事务块
INSERT INTO tmp VALUES (-1);
select demo_plpgsql_subxact();
INSERT INTO tmp VALUES (-4);
SELECT * FROM tmp;
COMMIT;


TRUNCATE tmp;
-- 等价于
BEGIN; -- 开启事务块
INSERT INTO tmp VALUES (-1);
SAVEPOINT exception;
INSERT INTO tmp VALUES (-2);
INSERT INTO tmp VALUES (-3);
RELEASE SAVEPOINT exception;
INSERT INTO tmp VALUES (-4);
SELECT * FROM tmp;
COMMIT;

运行结果

postgres@zxm-VMware-Virtual-Platform:~$ psql
psql (16.10)
Type "help" for help.

postgres=# TRUNCATE tmp;
TRUNCATE TABLE
postgres=# CREATE OR REPLACE FUNCTION demo_plpgsql_subxact()
RETURNS void AS $$
BEGIN                        
    INSERT INTO tmp VALUES (-2);
    INSERT INTO tmp VALUES (-3);
EXCEPTION
    WHEN division_by_zero THEN
        RAISE INFO '%', SQLERRM;        
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# BEGIN; -- 开启事务块
BEGIN
postgres=*# INSERT INTO tmp VALUES (-1);
INSERT 0 1
postgres=*# select demo_plpgsql_subxact();
 demo_plpgsql_subxact 
----------------------
 
(1 row)

postgres=*# INSERT INTO tmp VALUES (-4);
INSERT 0 1
postgres=*# SELECT * FROM tmp;
 id 
----
 -1
 -2
 -3
 -4
(4 rows)

postgres=*# COMMIT;
COMMIT
postgres=# TRUNCATE tmp;
TRUNCATE TABLE
postgres=# -- 等价于
postgres=# BEGIN; -- 开启事务块
BEGIN
postgres=*# INSERT INTO tmp VALUES (-1);
INSERT 0 1
postgres=*# SAVEPOINT exception; -- 开启子事务
SAVEPOINT
postgres=*# INSERT INTO tmp VALUES (-2);
INSERT 0 1
postgres=*# INSERT INTO tmp VALUES (-3);
INSERT 0 1
postgres=*# RELEASE SAVEPOINT exception;
RELEASE
postgres=*# INSERT INTO tmp VALUES (-4);
INSERT 0 1
postgres=*# SELECT * FROM tmp;
 id 
----
 -1
 -2
 -3
 -4
(4 rows)

postgres=*# COMMIT;
COMMIT
postgres=#

没有exception则不会触发子事务的动作，部分exec_stmt_block代码片段如下

static int
exec_stmt_block(PLpgSQL_execstate *estate, PLpgSQL_stmt_block *block)
{
  // initialize 
  if (block->exceptions)
  {
    BeginInternalSubTransaction(NULL);  // 开启子事务

    PG_TRY();
    {
      /* Run the block's statements */
      rc = exec_stmts(estate, block->body); // 执行相关操作

      /* Commit the inner transaction, return to outer xact context */
      ReleaseCurrentSubTransaction(); // 释放子事务
    }
    PG_CATCH();
    {

      /* Abort the inner transaction */
      RollbackAndReleaseCurrentSubTransaction(); // 发生了异常回滚子事务

      // 异常匹配和处理
      foreach(e, block->exceptions->exc_list)
      {
        PLpgSQL_exception *exception = (PLpgSQL_exception *) lfirst(e);
        if (exception_matches_conditions(edata, exception->conditions))
        {
          rc = exec_stmts(estate, exception->action); // exception块中的其余操作
          break;
        }
      }
    }
    PG_END_TRY();
  }
  else
  {
    /*
     * Just execute the statements in the block's body
     */
    estate->err_text = NULL;
    // 没有exception块 执行此处 不会开启子事务
    rc = exec_stmts(estate, block->body);
  }
  // ...
}