本文已参与「新人创作礼」活动，一起开启掘金创作之路
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，后续再继续研究下。
