Фиксация данных в БД при обработке исключений

   При разработке бизнес-логики на сервере БД PostgreSQL, иногда возникает потребность в фиксации данных, как предшествующих исключительной ситуации, так и возникших в результате появления исключительной ситуации.

   Рассмотрим простой пример. В БД реализована длинная и сложная хранимая процедура, например, ежемесячное закрытие отчетного периода (или сложный расчёт, или импорт пользовательских данных, или обмен с другими серверами). Важные события хотелось бы видеть в таблице логов. Можно, конечно, просто писать в нее без всяких ухищрений. Но есть один серьезный недостаток — если в процедуре происходит необработанная ошибка — транзакция откатывает не только таблицы прикладных данных, но и таблицы логов.

   Для решения задачи, можно воспользоваться механизмом dblink, который откроет новое подключение к той же БД в которой выполняется процедура, и осуществит сохранение данных в отдельной транзакции, независимой от текущей. В таком случае, возбуждение исключительной ситуации в коде основной процедуры не приведет к откату сохраненных таким способом данных.

drop table if exists test.t_error;

drop table if exists test.t_log;

create table if not exists test.t_error(i bigint, name varchar);

create table if not exists test.t_log(id bigint);

CREATE or replace PROCEDURE test.exception_log_test()

LANGUAGE plpgsql

AS $$

DECLARE

i bigint;

j float;

begin

-- установка persistent-соединения, чтобы внутри цикла уже просто обращаться к нему не переоткрывая заново

-- соединение устанавливается с той же самой БД, в которой работает текущая процедура

perform dblink_connect('myconn', 'dbname=' || current_database());

-- "длинный" цикл

for i in 1..3 loop

begin

-- логгирование всего подряд, и оно не откатится

perform dblink_exec('myconn', 'insert into test.t_log (id) values('||i::text||');');

-- "сложный расчет", иногда выбрасывающий исключения

j=1.0/(2-i);

-- обработка исключения (если требуется)

exception when others then

-- логгирование только отловленных ошибок

insert into test.t_error(i, name) select i, SQLERRM;

end;

end loop;

-- корректное закрытие соединения

perform dblink_disconnect('myconn');

END;

$$;

call test.exception_log_test();

select * from test.t_log;

id|

--+

1|

2|

3|

select * from test.t_error;

i|name |

-+---------------+

2|деление на ноль|

   В результате выполнения видно, что в таблицу test.t_log накопился полный журнал событий, а в таблицу test.t_error только те записи, которые привели к ошибке.