具有异常块的过程中可以存在 Postgres 提交吗? [英] Can a Postgres Commit Exist in Procedure that has an Exception Block?

问题描述

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

I am having a difficult time understanding transactions in Postgres. I have a procedure that may encounter an exception. There are parts of the procedure where I might want to commit my work so-far so that it won't be rolled back if an exceptions ensues.

我希望在程序结束时有一个异常处理块，我可以在其中捕获异常并将异常中的信息插入到日志记录表中.

I want to have an exception handling block at the end of the procedure where I catch the exception and insert the information from the exception into a logging table.

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

I have boiled the problem down to a simple procedure, below, which fails on PostgreSQL 11.2 with

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' 的输出，而是进入了异常块?是否可以使用 Postgres 11 存储过程执行我上面描述的操作?

Why doesn't this stored procedure work? Why is it that we never see the output of raise notice 'B' and instead we go into the exception block? Is it possible to do what I have described above with a Postgres 11 stored procedure?

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

This is a complete code sample. Paste the above complete code sample (including both the create procedure and call statements) into a sql file and run it in a Postgres 11.2 database to repro. The desired output would be for the function to print A then B, but instead it prints A then C along with the exception information.

另请注意，如果您注释掉所有异常处理块，使得该函数根本不会捕获异常，则该函数将输出 'A' 然后是 'B'，而不会发生异常.这就是为什么我将问题命名为在具有异常块的程序中是否存在 Postgres 提交?"

Also notice that if you comment out all of the exception handling block such that the function does not catch exceptions at all, then the function will output 'A' then 'B' without an exception occurring. This is why I titled the question the way that I did 'Can a Postgres Commit Exist in Procedure that has an Exception Block?'

推荐答案

PL/pgSQL 的语义 错误处理 规定:

The semantics of PL/pgSQL's error handling dictate that:

当一个错误被 EXCEPTION 子句捕获时......块内对持久数据库状态的所有更改都将回滚.

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

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

This is implemented using subtransactions, which are basically the same as savepoints. In other words, when you run the following PL/pgSQL code:

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

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

...what's actually happening is something like this:

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

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

A COMMIT within the block would break this completely; your changes would be made permanent, the savepoint would be discarded, and the exception handler would be left with no way to roll back. As a result, commits are not allowed in this context, and trying to execute a COMMIT will result in a "cannot commit while a subtransaction is active" error.

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

That's why you see your procedure jump to the exception handler instead of running the raise notice 'B': when it reaches the commit, it throws an error, and the handler catches it.

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

This is fairly straightforward to work around, though. BEGIN ... END blocks can be nested, and only blocks with EXCEPTION clauses involve setting savepoints, so you can just wrap the commands before and after the commit in their own exception handlers:

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;
$$;

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

Unfortunately, it does lead to a lot of duplication in the error handlers, but I can't think of a nice way to avoid it.

这篇关于具有异常块的过程中可以存在 Postgres 提交吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！