562
一張表有且隻有一條記錄(續) - 支持插入，並且查詢、更新、刪除隻作用在最後一條記錄上
標簽
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、影子表（用於觸發器），如果沒有影子表，直接對基表建立觸發器，會有鎖錯誤。
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的做法。
所以插入的並發可以做到很高。
而更新，可能並發還是會較低，因為鎖的是最後一條記錄。記錄成為鎖熱點並發就上不來。
最後更新：2017-07-14 09:04:07