稻草芭比解說的十大戰役堪稱war3界典范,現而今PostgreSQL數據庫里的十大經典案例,它來了…
一、被自宮的登錄用戶
華山門票的后臺數據庫由平之的徒弟負責搭建,由于第一次操作太自信,順手把唯一的用戶登錄權限給自宮了。
alter user postgres nologin;
令狐大俠路過,使用單用戶模式的絕學助其解困:
pg_ctl stop -mi -D $PGDATA
postgres --single -D $PGDATA dbname
backend> alter user postgres login;
二、復雜的數據依賴
燕都需要遷徙大量人口至安雄,愚公一家負責盤點遷移人口關系,進展及其緩慢,天上的老君被其堅持所打動,隨口一聲急急如律令,三道符順勢而出:
--section=pre-data
--section=data
--section=post-data
依靠符咒的加持,任務迅速完成。詳細奧義請參考:<<pg_dump子項section的三種開關>>
三、刻意的破壞
新上任的招標辦負責人孫小空閑來無聊,刪除了牛阿魔家數據庫系統運行最重要的WAL,一旁的來主任不慌不忙,使用如下法寶快速恢復了他家的測試環境。
pg_resetwal $PGDATA
技術詳解:某些場景誤刪pg_wal目錄下的WAL文件后,此時會導致服務器無法正常重啟,可以使用pg_resetwal工具重置WAL來恢復環境。
四、精心的刁難
法和尚修改了金山寺經文管理系統的默認LOCALE后,刁難許官人須快速檢索出某本心經才能前往雷峰塔。番外修煉回來的青姨,利用索引修復技術,幫助他倆成功相會。
CREATE INDEX ON 經文表 (name text_pattern_ops);
技術詳解:數據庫如果使用非C LOCALE會導致無法在LIKE查詢子句中使用普通索引,索引創建時需要使用文本模式操作符text_pattern_ops。
五、可變的認知
劉皇叔過江迎娶新夫人孫二香,攔門的孫小妹搖著頭,晃著腦出題:空是為空,NULL是為NULL,空即為空,而NULL非NULL。眨著大圓眼對迎親隊伍說:要進門,先過我這關。伴郎大羽哥接話:蜀國之文化,一切皆可為,NULL亦可NULL,NULL亦可非NULL。隨即掏出蜀國兵冊設計圖,指出其中一處設計,折服的孫小妹暗生情愫。
CREATE UNIQUE INDEX ON table ((column is null)) WHERE column IS NULL;
技術詳解:SQL標準允許在唯一約束列上插入多個NULL值,借助索引表達式約束也可達成這一設計。
六、樸實無華的安全
七十二洞主齊聚逍遙宮,想要辦證進入練習武林絕學,發現新告示寫著:系統已升級采用最簡化的peer認證,只有門人在宮內方可習練。
技術詳解:一些場景下使用peer認證是非常合適的,即簡潔又安全。
七、上分的絆腳石
電玩小子在峽谷最近七連跪,不上分的根因最終定位到新裝備的時區時間不準,魯班大師編入如下代碼后,小魯班快速升上了王者。
CREATE OR REPLACE FUNCTION <<Your schema>>.sysdate()
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS
$BODY$
SELECT clock_timestamp() AT TIME ZONE '<<DB Timezone>>';
$BODY$
LANGUAGE sql;
八、斗轉星移
阿紫參加信創去O夏令營活動時,遇到幾個不常見的數據類型無法遷移過關,遠程找他堂哥幫忙,利用domain這一特性斗轉星移,順利通關。
create domain binary_float as float;
技術詳解:使用domain作為別名可以創建兼容性數據類型
九、觸發器里的屏障
瓊恩為了快速統計臨冬城頻繁的物資變更以便協調資源,使用trigger觸發器進行串行工作,這確實有效。
CREATE TABLE item (
id bigint GENERATED ALWAYS AS IDENTITY,
value bigint NOT NULL
);
CREATE TABLE sum (
total bigint NOT NULL
);
INSERT INTO sum VALUES (0);
CREATE FUNCTION add_to_sum() RETURNS trigger
LANGUAGE plpgsql AS
$$BEGIN
UPDATE sum SET total = total + NEW.value;
RETURN NEW;
END;$$;
CREATE CONSTRAINT TRIGGER add_to_sum
AFTER INSERT ON item
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION add_to_sum();
可一旦有大量難民涌入,物資分配就會陷入混亂。紅袍女使用古法對其重排化簡幫助瓊恩,臨冬城瞬間變得盡然有序。
DROP TRIGGER add_to_sum ON item;
CREATE OR REPLACE FUNCTION add_to_sum() RETURNS trigger
LANGUAGE plpgsql AS
$$BEGIN
UPDATE sum SET total = total +
(SELECT sum(value) FROM new_rows);
RETURN NULL;
END;$$;
CREATE TRIGGER add_to_sum
AFTER INSERT ON item
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION add_to_sum();
技術詳解:在一個事務里頻繁對單行數據進行更新操作會引起dead tuple回收不及時,此時基于觸發器統計更新數據會變得越來越慢,使用觸發器的過渡表特性在內存中批量處理效果極佳。
十、絕代雙驕之完美合作
生產環境如果直接修改業務表名會影響業務,此時可以使用影子替身可更新視圖來合作完成任務。
BEGIN;
ALTER TABLE x RENAME TO y;
CREATE VIEW x AS SELECT * FROM y;
COMMIT;
技術詳解:直接修改業務表名會導致使用舊表的客戶端中斷,當我們使用可更新視圖(視圖可以正常進行insert、update、delete操作),創建一個與舊表同名的視圖并在同一個事務里進行重命名表,因為是事務級的修改,重命名新表和創建影子視圖是一個原子操作。最后當系統穩定運行一段時間,并且舊的程序被清理干凈之后,我們再刪除影子視圖。
本文外貌內容純屬虛構,技術細節來自真實案例,歡迎大家鑒賞或留言。
保持聯系
從2019年12月開始寫第一篇文章,分享的初心一直在堅持,本人現在組建了一個PG樂知樂享交流群,歡迎關注我文章的小伙伴加我微信進群吹牛嘮嗑,交流技術。





