我有一个包括主键id、两个外键(另外两个id)和一个时间戳的表格。
我希望对该表格进行限制,每种外键组合只能存在有限数量的行。
例如,如果同样的外键组合出现了超过10行,则应该删除最早的那一行。
目前的解决方案是在插入之前使用触发器来检查是否有多于某个数字的行具有与将要插入的行相同的外键。如果有,按照时间戳字段排序,最早的行应该被删除。
有谁可以帮助我实现这个功能呢?
INSERT INTO ... ON CONFLICT UPDATE ...
支持。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;
您应该使用一个单独的摘要表,通过触发器维护,其中包含列fk1
、fk2
和count
,并带有check count<=N
和索引(fk1, fk2)
。
在插入原始表行之前,您应该检查count
的值,如果达到限制,则首先删除最旧的行。如果不想在应用程序中执行此操作,可以使用触发器完成。
您必须记住:
N
行具有相同的(fk1, fk2)
- 将没有足够的行可供删除;(fk1, fk2)
的多个行将导致性能损失(较差的并行性)。(fk1,fk2)
,则可能会变慢。此外,在并行进行多个插入时,它也可能允许太多的行。