postgresql 删除触发器_PostgreSQL：我没有带闪，不讲武德
前言
今天有個業務的妹子問我“在嗎？”
我說什么事？
給我發個截圖，我一看！噢，原來是把數據刪除了，想讓我把數據找回來。
他說，大哥你能不能幫我。
我說可以！
很快啊，我就打開終端，一個指令就開始了。
我大E了，發現數據閃回不了。
然后十分鐘后我告訴她，搞不定了。
然后她就投訴到領導哪里去了，說她丟的數據找不回來了，DBA也搞不定。
我找到這個妹子，說：“你不講規矩，你不懂這個恢復的難度?！?/p>
她忙說對不起，我不懂規矩啊！
我說：“不講規矩，來，告狀，誣陷我20多年經驗的dba連點數據都恢復不了。這好嗎？這不好。我勸這位女同學，耗子尾汁。好好反思。
PostgreSQL如何開閃
說到閃回查詢，PostgreSQL一開始是有這個功能的，它叫Time Travel(時間旅行)，這名字聽上去比閃回查詢要高大上，瞬間讓我想起了好幾部描述這類的電影，如《時間機器》、《時間旅行者的妻子》、《信條》。
我們擁有時間旅行，可以穿梭于任意的時空，然后來阻止一場災難？大多數電影都是這類的橋段，而對于數據庫來說，時間旅行也是我們拯救災難的一種方法。
時間旅行Time Travel這項技術還要追溯到PostgreSQL 6時代，但是在之后就被打入了冷宮，變成可以使用但是并不推薦。官方文網也說有性能上的影響，推薦使用觸發器替代，一直到PostgreSQL 12這個版本，才真正的把這項功能移除了。
那么怎么在PostgreSQL 12上實現呢？有很多種解決方案，其實原生的PG只是一個食材，大家可以根據這個食材，自由發揮做出各種各樣美味的料理。你可以靈活選擇適合你口味的料理(適配你的系統)。
pg_dirtyread
pg_dirtyread的工作原理比較簡單，就是從Dead Tuples中讀取數據。這得益于PostgreSQL的MVCC機制。在PostgreSQL的MVCC機制中，當更新或者刪除任何一行記錄的時候，將在內部創建新行并將舊行標記為Dead Tuples。而Pg_dirtyread就可以助我們從Dead Tuples讀出數據。但是缺點也很明顯，如果Dead Tuples被autovacuum進程清理了，也就沒數據了。
接下來我們來測試一下，下載編譯pg_dirtyread插件。
make?&&?make?installcreate?extension?pg_dirtyread;
create?table?students
(
????stuno?int,
????name?varchar(50),
????age?varchar(50),
????city?varchar(50)
);
insert?into?students?(stuno,?name,?age,?city)??values?(1,?'abhiram',?22,?'allahabad');??
insert?into?students?(stuno,?name,?age,?city)??values?(2,?'alka',?20,?'ghaziabad');??
insert?into?students?(stuno,?name,?age,?city)??values?(3,?'disha',?21,?'varanasi');??
insert?into?students?(stuno,?name,?age,?city)??values?(4,?'esha',?21,?'delhi');??
insert?into?students?(stuno,?name,?age,?city)??values?(5,?'manmeet',?23,?'jalandhar');?
postgres=#?select?*?from?students;
?stuno?|??name???|?age?|???city????
-------+---------+-----+-----------
?????1?|?abhiram?|?22??|?allahabad
?????2?|?alka????|?20??|?ghaziabad
?????3?|?disha???|?21??|?varanasi
?????4?|?esha????|?21??|?delhi
?????5?|?manmeet?|?23??|?jalandhar?
對上述表做多次更新。
update?students?set?city='WuHan'?where?stuno=5;update?students?set?age=21?where?stuno=5;
update?students?set?city='Shanghai'?where?stuno=5;
通過pg_dirtyread讀取更新前的數據。
postgres=#?SELECT?*?FROM?pg_dirtyread('students')?students(stuno?int,?name?varchar(50),age?varchar(50),city?varchar(50));?stuno?|??name???|?age?|???city????
-------+---------+-----+-----------
?????1?|?abhiram?|?22??|?allahabad
?????2?|?alka????|?20??|?ghaziabad
?????3?|?disha???|?21??|?varanasi
?????4?|?esha????|?21??|?delhi
?????5?|?manmeet?|?23??|?jalandhar
?????5?|?manmeet?|?23??|?WuHan
?????5?|?manmeet?|?21??|?WuHan
?????5?|?manmeet?|?21??|?Shanghai
可以看到把歷史的數據都找回來了，但是很亂，它讀出了所有的歷史數據，可能你只是想恢復到其中的某一個時間點。
postgres=#?SELECT?*?FROM?pg_dirtyread('students')postgres-#?AS?students(tableoid?oid,?ctid?tid,?xmin?xid,?xmax?xid,?cmin?cid,?cmax?cid,?dead?boolean,stuno?int,?name?varchar(50),age?varchar(50),city?varchar(50));??
?tableoid?|?ctid??|???xmin???|???xmax???|?cmin?|?cmax?|?dead?|?stuno?|??name???|?age?|???city????
----------+-------+----------+----------+------+------+------+-------+---------+-----+-----------
?20023788?|?(0,1)?|?26678735?|????????0?|????0?|????0?|?f????|?????1?|?abhiram?|?22??|?allahabad
?20023788?|?(0,2)?|?26678736?|????????0?|????0?|????0?|?f????|?????2?|?alka????|?20??|?ghaziabad
?20023788?|?(0,3)?|?26678737?|????????0?|????0?|????0?|?f????|?????3?|?disha???|?21??|?varanasi
?20023788?|?(0,4)?|?26678738?|????????0?|????0?|????0?|?f????|?????4?|?esha????|?21??|?delhi
?20023788?|?(0,5)?|?26678739?|?26678741?|????0?|????0?|?t????|?????5?|?manmeet?|?23??|?jalandhar
?20023788?|?(0,6)?|?26678741?|?26678742?|????0?|????0?|?t????|?????5?|?manmeet?|?23??|?WuHan
?20023788?|?(0,7)?|?26678742?|?26678743?|????0?|????0?|?f????|?????5?|?manmeet?|?21??|?WuHan
?20023788?|?(0,8)?|?26678743?|????????0?|????0?|????0?|?f????|?????5?|?manmeet?|?21??|?Shanghai
不過知道死元組的事務ID就可以通過pg_xact_commit_timestamp函數將xmin轉換成時間。
使用pg_xact_commit_timestamp函數，需要將參數track_commit_timestamp設置為on，修改該參數需要重啟數據庫。
postgres=#?select?(pg_xact_commit_timestamp(xmin))?from?postgres-#?(SELECT?*?FROM?pg_dirtyread('students')?AS?students(tableoid?oid,?ctid?tid,?xmin?xid,?xmax?xid,?cmin?cid,?cmax?cid,?dead?boolean,stuno?int,?name?varchar(50),age?varchar(50),city?varchar(50)))?as?b;?
???pg_xact_commit_timestamp????
-------------------------------
?2020-11-23?15:23:48.611553+08
?2020-11-23?15:23:48.624343+08
?2020-11-23?15:23:48.6367+08
?2020-11-23?15:23:48.65629+08
?2020-11-23?15:23:48.676773+08
?2020-11-23?15:23:55.716173+08
?2020-11-23?15:23:55.729868+08
?2020-11-23?15:23:56.02215+08
這樣就可以基于時間點恢復到你想要的位置了。
可以說pg_dirtyread很好的解決了誤操作導致的數據修改刪除問題。但是它最大的缺點就是受制于autovacuum進程，如果autovacuum進程清理掉了死元組，pg_dirtyread就沒辦法工作了。所以當出現誤刪數據之后，我們第一時間就要先關閉autovacuum，然后通過下面查詢誤操作的表是否已經發生了vacuum。
select?relname,last_vacuum,?last_autovacuum,?last_analyze,?last_autoanalyze?from?pg_stat_user_tables;最后說一點，這個方法雖好，但是不支持truncate和drop這類的ddl。
temporal_tables
temporal_tables也是一個不錯的擴展，但是它的原理和pg_dirtyread完全不同。它的原理是將我們修改或者刪除的舊行歸檔到一個歷史表中，這樣可以方便進行審計、對比。至于這個插件我覺得他們和IBM DB2的功能很接近，都需要使用一個叫時態表temporal tables技術。
我們來測試一下，下載編譯temporal_tables插件。
make?&&?make?installcreate?extension?temporal_tables;
接下來我們來創建表。
create?table?students(
????stuno?int,
????name?varchar(50),
????age?varchar(50),
????city?varchar(50)
);
建完表后，我們要增加一個sys_period列。
ALTER?TABLE?students?ADD?COLUMN?sys_period?tstzrange?NOT?NULL;然后我們需要創建一個歷史表。
CREATE?TABLE?students_history?(LIKE?students);最后我們要創建一個觸發器，把我們的表和歷史表關聯起來。
CREATE?TRIGGER?students_hist_trigger?BEFORE?INSERT?OR?UPDATE?OR?DELETE?ON?students?FOR?EACH?ROW
EXECUTE?PROCEDURE?versioning('sys_period',?'students_history',?true);
插入記錄。
insert?into?students?(stuno,?name,?age,?city)??values?(1,?'abhiram',?22,?'allahabad');??insert?into?students?(stuno,?name,?age,?city)??values?(2,?'alka',?20,?'ghaziabad');??
insert?into?students?(stuno,?name,?age,?city)??values?(3,?'disha',?21,?'varanasi');??
insert?into?students?(stuno,?name,?age,?city)??values?(4,?'esha',?21,?'delhi');??
insert?into?students?(stuno,?name,?age,?city)??values?(5,?'manmeet',?23,?'jalandhar');??
此時通過查詢，會發現sys_period列上會顯示時間。["2020-11-23 17:10:58.702324+08",)。這個格式前面代表著有效期開始時間，后面則代表有效期結束時間，這里，后面是空的，代表了無窮大。
postgres=#?select?*?from?students;stuno?|??name???|?age?|???city????|?????????????sys_period?????????????
-------+---------+-----+-----------+------------------------------------
?????1?|?abhiram?|?22??|?allahabad?|?["2020-11-23?17:48:13.841549+08",)
?????2?|?alka????|?20??|?ghaziabad?|?["2020-11-23?17:48:13.862973+08",)
?????3?|?disha???|?21??|?varanasi??|?["2020-11-23?17:48:13.874852+08",)
?????4?|?esha????|?21??|?delhi?????|?["2020-11-23?17:48:13.891388+08",)
?????5?|?manmeet?|?21??|?Shanghai??|?["2020-11-23?17:51:22.613059+08",)
我們對表進行更新操作，此時查詢students_history就會顯示我們的歷史數據。
update?students?set?city='WuHan'?where?stuno=5;update?students?set?age=21?where?stuno=5;
update?students?set?city='Shanghai'?where?stuno=5;
postgres=#?select?*?from?students_history;?
stuno?|??name???|?age?|???city????|????????????????????????????sys_period?????????????????????????????
-------+---------+-----+-----------+-------------------------------------------------------------------
?????5?|?manmeet?|?23??|?jalandhar?|?["2020-11-23?17:48:13.916846+08","2020-11-23?17:48:31.964398+08")
?????5?|?manmeet?|?23??|?WuHan?????|?["2020-11-23?17:48:31.964398+08","2020-11-23?17:50:30.014874+08")
?????5?|?manmeet?|?21??|?WuHan?????|?["2020-11-23?17:50:30.014874+08","2020-11-23?17:51:22.613059+08")
雖然能顯示數據，但是和我們想要的差距還是有點大。因為如上面所示，數據還是很紊亂的。想要查詢到指定的時間點還是很困難的。不過根據歷史視圖，我們知道我們在下面三個時間點更新了數據。分別是17:48:13 17:50:30 17:51:22
我們可以在創建一個視圖。
CREATE?VIEW?students_with_history?ASSELECT?*?FROM?students
UNION?ALL
SELECT?*?FROM?students_history;
然后執行下面操作，我就想看看在17:49分應該是顯示那條數據，這里要顯示第一次做update的結果。
postgres=#?SELECT?*?FROM?students_with_historypostgres-#?WHERE?stuno?=?5?AND?sys_period?@>?'2020-11-23?17:49:00'::timestamptz;
stuno?|??name???|?age?|?city??|????????????????????????????sys_period?????????????????????????????
-------+---------+-----+-------+-------------------------------------------------------------------
?????5?|?manmeet?|?23??|?WuHan?|?["2020-11-23?17:48:31.964398+08","2020-11-23?17:50:30.014874+08")
(1?row)
可以看到結果完全正確。換成17點51分在看看，這里顯示了第二次做update的結果。
postgres=#?SELECT?*?FROM?students_with_historypostgres-#???WHERE?stuno?=?5?AND?sys_period?@>?'2020-11-23?17:51:00'::timestamptz?;
?stuno?|??name???|?age?|?city??|????????????????????????????sys_period?????????????????????????????
-------+---------+-----+-------+-------------------------------------------------------------------
?????5?|?manmeet?|?21??|?WuHan?|?["2020-11-23?17:50:30.014874+08","2020-11-23?17:51:22.613059+08")
(1?row)
以上的操作方法就像Oracle中的語法as of timestamp一樣。而AS OF SYSTEM TIME語法是SQL 2011的標準。
而wiki上也有一篇文章詳細的描述了PostgreSQL實現SQL 2011的方法和建議，實現的原理和temporal_tables差不多，但是它多出來了truncate的恢復。其實temporal_tables要實現truncate也很簡單，自己做一個truncate的觸發器就行了。
SQL2011Temporal
衍生版PG
當前現在市面上有一些衍生版的PG，比如CockroachDB。提供了SQL 2011中的AS OF SYSTEM TIME語法。
有興趣的同學可以看看蟑螂數據庫是怎么實現SQL：2011標準的。
Time-Travel Queries: SELECT witty_subtitle FROM THE FUTURE
總結
通過驗證可以看出PostgreSQL打開閃回功能還是比較復雜的，它不像其他數據庫內置了這個功能。需要我們自己找第三方插件來實現。而大多數第三方插件都是基于觸發器實現的。而觸發器的往往會存在一些開銷。同時還要在原表上增加一個時間區間的字段。所以還是推薦使用pg_dirtyread來拯救您誤刪除的數據啊喂。
總結
以上是生活随笔為你收集整理的postgresql 删除触发器_PostgreSQL：我没有带闪，不讲武德的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 沪港通50万要存20天吗
- 下一篇： 增强包_情暖冬至 饺子飘香——临沭县兴