Обработка ошибок в PostgreSQL

Введение

В этой лабораторной работе вы изучите методы обработки ошибок в PostgreSQL с использованием его процедурного языка PL/pgSQL. Вы научитесь создавать функции, которые могут корректно обрабатывать потенциальные ошибки вместо аварийного завершения. Лабораторная работа охватывает генерацию информационных сообщений, перехват конкретных исключений, таких как "деление на ноль", и запись деталей ошибок в таблицу базы данных для последующего анализа. К концу работы вы поймете, как создавать более надежные и устойчивые функции базы данных.

Выдача уведомлений и предупреждений

На этом шаге вы научитесь использовать оператор RAISE в функции PL/pgSQL. Оператор RAISE полезен для предоставления информационных сообщений, предупреждений или отладочного вывода без остановки выполнения функции.

Сначала подключитесь к интерактивному терминалу PostgreSQL, psql, от имени пользователя postgres. Все последующие SQL-команды в этой лабораторной работе будут выполняться внутри этого терминала.

sudo -u postgres psql

Теперь вы увидите приглашение psql, которое выглядит как postgres=#.

Давайте создадим функцию с именем greet, которая принимает имя в качестве входных данных и генерирует NOTICE. Уведомления (Notices) — это сообщения низкого приоритета.

Выполните следующую SQL-команду в оболочке psql:

CREATE OR REPLACE FUNCTION greet(name TEXT)
RETURNS TEXT AS $$
BEGIN
  RAISE NOTICE 'Greeting function called with name: %', name;
  RETURN 'Hello, ' || name;
END;
$$ LANGUAGE plpgsql;

Эта функция принимает аргумент name. Строка RAISE NOTICE '...', name; генерирует сообщение уведомления. Символ % является заполнитель, который заменяется значением переменной name.

Теперь вызовите функцию, чтобы увидеть ее в действии:

SELECT greet('LabEx');

Вывод показывает как сообщение уведомления, так и возвращаемое значение функции:

NOTICE:  Greeting function called with name: LabEx
   greet
-----------
 Hello, LabEx
(1 row)

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

CREATE OR REPLACE FUNCTION greet(name TEXT)
RETURNS TEXT AS $$
BEGIN
  IF name IS NULL OR name = '' THEN
    RAISE WARNING 'Input name is empty or NULL.';
    RETURN 'Hello, stranger';
  ELSE
    RAISE NOTICE 'Greeting function called with name: %', name;
    RETURN 'Hello, ' || name;
  END IF;
END;
$$ LANGUAGE plpgsql;

Теперь вызовите функцию с пустой строкой, чтобы вызвать предупреждение:

SELECT greet('');

На этот раз вы увидите сообщение WARNING вместо NOTICE:

WARNING:  Input name is empty or NULL.
      greet
------------------
 Hello, stranger
(1 row)

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

Вызов и перехват исключения

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

Давайте создадим функцию simple_divide, которая выполняет целочисленное деление. Эта функция имеет потенциальный недостаток: она завершится с ошибкой, если вы попытаетесь выполнить деление на ноль.

CREATE OR REPLACE FUNCTION simple_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
END;
$$ LANGUAGE plpgsql;

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

SELECT simple_divide(10, 2);

Вывод будет результатом деления:

 simple_divide
---------------
             5
(1 row)

Теперь вызовите функцию с нулевым знаменателем, чтобы вызвать исключение:

SELECT simple_divide(10, 0);

Эта команда завершится с ошибкой и вернет сообщение об ошибке. Выполнение функции прерывается.

ERROR:  division by zero
CONTEXT:  PL/pgSQL function simple_divide(integer,integer) line 3 at RETURN

Это необработанное исключение. Чтобы предотвратить аварийное завершение функции, вы можете использовать блок EXCEPTION. Давайте создадим новую функцию safe_divide, которая включает обработку ошибок.

CREATE OR REPLACE FUNCTION safe_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Error: Cannot divide by zero.';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Вот как работает блок EXCEPTION:

BEGIN...END;: Определяет блок кода.
EXCEPTION: Этот ключевое слово начинает раздел обработки исключений.
WHEN division_by_zero THEN: Указывает, что следующий код должен выполняться только при возникновении ошибки division_by_zero.
Код внутри обработчика генерирует уведомление и возвращает NULL вместо аварийного завершения.

Теперь вызовите новую функцию safe_divide с нулевым знаменателем:

SELECT safe_divide(10, 0);

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

NOTICE:  Error: Cannot divide by zero.
 safe_divide
-------------

(1 row)

Вы успешно перехватили конкретное исключение и контролировали вывод функции.

Логирование ошибок в таблицу

Перехват исключений — это хорошо, но для аудита и отладки часто лучше записывать ошибки в постоянное хранилище, например, в таблицу. На этом шаге вы измените функцию safe_divide, чтобы записывать детали ошибки в таблицу error_log, которая была создана во время настройки лабораторной работы.

Сначала давайте изучим структуру таблицы error_log с помощью команды \d в psql.

\d error_log

Вы увидите столбцы таблицы, которые включают автоматически увеличивающийся id, timestamp (временную метку), message (сообщение) об ошибке и function_name (имя функции).

                                              Table "public.error_log"
    Column     |            Type             | Collation | Nullable |                     Default
---------------+-----------------------------+-----------+----------+----------------------------------------------------
 id            | integer                     |           | not null | nextval('error_log_id_seq'::regclass)
 timestamp     | timestamp without time zone |           |          | (now() AT TIME ZONE 'utc'::text)
 message       | text                        |           |          |
 function_name | text                        |           |          |
Indexes:
    "error_log_pkey" PRIMARY KEY, btree (id)

Теперь давайте изменим функцию safe_divide. В блоке EXCEPTION вы добавите оператор INSERT для записи ошибки перед возвратом значения.

CREATE OR REPLACE FUNCTION safe_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
EXCEPTION
  WHEN division_by_zero THEN
    INSERT INTO error_log (message, function_name)
    VALUES ('Division by zero occurred!', 'safe_divide');

    RAISE NOTICE 'Error: Cannot divide by zero. Details logged.';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Теперь снова вызовите функцию, чтобы вызвать ошибку и новую логику логирования:

SELECT safe_divide(10, 0);

Функция вернет NULL, как и раньше, но также выполнит запись в базу данных. Чтобы проверить это, выполните запрос к таблице error_log, чтобы увидеть новую запись.

SELECT function_name, message FROM error_log;

Вывод должен показать зарегистрированную ошибку:

 function_name |          message
---------------+----------------------------
 safe_divide   | Division by zero occurred!
(1 row)

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

Обработка общих исключений с помощью OTHERS

До сих пор вы обрабатывали очень специфическую ошибку: division_by_zero. Но как насчет других, неожиданных ошибок? PL/pgSQL предоставляет универсальный обработчик исключений OTHERS для перехвата любых ошибок, которые не были перехвачены более специфическим предложением WHEN.

На этом шаге вы в последний раз улучшите функцию safe_divide, чтобы обрабатывать любые возможные исключения. Вы также будете использовать две специальные переменные: SQLSTATE (код ошибки SQL) и SQLERRM (сообщение об ошибке SQL) для логирования более подробной информации.

Давайте в последний раз изменим функцию safe_divide.

CREATE OR REPLACE FUNCTION safe_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
EXCEPTION
  WHEN division_by_zero THEN
    INSERT INTO error_log (message, function_name)
    VALUES ('Division by zero occurred!', 'safe_divide');

    RAISE NOTICE 'Error: Cannot divide by zero. Details logged.';
    RETURN NULL;

  WHEN OTHERS THEN
    INSERT INTO error_log (message, function_name)
    VALUES ('An unexpected error occurred: ' || SQLSTATE || ' - ' || SQLERRM, 'safe_divide');

    RAISE NOTICE 'An unexpected error occurred. Details logged.';
    RETURN -1;
END;
$$ LANGUAGE plpgsql;

Новый блок WHEN OTHERS будет перехватывать любые другие ошибки. Он формирует подробное сообщение, используя переменные SQLSTATE и SQLERRM, и вставляет его в журнал. Затем он возвращает -1, чтобы сигнализировать о возникновении общей ошибки.

Хотя наша текущая функция может вызвать только ошибку division_by_zero, такая структура делает ее гораздо более устойчивой к будущим изменениям или неожиданным входным данным. Например, если бы типы входных данных были изменены на тип, который может вызвать переполнение, блок OTHERS перехватил бы ошибку numeric_value_out_of_range.

Давайте снова протестируем путь division_by_zero, чтобы убедиться, что он по-прежнему работает должным образом. Сначала очистите таблицу журнала для чистого теста.

TRUNCATE error_log;

Теперь вызовите функцию:

SELECT safe_divide(10, 0);

Наконец, проверьте журнал. Он должен содержать конкретное сообщение "Division by zero", подтверждающее, что первый блок WHEN был выбран правильно.

SELECT message FROM error_log;

          message
----------------------------
 Division by zero occurred!
(1 row)

Теперь вы создали надежную функцию, которая обрабатывает как специфические, ожидаемые ошибки, так и общие, неожиданные.

Резюме

В этой лабораторной работе вы изучили основы обработки ошибок в PL/pgSQL PostgreSQL. Вы начали с использования оператора RAISE для выдачи сообщений NOTICE и WARNING в целях отладки и информирования. Затем вы перешли к обработке исключений времени выполнения, создав функцию, которая могла завершиться ошибкой, и реализовав блок BEGIN...EXCEPTION...END для перехвата специфической ошибки division_by_zero. Основываясь на этом, вы улучшили функцию для записи деталей ошибки в таблицу базы данных, обеспечив постоянную запись для аудита. Наконец, вы сделали функцию более надежной, добавив общий обработчик WHEN OTHERS для перехвата любых неожиданных ошибок, гарантируя, что ваша функция сможет корректно обрабатывать сбои в различных условиях.