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

背景

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

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