如何创建包含多个列的MD5的生成列？

发布于 2025-02-04 05:10:33 字数 821 浏览 2 评论 0原文

我尝试在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抱怨：

在

我看到了许多答案，讨论了如何创建一个包含单列的md5的列，但是一旦添加row> row（）要在多个列上计算md5，该表达式不再是不可变的。

我可以使用行（MD5（a），MD5（b））创建生成的列，而不是md5（row> row（a，b））。

我该怎么做，以在不同类型的多个列（如上所述）上创建一个md5值？

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

原文

I tried to add the following table in 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
);

But Postgres complains:

[42P17] ERROR: generation expression is not immutable

I've seen many answers discussing how to create a GENERATED column containing the MD5 of a single column, but as soon as you add ROW() to calculate MD5 over multiple columns, the expression is no longer immutable.

I can create a GENERATED column using ROW(MD5(A), MD5(B)) but not MD5(ROW(A, B)).

What can I do instead to create a single MD5 value over multiple columns of varying types (as seen above)?

I know that I can create a view or populate a column using a trigger, but I'd really like to stick to a GENERATED column if possible.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

度的依靠╰つ 2025-02-11 05:10:33

我建议一个不变的助手功能：

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
);

db＆lt;这里

注意更有效的uuid而不是varchar。请参阅：

MD5字段的最佳数据类型是什么？

背景

有两个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，一个text，两者都是不变的和return <代码>文本。因此，此表达式是不可变的：

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

但这不是您发现的困难方式：

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

记录 的文本表示不可变。有很多原因。手头情况的一个明显原因是：bytea输出可以在（默认）hex格式或过时的easce> easce> easce格式中。平原

SET bytea_output = 'escape';

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

要获取bytea值的不变文本表示，您将通过 encode（request_body，'hex'） 。但是不要去那里。 md5（request_body）为我们的目的为我们提供了更快的不变文本“表示”。

我们仍然无法投射纪录。因此，我创建了包装器功能。请务必阅读此相关答案以获取更多说明：

为什么我的独特约束触发不触发？

喜欢那样讨论的喜欢答案，新的，内置的功能hash_record_extended（）将是 More 为此目的更有效。因此，如果一个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
);

： =“ nofollow noreferrer”>此处

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

Background

There are two overloaded variants of md5() in Postgres 14 (or any supported version):

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)

One takes bytea, one text, both are IMMUTABLE and return text. So this expression is immutable:

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

But this is not, like you found out the hard way:

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

The text representation of a record is not immutable. There are many reasons. One obvious reason for the case at hand: bytea output can be in (default) hex format or in the obsolescent escape format. A plain

SET bytea_output = 'escape';

... would break your generated column.

To get an immutable text representation of a bytea value, you'd run it through encode(request_body, 'hex'). But don't go there. md5(request_body) gives us a faster immutable text "representation" for our purpose.

We still can't cast a record. So I created the wrapper function. Be sure to read this related answer for more explanation:

Why doesn't my UNIQUE constraint trigger?

Like discussed in that answer, the new, built-in function hash_record_extended() would be much more efficient for the purpose. So if a bigint is good enough, consider this:

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
);

same db<>fiddle here

Works out of the box in Postgres 14 or later.

回复收藏 0 原文