作者
digoal
日期
2017-07-11
标签
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、影子表（用于DELETE触发器），在触发器中使用TRUNCATE来清理数据。如果不使用影子表，而是直接对基表建立触发器，TRUNCATE基表时会有锁错误。
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
set lock_timeout ='1s'; -- 设置锁超时，因为这里用的是TRUNCATE DDL语句。
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的做法。
所以插入的并发可以做到很高。
而更新，可能并发还是会较低，因为锁的是最后一条记录。记录成为锁热点并发就上不来。但是如果伴随有频繁的插入，那么更新热点会随之降低，因为被锁的记录是不一样的。
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
