Блог

Мне трудно понять транзакции в Postgres. У меня есть процедура, которая может столкнуться с исключением. Есть части процедуры, в которых я, возможно, захочу зафиксировать свою работу до сих пор, чтобы она не была откатана, если возникнут исключения.

Я хочу иметь блок обработки исключений в конце процедуры, где я перехватываю исключение и вставляю информацию из исключения в таблицу регистрации.

Я свел проблему к простой процедуре, приведенной ниже, которая завершается сбоем в 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' и вместо этого переходим в блок исключений? Возможно ли сделать то, что я описал выше, с помощью хранимой процедуры Postgres 11?

Редактировать: Это полный пример кода. Вставьте приведенный выше полный пример кода (включая оба оператора create procedure и call ) в файл sql и запустите его в базе данных Postgres 11.2 для воспроизведения. Желаемым результатом было бы, чтобы функция печатала A then B , но вместо этого она печатает A then C вместе с информацией об исключении.

Также обратите внимание, что если вы закомментируете весь блок обработки исключений таким образом, что функция вообще не будет перехватывать исключения, то функция выведет ‘A’, затем ‘B’ без возникновения исключения. Вот почему я озаглавил вопрос так, как я это сделал: «Может ли фиксация Postgres существовать в процедуре, имеющей блок исключений?»

Семантика обработки ошибок в PL / pgSQL диктует, что:

Когда ошибка перехватывается предложением EXCEPTION … все изменения постоянного состояния базы данных в блоке откатываются.

Это реализовано с использованием подтранзакций, которые в основном такие же, как точки сохранения. Другими словами, когда вы запускаете следующий код 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
  

COMMIT Внутри блока это полностью нарушило бы это; ваши изменения стали бы постоянными, точка сохранения была бы отброшена, а обработчик исключений остался бы без возможности отката. В результате фиксации в этом контексте не разрешены, и попытка выполнить COMMIT приведет к ошибке «не удается зафиксировать, пока активна подтранзакция».

Вот почему вы видите, что ваша процедура переходит к обработчику исключений вместо выполнения raise notice 'B' : когда она достигает commit , она выдает ошибку, и обработчик ее перехватывает.

Однако обойти это довольно просто. BEGIN ... END блоки могут быть вложенными, и только блоки с EXCEPTION предложениями предполагают установку точек сохранения, поэтому вы можете просто обернуть команды до и после фиксации в их собственные обработчики исключений:

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

К сожалению, это приводит к большому дублированию в обработчиках ошибок, но я не могу придумать хорошего способа избежать этого.

Проблема заключается в EXCEPTION предложении.

Это реализовано в PL / pgSQL как подтранзакция (то же самое, что SAVEPOINT в SQL), которая откатывается при достижении блока исключений.

Вы не можете COMMIT , пока активна подтранзакция.

Смотрите этот комментарий в src/backend/executor/spi.c :

 /*
 * This restriction is required by PLs implemented on top of SPI.  They
 * use subtransactions to establish exception blocks that are supposed to
 * be rolled back together if there is an error.  Terminating the
 * top-level transaction in such a block violates that idea.  A future PL
 * implementation might have different ideas about this, in which case
 * this restriction would have to be refined or the check possibly be
 * moved out of SPI into the PLs.
 */
if (IsSubTransaction())
    ereport(ERROR,
            (errcode(ERRCODE_INVALID_TRANSACTION_TERMINATION),
             errmsg("cannot commit while a subtransaction is active")));