一张表有且只有一条记录(续) - 支持插入，并且查询、更新、删除只作用在最后一条记录上

背景

之前写过一篇文档，介绍如何控制某张表有且只有一条记录。

《如何实现一张表有且只有一条记录 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;

注意

注意到，插入是不管你原来有没有记录的，并不是insert on conflict do update的做法。

所以插入的并发可以做到很高。

而更新，可能并发还是会较低，因为锁的是最后一条记录。记录成为锁热点并发就上不来。

JPA更新一条记录，数据库查看更新成功，但是紧接着查询，发现还是更新前的结果，怀疑缓存问题但是网上查了下没解决成功。