Автономные транзакции в PostgreSQL

    Начнем с определения того, что такое автономные транзакции в принципе:
    Автономные транзакции позволяют создавать новые подтранзакции (subtransaction), которые могут сохранять или отменять изменения вне зависимости от родительской транзакции. Подробнее тут.

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

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

    dblink


    Во-первых, нам понадобится contrib-модуль dblink. Этот модуль позволяет с помощью своих функций обращаться к другому экземпляру PostgreSQL. Подробное описание выходит за рамки топика. От себя скажу, что dblink — один из нескольких полезнейших contrib-модулей, которые я всегда устанавливаю независимо от задач, решаемых БД.

    Пример


    Покажу описываемый прием на примере из начала топика. Реализуем логирование на «автономных» транзакциях.

    Создаем таблицу логов:
    CREATE TABLE "public"."logs" (
     "log_id" BIGSERIAL,
     "source" TEXT NOT NULL,
     "level" TEXT NOT NULL,
     "message" TEXT NOT NULL,
     "time_added" TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
     CONSTRAINT "logs_pkey" PRIMARY KEY("log_id")
    ) WITH OIDS;


    * This source code was highlighted with Source Code Highlighter.


    Создадим функцию для логгирования:
    CREATE OR REPLACE FUNCTION "public"."log"(IN p_source text, IN p_level text, IN p_message text)
    RETURNS void AS
    $$
    DECLARE
      INSERT_SQL text := 'INSERT INTO public.logs (source, level, message) VALUES (''[SOURCE]'',''[LEVEL]'',''[MESSAGE]'')';
    BEGIN

      IF p_level != 'ERROR' AND p_level != 'WARNING'
        AND p_level != 'INFO' AND p_level != 'DEBUG' THEN
        RAISE EXCEPTION 'Log level should be one of the following types: ERROR, WARNING, INFO, DEBUG';
      END IF;
      
      INSERT_SQL := replace(INSERT_SQL, '[SOURCE]', p_source);
      INSERT_SQL := replace(INSERT_SQL, '[LEVEL]', p_level);
      INSERT_SQL := replace(INSERT_SQL, '[MESSAGE]', p_message);
      
      PERFORM dblink_exec('dbname=' || current_database(), INSERT_SQL);
    END;
    $$ LANGUAGE 'plpgsql';


    * This source code was highlighted with Source Code Highlighter.


    Теперь в любой момент мы можем вызвать функцию «log» и запись тут же появится в таблице логов независимо от статуса транзакции, в во время которой мы ее вызвали.
    PERFORM "public"."log"('monthly billing', 'INFO', 'Starting monthly billing');

    * This source code was highlighted with Source Code Highlighter.


    Как видите, весь прием состоит в том, что мы записываем в таблицу логов с помощью функции «dblink_exec». Это означает, что создается новые соединение, сессия и транзакция, в контексте которых и происходит запись.

    Замечания


    1. К сожалению, этот финт не может полностью заменить автономных транзакций Oracle. Проблемой является производительность — я бы не советовал использовать этот прием налево и направо, подумайте — где это действительно нужно.
    2. Подобный метод применим не только к PostgreSQL. Каждая известная мне СУБД располагает функциями, подобными «dblink_exec»
    3. Как грамотно заметили в комментариях — использование dblink_connect для создания persistent соединения, и использование этого соединения в дальнейшем, ощутимо ускорит работу.
    Метки:
    Поделиться публикацией
    Комментарии 18
    • 0
      Начинали за здравие, закончили за упокой. Основное применение в логировании, но нельзя использовать, так как проблемы с производительностью. И чтоб не выстрелить себе в ногу в самом начале проекта, возьмите лучше вместо PostgreSQL Oracle.
      • +1
        Отчего же, использовать стоит, просто не забывайте о том, что эта операция относительно дорогая.
      • 0
        SAVEPOINT не катит?
        • 0
          А причем тут вообще savepoint? Это точка сохранения внутри одной транзакции. Автономная транзакция независима от родительской.
          • 0
            Ну, условно говоря, если у вас случилась ошибка вы прыгаете к сохраненному значению, а потом врубаете обработку ошибки (либо сперва вставляете обработку ошибки, а ву случае успешного прохождения этапа эту обработку ошибки удаляете).
            Хотя я тут посмотрел — в функциях использование savepoint может вызывать (либо вызывает — тут уже смотреть надо) ошибку. Обходится RAISE EXCEPTION и блоком
            EXCEPTION
            WHEN RAISE_EXCEPTION THEN…
            • 0
              Вы сейчас о конкретной задаче, применение автономных транзакций много шире. Конечно же вы можете логировать разными способами и обходить описанную проблему по-разному. Просто savepoint и автономные транзакции это разные вещи, они никак не связаны.
              • 0
                Они разные, разумеется. Просто мой подход (он, вообще говоря, как мне кажется годится не только для логирования)))) позволяет обойтись без PERFORM dblink_exec — а я подозреваю, что именно эта штука и тормозит. Причем тормозит за счет perform. Хотя, опять же, я могу и ошибаться.
                Собственно — к сути разговора, когда вы пишете про проблемы с производительностью — какого они плана? Насколько оно тормозит? И на каком железе?
                Исключительно шкурный интерес — если вы эту штуку тестировали, вы можете серьезно сэкономить мое время))))
                • 0
                  dblink_exec работает медленно из-за того, что создается новое соединение с БД — а это процесс намного более дорогостоящий чем простая запись в таблицу. Поэтому использовать необходимо с умом. Приведу пример: предположим ваша функция состоит из следующих шагов: начало, большой длинный цикл, конец, обработка ошибок.
                  Вы логируете начало, конец и обработку ошибок с уровнями логирования «INFO» и «ERROR», а внутри цикла с уровнем «DEBUG». Чтобы функция работала как можно быстрее, вы можете отключить логирование с уровнем «DEBUG» (доработать функцию «log» для такого поведения не составит большого труда), и включать логирование «DEBUG» только если приперло и необходимо какое-то серьезное разбирательство.

                  То есть вы можете избежать потерь производительности просто с умом применяя этот прием, на это я и хотел обратить внимание в конце топика.
                  • +3
                    Понятно.

                    Кстати (подозрвеаю, что вам эта информация известна, а вот кому-то из читателей может помочь) — dblink позволяет открыть постоянное соединение — если соответствующим образом скомпоновать функцию, можно дополнительно увеличить производительность.
                    • +1
                      Да, действительно, dblink_connect для создания persistent соединения и в дальнейшем его использование ощутимо ускорит работу.

                      Спасибо что указали на это, действительно важный момент.
              • 0
                Что значит «прыгаете к сохраненному значению а потом врубаете обработку ошибок»? Вы по-моему не представляете себе ни как работает rollback to savepoint, ни вообще как обрабатываются исключения.

                • 0
                  Вообще говоря — представляю. очень даже.
                  Там все вообще говоря просто — в блоке (если мы говорим о функции) мы присваиваем переменной некие значения. Например — про то, что следующий блок у нас завершился ошибкой. В следующем блоке мы меняем значение переменной. Как-то примерно так.
                  Либо, перед окончанием транзакции проверяем (по определенным, напрямую зависящим от транзакции критериям), что именно у нас сработало, а что — нет — (этот способ сравнительно легко гуглится) и обрабатываем результаты.

                  Но, как я уже писал выше, savepoint в функциях может вызывать (либо вызывает — еще раз вынужден повториться — поскольку сам я эту штуку не тестировал а из найденных обсуждений четкого вывода сделать тоже нельзя — я не знаю, может вызывать, или вызывает) ошибку SPI, посему правильнее и надежнее пользоваться
                  RAISE EXCEPTION
                  WHEN RAISE_EXCEPTION THEN
                  • 0
                    Что-то либо я не понимаю, либо вы бредите.
                    1. Как мы можем узнать текущем блоке узнать, «что следующий блок завершился ошибкой»?
                    Нужно в будущее заглядывать?
                    2. «Либо, перед окончанием транзакции проверяем (по определенным, напрямую зависящим от транзакции критериям), что именно у нас сработало, а что — нет — (этот способ сравнительно легко гуглится) „
                    Что такое “ перед окончанием транзакции»? Каким образом вы это определите? Вы в курсе, что если возникает исключение, то обработка прерывается, и переходит в блок исключений? Вы в курсе, что rollback to savepoint — это не «прыжок», как вы выразились, а откат изменений?
                    Вы в курсе, что нельзя переходить из блока исключений обратно в блок операторов?

                    Возможно, это я вас не понимаю. Может, если вы напишете код, то ситуация прояснится.
                    Например простая задача: вставить запись в таблицу, записать при этом в лог-таблицу — успешно она вставилась или нет.
          • +3
            Вот за что люблю хабру — если в статье не очень круто ясен смысл, то в комментах всё разрулят и еще кучу всего расскажут ;)
            • 0
              У вас source и message не экранируются, можно легко словить exception там, где не ожидаешь.
              За статью спасибо, когда пару месяцев назад жалел об отсутствии автономных транзакций — думали в сторону dbms_pipe (есть в составе какого-то contrib-модуля) и постоянно запускающегося задания для job'а для получения и записи лога, про dblink не подумали.
              Увы, job'ы в postgresql реализованы тоже так себе, чаще раза в две минуты штатными средствами запустить не удаётся. Данное решение отмели как либо ненадёжное (теряются сообщения при переполнении очереди), либо вызывающее проблемы с производительностью (sleep от процесса, записывающего лог, при переполнении очереди).
              В итоге написали c-функцию, которая пишет лог в файл. Для нашей задачи это решение подходило, проблем с производительностью вроде нет :)
              • +1
                Да, pgagent не радует. Этот его 2-х минутный период сильно обламывает, не понимаю что им мешает добавить секунды в шедулер.

                А насчет c-функции — мне логи все-таки хочется видеть в таблице. Это, конечно, вопрос вкуса, но мне например лень лезть в шелл и грепать файл вместо того, чтобы написать селект.
              • +1
                Для форматирования кода в постах (особенно SQL) я лично предпочитаю paste.ly
                • 0
                  Хорошая штука, сохранил в закладках, спасибо.

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.