面向未来的 SQL 的简单方法与错误情况梳理

想要向后兼容性非常简单，可以完全控制新代码，并且完全了解过去的数据和 API。但若想向前兼容性则更具挑战性，可以实现完全控制新代码，但却无法知道数据将来会如何变化，以及将必须支持哪些类型的API。

在应用程序代码中维护向后和向前兼容性有许多最佳实践，但在与 SQL 相关的操作中，它并不常见。SQL用于为应用程序和决策生成关键业务信息，因此没有理由不从类似的实践中受益。

在本文中，我将介绍一种面向未来的 SQL 的简单方法。

目录导读

• 简单的支付系统
• 计算佣金
• 面向未来的 SQL
• 故意失败
• SQL中的Assert never函数
• Assert never 函数
• 没有函数导致的失败
• 滥用除以零
• 滥用转换
• 滥用非文本类型的强制转换

简单的支付系统

假设您有一个支付系统，您的客户可以向客户收取产品费用。该表可能如下所示：

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)
```sql

恭喜！你刚刚赚了你的第一个5美元。

#### 添加新的付款方式
随着时间的流逝，您的支付系统正在成为真正的打击！对您的服务的需求正在飙升，您的客户要求更多的付款方式。您仔细考虑了一下，并决定引入一种新的付款方式 - 银行转账：

```sql
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

SQL中的Assert never函数

要在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中详尽检查的文章，在那里我演示了如何实现Python中一个名为assert_never 的类似函数。

幸运的是，自从本文发布以来，assert_never函数在Python 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"

就像 SQL 中的 assert_never 函数一样，该错误会警告未处理的值“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 ）的表达式在计划时计算。这就是查询失败的原因，即使数据库不必计算子句中的其他表达式也是如此。

滥用转换

另一种流行的错误类型是转换错误。让我们尝试通过将值转换为不兼容的类型来触发错误：

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"

它没有那么优雅，但它可以完成工作。我们触发了一个错误，并收到一条有用的错误消息，我们可以对其执行操作。

原文标题：Future Proofing SQL with Carefully Placed Errors
原文作者：Haki Benita
原文地址：https://hakibenita.com/future-proof-sql