如何创建包含多列MD5的GENERATED列？

我尝试在 PostgreSQL 14.3 中添加下表：

CREATE TABLE client_cache (
    id            BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    request       VARCHAR COMPRESSION lz4 NOT NULL CHECK (LENGTH (request) <= 10240),
    request_body  BYTEA COMPRESSION lz4 NOT NULL CHECK (LENGTH (request_body) <= 1048576),
    request_hash VARCHAR GENERATED ALWAYS AS (MD5(ROW(request::BYTEA, request_body)::VARCHAR)) STORED
);

但 Postgres 抱怨道：

[42P17] ERROR: generation expression is not immutable

我看到很多答案讨论如何创建GENERATED列包含MD5单列，但一旦添加ROW()计算MD5在多个列上，表达式不再是不可变的。

我可以创建一个GENERATED列使用ROW(MD5(A), MD5(B))但不是MD5(ROW(A, B)).

我可以做什么来创建一个MD5不同类型的多个列的值（如上所示）？

我知道我可以使用触发器创建视图或填充列，但我真的想坚持使用GENERATED如果可能的话列。

我建议使用一个不可变的辅助函数：

CREATE OR REPLACE FUNCTION f_request_md5(_request text, _request_body bytea)
  RETURNS uuid
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 
'SELECT md5(textin(record_out((md5(_request_body), _request))))::uuid';

还有一个像这样的表：

CREATE TABLE client_cache (
  id           bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY
, request      text   COMPRESSION lz4 NOT NULL CHECK (length(request) <= 10240)
, request_body bytea  COMPRESSION lz4 NOT NULL CHECK (length(request_body) <= 1048576)
, request_hash uuid   GENERATED ALWAYS AS (f_request_md5(request, request_body)) STORED
);

数据库小提琴

注意更有效uuid代替varchar. See:

• MD5 字段的最佳数据类型是什么？ https://dba.stackexchange.com/a/115316/3684

背景

有两种重载变体md5()在 Postgres 14（或任何支持的版本）中：

test=> SELECT (proargtypes::regtype[])[0], prorettype::regtype, provolatile
test-> FROM   pg_proc
test-> WHERE  proname = 'md5';
 proargtypes | prorettype | provolatile 
-------------+------------+-------------
 bytea       | text       | i
 text        | text       | i
(2 rows)

一个需要bytea, one text， 两者都是IMMUTABLE并返回text。所以这个表达式是不可变的：

ROW(MD5(request), MD5(request_body))

但这并不像您通过艰难的方式发现的那样：

MD5(ROW(A, B)::varchar)

a 的文本表示record不是一成不变的。原因有很多。本案的一个明显原因是：bytea输出可以在（默认）hex格式或已过时escape格式。一个平原

SET bytea_output = 'escape'; 

...会破坏您生成的列。

获取一个不可变的文本表示bytea值，你会运行它encode(request_body, 'hex') https://www.postgresql.org/docs/current/functions-binarystring.html#FUNCTION-ENCODE。但不要去那里。md5(request_body)为我们的目的提供了更快的不可变文本“表示”。

我们仍然无法录制唱片。所以我创建了包装函数。请务必阅读此相关答案以获取更多说明：

• 为什么我的 UNIQUE 约束没有触发？ https://dba.stackexchange.com/a/299107/3684

就像该答案中讨论的那样，新的内置函数hash_record_extended()将会much更有效地达到目的。所以如果一个bigint已经足够好了，考虑一下：

CREATE TABLE client_cache2 (
  id           bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY
, request      text   COMPRESSION lz4 NOT NULL CHECK (length(request) <= 10240)
, request_body bytea  COMPRESSION lz4 NOT NULL CHECK (length(request_body) <= 1048576)
, request_hash bigint GENERATED ALWAYS AS (hash_record_extended((request, request_body), 0)) STORED
);

相同的数据库小提琴

在 Postgres 14 或更高版本中开箱即用。

Related:

• 为什么在 PostgreSQL 中创建生成列时出现错误？ https://stackoverflow.com/questions/60249404/why-am-i-getting-a-an-error-when-creating-a-generated-column-in-postgresql
• PostgreSQL 中的计算/计算/虚拟/派生列 https://stackoverflow.com/questions/8250389/computed-calculated-virtual-derived-columns-in-postgresql/8250729#8250729