В этой статье мы рассмотрим различные способы вызова ошибок, которые помогут упростить поддержку SQL-кода в будущем.

Данная статья является переводом. Ссылка на оригинал.

Существует множество передовых методов обеспечения обратной и прямой совместимости в коде приложения, но они не очень часто упоминаются в отношении 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"     

Это не так элегантно, но со своей задачей справляется. Мы вызвали ошибку и получили полезное сообщение об ошибке, с которым мы можем работать в дальнейшем.

***

Материалы по теме

• 🗄️ ✔️ 10 лучших практик написания SQL-запросов
• 🐘 Руководство по SQL для начинающих. Часть 1: создание базы данных, таблиц и установка связей между таблицами
• 📜 Основные SQL-команды и запросы с примерами, которые должен знать каждый разработчик