本文共 6673 字，大约阅读时间需要 22 分钟。
PostgreSQL , 有且只有一条记录
之前写过一篇文档，介绍如何控制某张表有且只有一条记录。
接下来这个需求与之类似，一张表好像有且只有一条记录，要求这样：
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的做法。
所以插入的并发可以做到很高。
而更新，可能并发还是会较低，因为锁的是最后一条记录。记录成为锁热点并发就上不来。
转载地址：http://tfufm.baihongyu.com/