sql - Postgresql 以先进先出的方式限制表的行数

Question

我有一个表，其中包含 PK id、两个 FK(另外两个 id)和一个时间戳。我想对该表进行限制，以便 FK 的每个组合只能有有限的行数。例如，如果有超过 10 行具有相同的 FK，则应删除时间戳最旧的行。

目前，解决方案的想法是插入之前的触发器，它应该检查表中是否有超过一定数量的行与要插入的行具有相同的 FK。如果按字段时间戳存在最早的一个，则应将其删除。

有人可以帮我实现这个吗？

Answer 1

此问题的另一种解决方案是使用数组列而不是行，以及 INSERT INTO ... ON CONFLICT UPDATE ... 支持。

在线示例:https://www.db-fiddle.com/f/2y46V6EEVJLQ5cPNTDAUPy/0

结构:

CREATE TABLE test_rr (
    id serial primary key,
    fk_1 integer not null,
    fk_2 integer not null,
    latest timestamptz[] not null
);

CREATE UNIQUE INDEX idx_unique_rr ON test_rr (fk_1, fk_2);

更新数据:

INSERT INTO test_rr (fk_1, fk_2, latest)
    VALUES (1, 2, array[current_timestamp])
    ON CONFLICT (fk_1, fk_2) DO UPDATE SET latest = (array_cat(EXCLUDED.latest, test_rr.latest))[:10];

选择条目:

SELECT id, fk_1, fk_2, unnest(latest) AS ts FROM test_rr WHERE fK_1 = 1 AND fk_2 = 2;

...导致:

 id  | fk_1 | fk_2 |             ts
-----+------+------+-------------------------------
 652 |    1 |    2 | 2019-03-10 13:28:57.806489+01
 652 |    1 |    2 | 2019-03-10 13:28:56.670678+01
 652 |    1 |    2 | 2019-03-10 13:28:55.470668+01
 652 |    1 |    2 | 2019-03-10 13:28:54.174111+01
 652 |    1 |    2 | 2019-03-10 13:28:52.878719+01
 652 |    1 |    2 | 2019-03-10 13:28:51.3748+01
 652 |    1 |    2 | 2019-03-10 13:28:49.886457+01
 652 |    1 |    2 | 2019-03-10 13:28:48.190317+01
 652 |    1 |    2 | 2019-03-10 13:28:46.350833+01
 652 |    1 |    2 | 2019-03-10 13:11:50.506323+01
(10 rows)

除了 timestamptz[]，您还可以创建自己的类型以支持更多列:

CREATE TYPE my_entry_data AS (ts timestamptz, data varchar);

CREATE TABLE test_rr (
    id serial primary key,
    fk_1 integer not null,
    fk_2 integer not null,
    latest my_entry_data[] not null
);

CREATE UNIQUE INDEX idx_unique_rr ON test_rr (fk_1, fk_2);

-- ...
INSERT INTO test_rr (fk_1, fk_2, latest)
  VALUES (1, 2, array[(current_timestamp,'L')::my_entry_data])
  ON CONFLICT (fk_1, fk_2) DO UPDATE
    SET latest = (array_cat(EXCLUDED.latest, test_rr.latest))[:10];

SELECT id, fk_1, fk_2, tmp.ts, tmp.data
FROM test_rr, unnest(latest) AS tmp -- LATERAL function call
WHERE fK_1 = 1 AND fk_2 = 2;

但是，负载测试必须表明这是否真的比触发器或其他方法更快。至少这有一个好处，即行将被更新而不是插入 + 删除，这可能会节省一些 I/O。