PostgreSQL. Логирование исключений. Часть 2. Реализация.

Hello World! :)

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

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

CREATE TABLE sys.exception_log
(
  id_exception_log bigserial NOT NULL, -- идентификатор записи
  exception_time timestamp without time zone NOT NULL, --время исключения
  exception_code character varying NOT NULL DEFAULT 0, --код исключения
  exception_message character varying NOT NULL, --сообщение
  exception_query text NOT NULL, --текст запроса возбудившего исключение
  exception_label character varying, --метка
  usr_name character varying NOT NULL, --пользователь запустивший запрос
  server_version character varying NOT NULL, --версия сервера PostgreSQL
  db_location character varying NOT NULL, -- локация сервера
  client_location character varying NOT NULL, --локация удаленного клиента
  CONSTRAINT pk_exception_log_id_exception_log PRIMARY KEY (id_exception_log)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE sys.exception_log OWNER TO postgres;

       Одна из главных задач юзабилити этого функционала - это простота использования. Т.е. необходимо минимизировать количество кода который требуется для обеспечения попадания исключения в лог. Решить данную задачу нам поможет написание хранимой процедуры заполнения лога:

CREATE OR REPLACE FUNCTION sys.exception_log_add(acode character varying, amessage character varying, aquery text, alabel character varying DEFAULT NULL::character varying)
  RETURNS void AS
$BODY$
begin
    insert into sys.exception_log(
        exception_time,
        exception_code,
        exception_message,
        exception_query,
        exception_label,
        usr_name,
        server_version,
        db_location,
        client_location)
    values (
        localtimestamp,
        acode,
        amessage,
        aquery,
        alabel,
        current_user,
        version(),
        'Port:'||coalesce(inet_server_port()::text,'no data')||
        '; Host:'||coalesce(inet_server_addr()::text,'no data')||
        '; Database:'||coalesce(current_database(),'no data'),
        'Port:'||coalesce(inet_client_port()::text,'no data')||
        '; Host:'||coalesce(inet_client_addr()::text,'no data')
        );
end;
$BODY$
  LANGUAGE plpgsql;

       Хранимая процедура на данном этапе, просто втсавляет запись в лог. Динамически она определяет время, текущего пользователля и данные локации клиента и сервера. Все остальное передавется через параметры.

Пришло время тестировать!!!

Модифицируем тестовую хранимку из предыдущего поста:

create or replace function test_except_log(
    value1 character varying,
    value2 integer,
    value3 integer)
returns void as
$body$
begin
    select value2/value3;
exception
    when others then
    execute sys.exception_log_add(SQLSTATE, SQLERRM, current_query());
end;   
$body$
language plpgsql;

Запускаем:

select test_except_log('',10,0);

В результате мы получаем следующего вида запись в "exception_log"

Это именно то чего хотелось! :)

Но есть одно существенное НО!
       Тестовая хранимая процедура теперь не выдает никаких исключений, и если я не буду проверять лог, то я никогда не узнаю были исключения или нет. Необходимо сделать так, чтобы после записи в лог исключение все же возбудилось и передалось клиенту. Это реально проблема, т.к. хранимые процедуры в PostgreSQL не поддредвивают автономных транзакций. И если мы попытаемся все же сгенерить исключение при обработке:

create or replace function test_except_log(
    value1 character varying,
    value2 integer,
    value3 integer)
returns void as
$body$
begin
    select value2/value3;
exception
    when others then
        execute sys.exception_log_add(SQLSTATE, SQLERRM, current_query());
        raise; --(!!!)
end;
$body$
language plpgsql;

то записи в лог просто напросто не попадут.
       Блок "exception" как бы создает свою подтранзакцию в которой сервер выполняет операторы обработки исключения, и при добавлении в этот блок оператора "raise" возникает исключение и транзакция откатывает все изменения выполненные операторами обработки.

       Много я сидел и думал на эту тему. Пока не наткнулся на такую штуку как dblink. dblink позволяет выполнять SQL создавая новое соединение с БД. Таким образом выполняемый SQL имеет собственную сессию и собственную транзакцию не зависящую от основной. Вот русскоязычная справка по dblink.

       Для корректного наполенния лога в случае указания "raise" в блоке "exception" я изменил хранимую процедуру "sys.exception_log_add" таким образом чтобы можно было работать через dblink:

CREATE OR REPLACE FUNCTION sys.exception_log_add(acode character varying, amessage character varying, aquery text, alabel character varying DEFAULT NULL::character varying)
  RETURNS void AS
$BODY$
declare
    sql_int text;
begin
    sql_int = 'insert into sys.exception_log(
        exception_time,
        exception_code,
        exception_message,
        exception_query,
        exception_label,
        usr_name,
        server_version,
        db_location,
        client_location)
    values (
        localtimestamp,
        '||coalesce(quote_literal(acode),'null')||',
        '||coalesce(quote_literal(amessage)||'','null')||',
        '||coalesce(quote_literal(aquery)||'','null')||',
        '||coalesce(quote_literal(alabel)||'','null')||',
        current_user,
        version(),'||
        quote_literal('Port:'||coalesce(inet_server_port()::text,'no data')||
        '; Host:'||coalesce(inet_server_addr()::text,'no data')||
        '; Database:'||coalesce(current_database(),'no data'))||','||
        quote_literal('Port:'||coalesce(inet_client_port()::text,'no data')||
        '; Host:'||coalesce(inet_client_addr()::text,'no data'))||')';
    perform dblink_exec('dbname='||current_database(), sql_int);
end;
$BODY$
  LANGUAGE plpgsql;

       Суть изменений состоит в том, что "insert into..." теперь выполняется через динамический SQL, с помощью функции "dblink_exec".

Итог:
       При выполнении тестовой хранимой процедуры генерируется иключение, передается на клиент (как будто обработки нет), и в "фоновом режиме" пишется лог с подробной информацией для локализации.

Если же необходимо просто писать исключения в лог. не возбуждая их для клиента, тогда достаточно НЕ прописывать "raise" в блоке обработки исключений.

Спасибо за внимание.