postgresql - Postgres 提交是否可以存在于具有异常 block 的过程中？

Question

我很难理解 Postgres 中的事务。我有一个程序可能会遇到异常。在程序的某些部分，我可能希望到目前为止提交我的工作，以便在出现异常时不会回滚。

我想在过程结束时有一个异常处理 block ，我可以在其中捕获异常并将来自异常的信息插入到日志记录表中。

我已将问题归结为一个简单的过程，如下所示，它在 PostgreSQL 11.2 上失败了

2D000 cannot commit while a subtransaction is active
PL/pgSQL function x_transaction_try() line 6 at COMMIT

    drop procedure if exists x_transaction_try;
    create or replace procedure x_transaction_try()
        language plpgsql
    as $$
    declare
    begin
         raise notice 'A';
         -- TODO A: do some insert or update that I want to commit no matter what
         commit;
         raise notice 'B';
         -- TODO B: do something else that might raise an exception, without rolling
         -- back the work that we did in "TODO A".
    exception when others then
      declare
        my_ex_state text;
        my_ex_message text;
        my_ex_detail text;
        my_ex_hint text;
        my_ex_ctx text;
      begin
          raise notice 'C';
          GET STACKED DIAGNOSTICS
            my_ex_state   = RETURNED_SQLSTATE,
            my_ex_message = MESSAGE_TEXT,
            my_ex_detail  = PG_EXCEPTION_DETAIL,
            my_ex_hint    = PG_EXCEPTION_HINT,
            my_ex_ctx     = PG_EXCEPTION_CONTEXT
          ;
          raise notice '% % % % %', my_ex_state, my_ex_message, my_ex_detail, my_ex_hint, my_ex_ctx;
          -- TODO C: insert this exception information in a logging table and commit
      end;
    end;
    $$;

    call x_transaction_try();

为什么这个存储过程不起作用？为什么我们从未看到 raise notice 'B' 的输出，而是进入了异常 block ？是否可以使用 Postgres 11 存储过程执行我上面描述的操作？

编辑:这是一个完整的代码示例。将上述完整代码示例(包括create procedure 和call 语句)粘贴到一个sql 文件中，然后在Postgres 11.2 数据库中运行它以重现。期望的输出是函数打印 A 然后 B，但是它打印 A 然后 C带有异常信息。

另请注意，如果您注释掉所有异常处理 block ，使该函数根本不捕获异常，那么该函数将输出“A”然后“B”而不会发生异常。这就是为什么我将问题命名为“Postgres Commit Exist in Procedure that has an Exception Block?”的方式吗？

Answer 1

PL/pgSQL 的语义 error handling规定:

When an error is caught by an EXCEPTION clause ... all changes to persistent database state within the block are rolled back.

这是使用子事务实现的，与savepoints基本相同.换句话说，当您运行以下 PL/pgSQL 代码时:

BEGIN
  PERFORM foo();
EXCEPTION WHEN others THEN
  PERFORM handle_error();
END

...实际发生的事情是这样的:

BEGIN
  SAVEPOINT a;
  PERFORM foo();
  RELEASE SAVEPOINT a;
EXCEPTION WHEN others THEN
  ROLLBACK TO SAVEPOINT a;
  PERFORM handle_error();
END

block 中的 COMMIT 将完全破坏它；您的更改将成为永久性的，保存点将被丢弃，并且异常处理程序将无法回滚。因此，在此上下文中不允许提交，并且尝试执行 COMMIT 将导致“子事务处于事件状态时无法提交”错误。

这就是为什么您会看到您的过程跳转到异常处理程序而不是运行 raise notice 'B' 的原因:当它到达 commit 时，它会抛出一个错误，并且处理程序捕获它。

不过，这很容易变通。 BEGIN ... END block 可以嵌套，只有带有 EXCEPTION 子句的 block 涉及设置保存点，因此您可以将提交前后的命令包装在自己的异常中处理程序:

create or replace procedure x_transaction_try() language plpgsql
as $$
declare
  my_ex_state text;
  my_ex_message text;
  my_ex_detail text;
  my_ex_hint text;
  my_ex_ctx text;
begin
  begin
    raise notice 'A';
  exception when others then
    raise notice 'C';
    GET STACKED DIAGNOSTICS
      my_ex_state   = RETURNED_SQLSTATE,
      my_ex_message = MESSAGE_TEXT,
      my_ex_detail  = PG_EXCEPTION_DETAIL,
      my_ex_hint    = PG_EXCEPTION_HINT,
      my_ex_ctx     = PG_EXCEPTION_CONTEXT
    ;
    raise notice '% % % % %', my_ex_state, my_ex_message, my_ex_detail, my_ex_hint, my_ex_ctx;
  end;

  commit;

  begin
    raise notice 'B';
  exception when others then
    raise notice 'C';
    GET STACKED DIAGNOSTICS
      my_ex_state   = RETURNED_SQLSTATE,
      my_ex_message = MESSAGE_TEXT,
      my_ex_detail  = PG_EXCEPTION_DETAIL,
      my_ex_hint    = PG_EXCEPTION_HINT,
      my_ex_ctx     = PG_EXCEPTION_CONTEXT
    ;
    raise notice '% % % % %', my_ex_state, my_ex_message, my_ex_detail, my_ex_hint, my_ex_ctx;
  end;      
end;
$$;

不幸的是，它确实会导致错误处理程序中出现大量重复，但我想不出一个很好的方法来避免它。