如何定义SQL多行约束 防止按updated_at排序时出现连续重复type和value

原有keyvalues表用于存储键值对，建表语句如下：

CREATE TABLE keyvalues (
  id         serial    PRIMARY KEY,
  key        text,
  type       text,
  value      text,
  updated_at timestamp NOT NULL DEFAULT current_timestamp
);

规则说明 #

• 表默认允许key、value字段重复，通过updated_at字段判定同一key对应的最新值
• 禁止规则：按updated_at升序排序后，同一key下连续出现type、value完全相同的行
• 允许写入场景：
  • 同key下type+value组合交替变化
  • 不同key下存在相同的type+value组合

实现方式 #

使用行级前置触发器实现校验，普通排他约束无法实现基于时间排序的相邻重复判断，触发器逻辑是：每次写入/修改记录前，查找同key下更新时间早于当前记录的最新一条数据，如果这条数据的type、value和新记录完全一致，就抛出异常拦截写入。

第一步：创建触发器校验函数 #

CREATE OR REPLACE FUNCTION check_kv_adjacent_duplicate()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM keyvalues
    WHERE key = NEW.key
      AND type = NEW.type
      AND value = NEW.value
      AND updated_at < NEW.updated_at
    ORDER BY updated_at DESC
    LIMIT 1
  ) THEN
    RAISE EXCEPTION '写入失败：同一key下不允许连续存储type、value完全相同的记录';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

第二步：绑定触发器到表 #

CREATE TRIGGER trg_block_duplicate_adjacent_kv
BEFORE INSERT OR UPDATE OF key, type, value, updated_at
ON keyvalues
FOR EACH ROW
EXECUTE FUNCTION check_kv_adjacent_duplicate();

效果验证 #

会被拦截的非法写入 #

以下同key下连续插入相同type、value的操作会被直接拦截：

1 | log_level | string | info | 2022-01-12 01:00:00
2 | log_level | string | info | 2022-01-12 01:02:00 -- 触发异常，写入失败
3 | log_level | string | info | 2022-01-12 01:10:00 -- 触发异常，写入失败

可正常写入的合法场景 #

1. 同key下type+value交替变化：

1 | log_level | string | info | 2022-01-12 01:00:00
2 | log_level | string | warn | 2022-01-12 01:02:00 -- 写入成功
3 | log_level | string | info | 2022-01-12 01:10:00 -- 写入成功

2. 不同key下type+value相同：

1 | log_level | string | info | 2022-01-12 01:00:00
2 | logging   | string | info | 2022-01-12 01:02:00 -- 写入成功

批量插入同一key的多条记录时，需要保证插入的数据集按updated_at排序后无连续重复，且首条记录和表内已存在的同key最新记录不重复，否则触发器会逐行校验拦截非法数据。

内容的提问来源于stack exchange，提问作者Mahoni