Существует множество передовых методов обеспечения обратной и прямой совместимости в коде приложения, но они не очень часто упоминаются в отношении SQL, который используется для получения крайне важной бизнес-информации для приложений и последующего принятия решений. Именно поэтому в данной статьей мы рассмотрим различные способы вызова ошибок для SQL-кода, которые помогут упростить поддержку проекта в будущем.
Простая платежная система
Скажем, у вас есть платежная система, в которой ваши пользователи могут взимать плату со своих клиентов за продукты. Таблица может выглядеть так:
db=# CREATE TABLE payment (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
method TEXT NOT NULL
CONSTRAINT payment_method_check CHECK (method IN ('credit_card', 'cash')),
amount INT NOT NULL
);
CREATE TABLE
Вы предоставляете своим пользователям два варианта оплаты: наличными или кредитной картой:
db=# INSERT INTO payment (method, amount) VALUES
('cash', 10000),
('credit_card', 12000),
('credit_card', 5000);
INSERT 0 3
db=# SELECT * FROM payment;
id │ method │ amount
────┼─────────────┼────────
1 │ cash │ 10000
2 │ credit_card │ 12000
3 │ credit_card │ 5000
(3 rows)
Расчет комиссии
Используйте следующий запрос для расчета комиссии за каждый платеж в зависимости от способа оплаты:
-- calculate_commission.sql
SELECT
COUNT(*) AS payments,
SUM(
CASE method
WHEN 'cash' THEN 100
WHEN 'credit_card' THEN 30 + amount * 0.02
END
) AS commission
FROM
payment;
При оплате наличными вы взимаете фиксированную комиссию в размере 1 доллара США (100 центов), а при оплате кредитной картой вы взимаете фиксированную плату в размере 30 центов плюс 2% от взимаемой суммы.
Это комиссия за первые 3 платежных процесса:
db=# \i calculate_commission.sql
payments │ commission
───────────┼────────────
3 │ 500.00
(1 row)
Поздравляю! Вы только что заработали свои первые 5$.
Добавление нового способа оплаты
Время идет, и ваша платежная система становится настоящим хитом! Спрос на ваши услуги стремительно растет, и ваши клиенты просят больше способов оплаты. Вы хорошенько все обдумываете и решаете ввести новый способ оплаты — банковский перевод:
db=# ALTER TABLE payment DROP CONSTRAINT payment_method_check;
ALTER TABLE
db=# ALTER TABLE payment ADD CONSTRAINT payment_method_check
CHECK (method IN ('credit_card', 'cash', 'bank_transfer'));
ALTER TABLE
Прошло еще несколько месяцев, и новый способ оплаты оказался настоящим хитом:
INSERT INTO payment (method, amount) VALUES
('bank_transfer', 9000),
('bank_transfer', 15000),
('bank_transfer', 30000);
INSERT 0 3
Вы обрабатываете больше платежей, чем могли себе представить, но что-то не так:
db=# \i calculate_commission.sql
payments │ commission
──────────┼────────────
6 │ 500.00
(1 row)
Вы обрабатываете все эти платежи, но ваш доход остается прежним, почему?
SQL-код, который не потеряет актуальности
При добавлении нового способа оплаты вы не редактировали запрос, рассчитывающий комиссию. Запрос никогда не завершался ошибкой, не возникало никаких исключений или предупреждений, и вы полностью забыли об этом!
Этот тип сценария довольно распространен. SQL обычно не проверяется статически, поэтому, если у вас нет автоматических тестов для этого конкретного запроса, он может легко остаться незамеченным!
Совершаем ошибки намеренно
Ошибки считаются неудачей, но на самом деле они довольно полезны. Если запрос выдает ошибку при столкновении с неизвестным способом оплаты, вы можете обнаружить эту ошибку и немедленно исправить.
Напомним запрос для расчета комиссии:
SELECT
COUNT(*) AS payments,
SUM(
CASE method
WHEN 'cash' THEN 100
WHEN 'credit_card' THEN 30 + amount * 0.02
END
) AS commission
FROM
payment;
В запросе используется CASE-выражение для расчета комиссии для каждого способа оплаты. Выражение не определяет, что должно произойти, если метод не соответствует ни одному из WHEN-выражений, поэтому выражение неявно оценивается как NULL, а агрегатная функция игнорирует его.
Что, если вместо неявной оценки NULL мы получим ошибку?
Assert never в SQL
Чтобы вызвать ошибку в PostgreSQL, мы можем использовать простую функцию:
CREATE OR REPLACE FUNCTION assert_never(v anyelement)
RETURNS anyelement
LANGUAGE plpgsql AS
$$
BEGIN
RAISE EXCEPTION 'Unhandled value "%"', v;
END;
$$;
Функция принимает аргумент любого типа и вызывает исключение:
db=# SELECT assert_never(1);
ERROR: Unhandled value "1"
CONTEXT: PL/pgSQL function assert_never(anyelement) line 3 at RAISE
Чтобы получить ошибку, когда запрос встречает неизвестное значение и срабатывает ветка ELSE, мы должны совершить вызов следующим способом:
db=# SELECT
COUNT(*) AS payments,
SUM(
CASE method
WHEN 'cash' THEN 100
WHEN 'credit_card' THEN 30 + amount * 0.02
ELSE assert_never(method)::int
END
) AS commission
FROM
payment;
ERROR: Unhandled value "bank_transfer"
CONTEXT: PL/pgSQL function assert_never(anyelement) line 3 at RAISE
Это круто! Запрос обнаружил необработанный способ оплаты bank_transfer и завершился ошибкой. К ошибке также относятся значения, которые мы забыли учесть, что делает его особенно полезным для отладки.
Ошибка заставляет разработчика предпринять следующие действия при обработке исключения:
Явно исключить необработанное значение:
SELECT
COUNT(*) AS payments,
SUM(
CASE method
WHEN 'cash' THEN 100
WHEN 'credit_card' THEN 30 + amount * 0.02
ELSE assert_never(method)::int
END
) AS commission
FROM
payment
WHERE
method IN ('cash', 'credit_card');
payments │ commission
──────────┼────────────
3 │ 500.00
Разработчик может решить явно исключить это значение. Может быть, оно не имеет значения или обрабатывается другим запросом. В любом случае значение теперь исключается явно, а не просто игнорируется.
Обработать новое значение:
SELECT
COUNT(*) AS payments,
SUM(
CASE method
WHEN 'cash' THEN 100
WHEN 'credit_card' THEN 30 + amount * 0.02
WHEN 'bank_transfer' THEN 50
ELSE assert_never(method)::int
END
) AS commission
FROM
payment;
payments │ commission
──────────┼────────────
6 │ 650.00
Разработчик заметил ошибку и добавил в запрос комиссию за необработанный способ оплаты. Ошибка предотвращена!
В обоих случаях результаты теперь точны, а запрос безопаснее.
Assert never
Исчерпывающая проверка является распространенным паттерном во многих языках, чтобы убедиться, что обработаны все возможные значения. Я уже писал об исчерпывающей проверке в Python в прошлом, где показал, как реализовать аналогичную функцию с именем assert_never в Python.
К счастью, после публикации статьи функция assert_never была встроена в модуль ввода в Python 3.11, и ее можно использовать для выполнения исчерпывающей проверки:
from typing import assert_never, Literal
def calculate_commission(
method: Literal['cash', 'credit_card', 'bank_transfer'],
amount: int,
) -> float:
if method == 'cash':
return 100
elif method == 'credit_card':
return 30 + 0.02 * amount
else:
assert_never(method)
Запуская код в Mypy, программа проверки опциональных статических типов для Python выдаст следующую ошибку:
error: Argument 1 to "assert_never" has incompatible type "Literal['bank_transfer']";
expected "NoReturn"
Как и функция assert_never в SQL, ошибка предупреждает о необработанном значении bank_transfer. В отличие от функции в SQL, это произойдет не во время выполнения, а во время статического анализа.
Ошибка без функции
Если по какой-то причине вы не можете или не хотите использовать функции, есть другие способы вызвать ошибки в SQL.
Злоупотребление делением на ноль
Самый простой способ вызвать ошибки в любом языке программирования — это разделить некоторое число на ноль:
SELECT
COUNT(*) AS payments,
SUM(
CASE method
WHEN 'cash' THEN 100
WHEN 'credit_card' THEN 30 + amount * 0.02
ELSE 1/0 -- intentional
END
) AS commission
FROM
payment;
ERROR: division by zero
Вместо возврата NULL, когда метод не обрабатывается, мы делим 1 на 0, чтобы вызвать ошибку деления на ноль. Запрос не удался, как мы и хотели, но это не работает так, как мы могли бы ожидать.
Рассмотрим следующий сценарий, в котором обрабатываются все возможные способы оплаты:
SELECT
COUNT(*) AS payments,
SUM(
CASE method
WHEN 'cash' THEN 100
WHEN 'credit_card' THEN 30 + amount * 0.02
WHEN 'bank_transfer' THEN 50
ELSE 1/0 -- fail on purpose
END
) AS commission
FROM
payment;
ERROR: division by zero
Этот запрос обрабатывал все возможные способы оплаты, но все равно возникли ошибки. Если посмотреть документацию про CASE, то становится понятно почему:
Существуют различные ситуации, в которых подвыражения выражения оцениваются в разное время, поэтому принцип «CASEоценивает только необходимые подвыражения» не является фундаментальным. Например, постоянное подвыражение 1/0 обычно приводит к ошибке деления на ноль во время планирования, даже если оно находится в той частиCASE, которая никогда не будет введена во время выполнения.
В документации можно найти объяснение этому. Хотя CASE обычно оценивает только необходимые выражения, бывают случаи, когда выражения, использующие только константы, такие как 1/0, оцениваются во время планирования. Вот почему запрос завершился ошибкой, хотя базе данных не нужно было оценивать выражение в ELSE-условии.
Злоупотребление приведением типов
Еще один популярный вид ошибок — ошибки приведения. Попробуем вызвать ошибку, приведя значение к несовместимому типу:
SELECT
COUNT(*) AS payments,
SUM(
CASE method
WHEN 'cash' THEN 100
WHEN 'credit_card' THEN 30 + amount * 0.02
ELSE method::int
END
) AS commission
FROM
payment;
ERROR: invalid input syntax for type integer: "bank_transfer"
Мы пытались преобразовать текстовое значение в столбце method в целое число, но запрос не был выполнен. В качестве бонуса сообщение об ошибке предоставляет нам значение bank_transfer, что позволяет легко идентифицировать необработанное значение.
Давайте также проверим, что запрос не завершается ошибкой при обработке всех методов:
SELECT
COUNT(*) AS payments,
SUM(
CASE method
WHEN 'cash' THEN 100
WHEN 'credit_card' THEN 30 + amount * 0.02
WHEN 'bank_transfer' THEN 50
ELSE method::int
END
) AS commission
FROM
payment;
payments │ commission
──────────┼────────────
6 │ 650.00
Когда запрос обрабатывает все возможные значения для method, он не завершается ошибкой!
Злоупотребление приведением типов для нетекстовых типов
Если вы будете использовать эту технику достаточно долго, вы обнаружите, что запуск ошибки приведения требует некоторого творчества. Инициировать ошибку приведения для текстовых значений, подобных приведенным выше, обычно проще — просто приведите к целому числу, и, скорее всего, это не удастся.
Однако, если у вас есть целочисленный тип, к какому типу вы бы его привели, чтобы вызвать ошибку? Вот что я придумал через некоторое время:
SELECT
CASE n
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE ('Unhandled value ' || n)::int::text
END as v
FROM (VALUES
(1),
(2),
(3)
) AS t(n);
ERROR: invalid input syntax for type integer: "Unhandled value 3"
Это не так элегантно, но со своей задачей справляется. Мы вызвали ошибку и получили полезное сообщение об ошибке, с которым мы можем работать в дальнейшем.
Комментарии