标签
PostgreSQL , 有且只有一条记录
背景
之前写过一篇文档，介绍如何控制某张表有且只有一条记录。
《如何实现一张表有且只有一条记录 implement PostgreSQL table have one and only one row》
接下来这个需求与之类似，一张表好像有且只有一条记录，要求这样：
1、支持插入、更新、删除、查询操作，
2、有一个时间字段用来区分这条记录是什么时候插入、更新的。
3、更新只作用在最后一条记录（时间最大的那条）上，
4、查询只返回时间最大的一条记录。所以看起来就只有一条记录一样。（实际上如果插入了很多，那么就是很多条）
5、删除时，删除所有记录。
实现方法
建立2张表，一张视图，面向用户的是视图（所有的增删改查都基于视图，所以用户可以忘记基表和影子表）。
1、基表
create table base_tbl (
id serial8 primary key, -- 必须有一个PK
info text, -- 用户自身需求的内容
c1 int, -- 用户自身需求的内容
c2 int, -- 用户自身需求的内容
ts timestamp -- 时间（更新、插入时务必更新这个时间）
);
create index idx_base_tbl on base_tbl (ts);
2、影子表（用于触发器），如果没有影子表，直接对基表建立触发器，会有锁错误。
create table shadow_base_tbl ();
3、基表的limit 1视图
create view tbl as select * from base_tbl order by ts desc limit 1;
创建规则和触发器，实现前面提到的需求。
1、视图insert, update, delete规则
create rule r1 AS ON INSERT TO tbl DO INSTEAD INSERT INTO base_tbl (info, c1, c2, ts) VALUES (new.info, new.c1, new.c2, clock_timestamp());
create rule r2 AS ON UPDATE TO tbl DO INSTEAD UPDATE base_tbl SET info = new.info, c1=new.c1, c2=new.c2, ts=clock_timestamp() WHERE base_tbl.id=old.id;
create rule r3 AS ON DELETE TO tbl DO INSTEAD DELETE FROM shadow_base_tbl;
2、影子表delete触发器
create or replace function tg_truncate_v() returns trigger as $$
declare
begin
truncate base_tbl;
return null;
end;
$$ language plpgsql strict;
create trigger tg before delete on shadow_base_tbl for each statement execute procedure tg_truncate_v();
结构定义如下
postgres=# \d+ tbl
View "public.tbl"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+-----------------------------+-----------+----------+---------+----------+-------------
id | bigint | | | | plain |
info | text | | | | extended |
c1 | integer | | | | plain |
c2 | integer | | | | plain |
ts | timestamp without time zone | | | | plain |
View definition:
SELECT base_tbl.id,
base_tbl.info,
base_tbl.c1,
base_tbl.c2,
base_tbl.ts
FROM base_tbl
ORDER BY base_tbl.ts DESC
LIMIT 1;
Rules:
r1 AS
ON INSERT TO tbl DO INSTEAD INSERT INTO base_tbl (id, info, c1, c2, ts)
VALUES (new.id, new.info, new.c1, new.c2, clock_timestamp())
r2 AS
ON UPDATE TO tbl DO INSTEAD UPDATE base_tbl SET info = new.info, c1 = new.c1, c2 = new.c2, ts = clock_timestamp()
WHERE base_tbl.id = old.id
r3 AS
ON DELETE TO tbl DO INSTEAD DELETE FROM shadow_base_tbl
postgres=# \d+ base_tbl
Table "public.base_tbl"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('base_tbl_id_seq'::regclass) | plain | |
info | text | | | | extended | |
c1 | integer | | | | plain | |
c2 | integer | | | | plain | |
ts | timestamp without time zone | | | | plain | |
Indexes:
"base_tbl_pkey" PRIMARY KEY, btree (id)
"idx_base_tbl" btree (ts)
postgres=# \d+ shadow_base_tbl
Table "public.shadow_base_tbl"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------+-----------+----------+---------+---------+--------------+-------------
Triggers:
tg BEFORE DELETE ON shadow_base_tbl FOR EACH STATEMENT EXECUTE PROCEDURE tg_truncate_v()
测试tbl视图的dml如下
1、插入多次
postgres=# insert into tbl(info,c1,c2,ts) values ('test',1,2,now());
INSERT 0 1
postgres=# insert into tbl(info,c1,c2,ts) values ('test12',2,222,now());
INSERT 0 1
postgres=# insert into tbl(info,c1,c2,ts) values ('test12',2,222,now());
INSERT 0 1
postgres=# insert into tbl(info,c1,c2,ts) values ('test12',2,222,now());
INSERT 0 1
只显示最后一条记录的目的达到
postgres=# select * from tbl;
id | info | c1 | c2 | ts
----+--------+----+-----+---------------------------
4 | test12 | 2 | 222 | 2017-07-11 20:39:56.75285
(1 row)
查看基表，所有记录都在
postgres=# select * from base_tbl;
id | info | c1 | c2 | ts
----+--------+----+-----+----------------------------
1 | test | 1 | 2 | 2017-07-11 20:39:49.933267
2 | test12 | 2 | 222 | 2017-07-11 20:39:54.939552
3 | test12 | 2 | 222 | 2017-07-11 20:39:56.406619
4 | test12 | 2 | 222 | 2017-07-11 20:39:56.75285
(4 rows)
2、查询
postgres=# select * from tbl;
id | info | c1 | c2 | ts
----+--------+----+-----+---------------------------
4 | test12 | 2 | 222 | 2017-07-11 20:39:56.75285
(1 row)
3、更新
只会更新最后一条
postgres=# update tbl set info='abcde';
UPDATE 1
postgres=# select * from base_tbl;
id | info | c1 | c2 | ts
----+--------+----+-----+----------------------------
1 | test | 1 | 2 | 2017-07-11 20:39:49.933267
2 | test12 | 2 | 222 | 2017-07-11 20:39:54.939552
3 | test12 | 2 | 222 | 2017-07-11 20:39:56.406619
4 | abcde | 2 | 222 | 2017-07-11 20:42:08.230306
(4 rows)
postgres=# select * from tbl;
id | info | c1 | c2 | ts
----+-------+----+-----+----------------------------
4 | abcde | 2 | 222 | 2017-07-11 20:42:08.230306
(1 row)
4、删除
删除，触发truncate基表的动作
postgres=# delete from tbl;
DELETE 0
postgres=# select * from tbl;
id | info | c1 | c2 | ts
----+------+----+----+----
(0 rows)
postgres=# select * from base_tbl;
id | info | c1 | c2 | ts
----+------+----+----+----
(0 rows)
5、维护
实际上如果用户一直不调用delete，那么随着插入，基表会越来越大。
建议要经常维护基表（例如 锁住基表，把最后一条查出来，TRUNCATE 基表，然后把最后一条插进去）。
例子
begin;
set lock_timeout ='1s';
create LOCAL temp table tmp1 (like tbl) ;
lock table base_tbl in ACCESS EXCLUSIVE mode;
insert into tmp1 select * from tbl;
truncate base_tbl;
insert into tbl select * from tmp1;
drop table tmp1;
end;
注意
注意到，插入是不管你原来有没有记录的，并不是insert on conflict do update的做法。
所以插入的并发可以做到很高。
而更新，可能并发还是会较低，因为锁的是最后一条记录。记录成为锁热点并发就上不来。
时间： 2024-10-08 06:29:44