PostgreSQL. Логирование исключений. Часть 1. Требования, тесты, поиски вариантов.

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

Что нужно от логирования?

1. Доступное хранилище лога исключений;
2. Добавление в лог новых записей при возникновении исключений любого типа;
3. Возможность хранения кода и текстового сообщения исключения;
4. Необходимо хранить данные для локализации. В идеале SQL-запрос который вызвал исключение (в т.ч. имя хранимки (породившей исключение) с указанными значениями параметров); 
5. Способ разметки областей хранимой процедуры для более точной локализации возникновения исключений.

       На первый взгляд это все что нужно. В процессе буду дополнять, если потребуется.
       Чтобы начать что-то делать. необходимо понять, какие возможности может предоставить PostgreSQL. Для этого создадим тестовую хранимую процедуру (на ней и будем обкатывать и тестить все варианты исполнения):

create or replace function test_except_log(
    value1 character varying,
    value2 integer,
    value3 integer)
returns character varying as
$body$
begin
    return '';
exception
    when others then
    --.......
end;  
$body$
language plpgsql;

       Первое, что хотелось бы знать, может ли PostgreSQL вернуть код и сообщение об ошибке возникшего исключения.
       После непродолжительных поисков я нашел переменные SQLERRM и SQLSTATE.
SQLERRM - содержит текстовое описание исключения. То что надо!!!
SQLSTATE - содержит код, что тоже будет далеко не лишним.

       Чтобы протестировать как PostgreSQL это делает немного модифицируем тестовую хранимку:

create or replace function test_except_log(
    value1 character varying,
    value2 integer,
    value3 integer)
returns character varying as
$body$
begin
    select value2/value3;
    return '';
exception
    when others then
    raise notice '%, %', SQLERRM, SQLSTATE;
end;   
$body$
language plpgsql;

И спровоцируем деление на ноль таким вызовом:

select test_except_log('',10,0); 

и получим результат:
NOTICE:  division by zero, 22012

Ну чтож, код ошибки и описание теперь есть.

       Далее, было бы круто получить данные по локализации исключений. А именно запрос который инициировал исключение.
       PostgreSQL позволяет получить выполняющийся запрос в реалтайме с помощью функции current_query() (источник), и это очень замечательно. В связи с этим модифицируем тестовую хранимку:

create or replace function test_except_log(
    value1 character varying,
    value2 integer,
    value3 integer)
returns character varying as
$body$
begin
    select value2/value3;
    return 'test';
exception
    when others then
    raise notice '%, %, %', SQLERRM, SQLSTATE, current_query();
end;   
$body$
language plpgsql;

Вызов:

select test_except_log('',10,0);

Результат не может не радовать:
NOTICE:  division by zero, 22012, select test_except_log('',10,0);

Итак, получили все, что было нужно:

• Код исключения;
• Описание;
• Запрос спровоцировавший исключение.

       Дальше, необходимо продумать как хранить все это богатство.
Местом хранения я немерен сделать таблицу в специально отведенной для такого функционала (не имеющего отношение к бизнес логике) схеме.

Таблица будет содержать:

• Дату и время возникновения исключения;
• Код исключения;
• Описание исключения;
• Запрос породивший исключение;
• Метка исключения; (Что это? См. ниже)
• Имя пользователя запустившего запрос;
• Версия сервера*;
• Данные о расположении базы данных*.

* Это необходимо на случай если идея разовьется до трансляции исключений на e-mail разрабтчика БД.

       Наполнять таблицу данными будет процедура со следующими входнями параметрами:

• Код исключения;
• Описание;
• Запрос инициирующий исключение;
• Метка исключения (для более точной локализации области хранимки в которой было сгенерировано исключение).

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

       Вот такой план получился... Пора пристпуать к реализации, но об этом в следующем посте.