PostgreSQL存储过程中事务与捕获异常的问题

晟数学苑 2021-11-16

4907

点击蓝字阅读更多干货

PostgreSQL11开始支持了存储过程的写法，同时也允许了在存储过程中嵌入事务。今天刚好碰到一个相关的问题。

do language plpgsql $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO t1(id) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
EXCEPTION 
WHEN others THEN  
insert into t1 values(100);
END;
$$;

上面这段存储过程大家可以先想想执行完会是什么结果？

从字面看显然就是把i % 2 = 0的数插入到t1表中了，但事实却是：

bill=# do language plpgsql $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO t1(id) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
EXCEPTION 
WHEN others THEN  
insert into t1 values(100);
END;
$$;
DO


bill=# select * from t1;
 id
-----
 100
(1 row)

竟然上面的循环部分全部跳过了，直接执行了异常捕获部分的语句。

为什么会这样呢？可能比较熟悉的人会知道，PG的存储过程中捕获异常和commit/rollback是会冲突的，如下：

bill=# do language plpgsql $$
bill$#  begin
bill$#      begin
bill$#          insert into t1(id) values (1);
bill$#          commit;
bill$#          insert into t1(id) values (1/0);
bill$#          rollback;
bill$#      exception
bill$#          when division_by_zero then
bill$#              raise notice 'caught division_by_zero';
bill$#      end;
bill$#  end;
bill$#  $$;
ERROR:  cannot commit while a subtransaction is active
CONTEXT:  PL/pgSQL function inline_code_block line 5 at COMMIT

那么问题来了，为什么前面的语句没有报错呢，而是执行了异常部分的语句？

通过对比我们发现，关键在于异常捕获的对象名称。在PG中特殊的条件名OTHERS匹配除了QUERY_CANCELED和ASSERT_FAILURE之外的所有错误类型，但是这又有什么不同呢？

经过跟踪发现两个SQL调用的堆栈都是一样的，而且执行的结果也是一样：

(lldb) b _SPI_commit
Breakpoint 1: where = postgres`_SPI_commit + 10 at spi.c:225:6, address = 0x0000000101b4a52a
(lldb) c
Process 62848 resuming
postgres was compiled with optimization - stepping may behave oddly; variables may not be available.
Process 62848 stopped
* thread #1, queue = 'com.apple.main-thread', stop reason = breakpoint 1.1
    frame #0: 0x0000000101b4a52a postgres`_SPI_commit(chain=<unavailable>) at spi.c:225:6 [opt]
   222  {
   223    MemoryContext oldcontext = CurrentMemoryContext;
   224
-> 225    if (_SPI_current->atomic)
   226      ereport(ERROR,
   227          (errcode(ERRCODE_INVALID_TRANSACTION_TERMINATION),
   228           errmsg("invalid transaction termination")));
Target 0: (postgres) stopped.
(lldb) n
Process 62848 stopped
* thread #1, queue = 'com.apple.main-thread', stop reason = step over
    frame #0: 0x0000000101b4a543 postgres`_SPI_commit(chain=<unavailable>) at spi.c:239:6 [opt]
   236     * this restriction would have to be refined or the check possibly be
   237     * moved out of SPI into the PLs.
   238     */
-> 239    if (IsSubTransaction())
   240      ereport(ERROR,
   241          (errcode(ERRCODE_INVALID_TRANSACTION_TERMINATION),
   242           errmsg("cannot commit while a subtransaction is active")));
Target 0: (postgres) stopped.
(lldb) n
Process 62848 stopped
* thread #1, queue = 'com.apple.main-thread', stop reason = step over
    frame #0: 0x0000000101b4a5ad postgres`_SPI_commit(chain=<unavailable>) at spi.c:240:3 [opt]
   237     * moved out of SPI into the PLs.
   238     */
   239    if (IsSubTransaction())
-> 240      ereport(ERROR,
   241          (errcode(ERRCODE_INVALID_TRANSACTION_TERMINATION),
   242           errmsg("cannot commit while a subtransaction is active")));
   243
Target 0: (postgres) stopped.
(lldb) p IsSubTransaction
(bool (*)()) $0 = 0x00000001019fe8e0 (postgres`IsSubTransaction at xact.c:4729)

按理说EXCEPTION WHEN others最终也应该是输出"cannot commit while a subtransaction is active"的错误，可却没有报错，而是执行了错误的命令，这就十分奇怪了。

总结：

在PostgreSQL的存储过程中，事务的commit/rollback是会和异常捕获冲突的，会抛出"cannot commit while a subtransaction is active"的错误。

但是当异常捕获的选项为OTHERS时，竟然没有抛出错误，而是直接执行了异常捕获部分的内容。目前看来像是个BUG，后续再继续研究下。

觉得内容还不错的话，给我点个“在看”呗

postgresql

文章转载自晟数学苑，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL存储过程中事务与捕获异常的问题

评论