十二月 25, 2024
摘要：在本教程中，您将了解 PostgreSQL 中的 PL/pgSQL 函数易失性。
目录
介绍
PL/pgSQL 语言，作为 PostgreSQL 中一个默认提供的扩展，为应用开发者提供了强大的工具和灵活性，以在数据库中构建复杂的企业级功能。通过 PL/pgSQL 的函数和过程，开发者可以选择不同的易失性类别，IMMUTABLE、STABLE或VOLATILE，从而可以提供不同的性能优势，尤其是在结果缓存和数据状态感知方面。要更深入地了解，请参阅官方文档。为函数选择合适的易失性非常重要。
示例
在一次迁移项目中，在调试从 Oracle 转换过来的代码时，我们遇到了意外的行为。下面是一个使用 mock 函数重新创建的示例，以说明这个问题：
TRUNCATE TABLE user_status;
CREATE TABLE user_status(
col1 bigint GENERATED BY DEFAULT AS IDENTITY,
status text);
CREATE OR REPLACE FUNCTION upd_status(text)
RETURNS bigint LANGUAGE plpgsql
AS $$
DECLARE var1 bigint;
BEGIN
WITH alias1 AS
(INSERT INTO user_status(status) VALUES($1) RETURNING col1)
SELECT * INTO var1 FROM alias1;
RETURN var1;
END;$$;
CREATE OR REPLACE FUNCTION lookup_status(text)
RETURNS boolean LANGUAGE plpgsql
IMMUTABLE PARALLEL SAFE
AS $$
BEGIN
RETURN (SELECT EXISTS (SELECT 1 FROM user_status WHERE status = $1));
END;$$;
CREATE OR REPLACE FUNCTION check_status(text)
RETURNS void LANGUAGE plpgsql
AS $$
DECLARE var1 bigint;
BEGIN
var1 := upd_status($1);
IF lookup_status($1) THEN
RAISE NOTICE 'Status exists as part of immutable check - %', $1;
ELSE
RAISE NOTICE 'Status does not exists - %', $1;
END IF;
RAISE NOTICE 'Actual Result for status % is %', $1,
(SELECT EXISTS (SELECT 1 FROM user_status WHERE status = $1 AND col1 = var1));
END;$$;
如果您观察输出，Open状态会通过check_status调用插入到user_status，但同一快照中对lookup_status的任何调用，都不会检测到该数据更改。
SELECT check_status('Open');
NOTICE: Status dose not exists - Open
NOTICE: Actual Result for status Open is t
check_status
--------------
(1 row)
查找函数无法找到新的数据状态。实际的查找确实显示数据是存在的：
table user_status;
col1 | status
------+--------
22 | Open
(1 row)
在花费一些时间后，定位出根本原因：lookup_status被迁移的工程师错误地标记为IMMUTABLE，导致它未捕获到同一快照中的数据状态更改，而返回了过时的结果。
注意：当一个 IMMUTABLE 函数在一个表上执行 SELECT 语句时，PostgreSQL 不会发出警告。尽管如此，鉴于 IMMUTABLE 函数是基于数据保持不变的假设而预测的，因此对表的任何修改，都可能因为查询计划的强制缓存，导致结果出现不一致。
函数易失性 – 决策矩阵
易失性的选择提供了性能优势，但如果不仔细考虑，也可能导致数据一致性问题，尤其是在从 Oracle 或 SQL Server 迁移代码时。以下是为不同使用场景选择合适的易失性的一个指导：
| 使用场景 | 易失性类别 | 原因 |
|---|---|---|
| 具有副作用的函数（例如，有更新） | VOLATILE | 确保每次都重新计算函数。 |
| 每次调用的值不断变化的函数 | VOLATILE | 如果在单个查询中值会变化（例如random()，currval()，timeofday()），则为需要标记为 VOLATILE。 |
| 依赖于事务上下文的函数 | STABLE | 在一个事务中保持一致的稳定函数（例如current_timestamp）。 |
| 没有外部依赖的纯函数 | IMMUTABLE | 适用于函数输出永不变化的情况（例如，不涉及数据访问或查找的数学计算）。 |
| 从表中查询数据的函数 | STABLE | 在单个查询快照中提供固定的视图；数据更新时 IMMUTABLE 会产生过时的结果。 |
在将lookup_status的易失性更改为STABLE后，它可以按预期工作了，作为调用方快照的一部分，正确地反映出状态变更：
CREATE OR REPLACE FUNCTION lookup_status(text)
RETURNS boolean LANGUAGE plpgsql
STABLE PARALLEL SAFE
AS $$
BEGIN
RETURN (SELECT EXISTS(SELECT 1 FROM user_status WHERE status = $1));
END;$$;
通过仔细设置适当的易失性，我们可以实现可靠的功能和性能，这在跨数据库处理过程性代码时尤其重要。
结论
在 PostgreSQL 函数中，设置正确的易失性级别会显著影响函数性能和数据一致性。在开发或迁移代码时，请始终评估好您的函数，是否应该标记为 IMMUTABLE、STABLE 或 VOLATILE，以避免意外结果。
- 将 VOLATILE 用于带有副作用或更改值的函数。
- 在事务中需要一致的结果时，请选择 STABLE。
- 将 IMMUTABLE 限制为没有外部依赖的函数。
请注意，易失性的设置有助于保持应用程序的数据准确性和效率。