一张表有且只有一条记录(续) - 支持插入,并且更新、删除都只作用在最后一条记录上, 查询也只时间最大的记录。

3 minute read

背景

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

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

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

而更新,可能并发还是会较低,因为锁的是最后一条记录。记录成为锁热点并发就上不来。但是如果伴随有频繁的插入,那么更新热点会随之降低,因为被锁的记录是不一样的。

Flag Counter

digoal’s 大量PostgreSQL文章入口