PostgreSQL按照先进先出的方式限制表格行数

Question

PostgreSQL按照先进先出的方式限制表格行数

4

我有一个包括主键id、两个外键（另外两个id）和一个时间戳的表格。我希望对该表格进行限制，每种外键组合只能存在有限数量的行。例如，如果同样的外键组合出现了超过10行，则应该删除最早的那一行。目前的解决方案是在插入之前使用触发器来检查是否有多于某个数字的行具有与将要插入的行相同的外键。如果有，按照时间戳字段排序，最早的行应该被删除。有谁可以帮助我实现这个功能呢？

- Tonci Zilic

如果应用程序错误导致它快速添加大量错误行，那该怎么办？您的旧历史记录将瞬间消失。此外，在更新触发器中删除数据会使更新变慢。最好实现清除旧数据作为批处理过程，并进行一些审计，甚至在某个地方备份旧行。 - mvp

有一个理论上的 SQL 构造用于跨行强制执行约束，即 ASSERTION。不幸的是，PostgreSQL 没有实现它（也没有其他企业级 RDBMS 产品）。最大的难点是并发性。假设你的表有十行数据，两个用户同时尝试插入一行。现在两个会话都将尝试删除最旧的行：哎呀！因此，您需要对表进行串行化 DML 访问。 - APC

我应该解释一下，我确信每次只有一个用户在编写，因为后端应用程序正在处理此事。 - Tonci Zilic

为什么需要逻辑存储行数限制？我更倾向于使用行限制视图（例如 https://dev59.com/K3NA5IYBdhLWcg3wBo9m ），然后根据性能原因进行定期清理。任何直接在写入事务中工作的解决方案都会在高负载下严重影响性能。如果您的数据是时间序列，则还可以考虑集成扩展，例如 TimescaleDB，它可以大幅提升写入和读取性能。 - Ancoron

2个回答

0

您应该使用一个单独的摘要表，通过触发器维护，其中包含列fk1、fk2和count，并带有check count<=N和索引(fk1, fk2)。

在插入原始表行之前，您应该检查count的值，如果达到限制，则首先删除最旧的行。如果不想在应用程序中执行此操作，可以使用触发器完成。

您必须记住：

如果您更改了fk1或fk2的值或在同一事务中删除具有相同fk1和fk2的多个行，则必须以某些指定顺序（例如按id排序）执行此操作，否则可能会出现死锁；
您不能在单个事务中添加超过N行具有相同的(fk1, fk2) - 将没有足够的行可供删除；
添加具有相同(fk1, fk2)的多个行将导致性能损失（较差的并行性）。

一个简单的触发器，只检查一定数量的行并在插入之前删除最旧的行，如果有许多（如100+）具有相同的（fk1，fk2），则可能会变慢。此外，在并行进行多个插入时，它也可能允许太多的行。

- Tometzky

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ancoron · Accepted Answer

这个问题的另一个解决方案是使用数组列而不是行，以及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。