Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- immutable helper function
CREATE OR REPLACE FUNCTION f_request_md5(_request text, _request_body bytea)
  RETURNS uuid
  LANGUAGE sql IMMUTABLE COST 10 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
);

CREATE FUNCTION



CREATE TABLE


INSERT INTO client_cache (request, request_body)
VALUES ('foo', '\xDEADBEEF')
RETURNING *;


id
request
request_body
request_hash


1
foo
\xdeadbeef
e0216006-d3a3-9359-be1b-c3c49d92b606



INSERT 0 1


-- *much* more efficient with the built-in hash_record_extended():
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
);

CREATE TABLE


INSERT INTO client_cache2 (request, request_body)
VALUES ('foo', '\xDEADBEEF')
RETURNING *;


id
request
request_body
request_hash


1
foo
\xdeadbeef
-8262441925920304547



INSERT 0 1