稻草芭比解说的冰封王座十大战役堪称经典，而今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认证是非常合适的，即简洁又安全。

七、上分的绊脚石

电玩小子在峡谷最近七连跪，根因原来是新装备的GPS等时区不准，鲁班大师编入如下设置后，小鲁班快速升上为最强王者。

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操作)，创建一个与旧表同名的视图并在同一个事务里进行重命名表，因为是事务级的修改，重命名新表和创建影子视图是一个原子操作。最后当系统稳定运行一段时间，并且旧的程序被清理干净之后，我们再删除影子视图。

本文外貌内容纯属虚构，技术细节来自真实案例，欢迎大家鉴赏或留言。

ITPub博客

新十大经典案例解说