no zuo no die系列，来自于pg的wiki。 这是第二部分，不要使用rule。 理由是： >Rules are incredibly powerful, but they don't do what they look like they do. They look like they're some conditional logic, but they actually rewrite a query to modify it or add additional queries to it. What escapes most people is that rules are not conditional engine to run another queries, but a way to modify queries and/or add more queries to flow. rule只不过是重写SQL带来不必要的复杂性，有时候会让人难以理解。 **rule的副作用** rule会带来副作用，产生“奇怪”的结果。下面举例说明： 创建数据表 ``` [local]:5432 pg12@testdb=# drop table if exists tbl; NOTICE: table "tbl" does not exist, skipping DROP TABLE Time: 3.118 ms [local]:5432 pg12@testdb=# CREATE TABLE tbl ( pg12@testdb(# id INT4 PRIMARY KEY, pg12@testdb(# value INT4 NOT NULL pg12@testdb(# ); CREATE TABLE Time: 212.508 ms ``` 创建规则 ``` [local]:5432 pg12@testdb=# CREATE RULE rule_tbl_update AS ON INSERT TO tbl pg12@testdb-# WHERE EXISTS ( SELECT * FROM tbl WHERE id = NEW.id ) pg12@testdb-# DO INSTEAD UPDATE tbl SET value = value + 1 WHERE id = NEW.id; CREATE RULE Time: 76.578 ms ``` 该规则希望实现在插入时如碰到相同的ID值，则更新value而不是插入。 下面插入第一条记录 ``` [local]:5432 pg12@testdb=# explain (analyze true,verbose true) insert into tbl(id,value) values(1,1); QUERY PLAN ----------------------------------------------------------------------------------------- ----------------------------------------------- Insert on public.tbl (cost=8.17..8.18 rows=1 width=8) (actual time=0.269..0.269 rows=0 loops=1) InitPlan 1 (returns $0) -> Index Only Scan using tbl_pkey on public.tbl tbl_1 (cost=0.15..8.17 rows=1 widt h=0) (actual time=0.033..0.033 rows=0 loops=1) Index Cond: (tbl_1.id = 1) Heap Fetches: 0 -> Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.038..0.038 rows=1 loops=1) Output: 1, 1 One-Time Filter: ($0 IS NOT TRUE) Planning Time: 0.879 ms Execution Time: 0.318 ms Update on public.tbl (cost=8.33..16.35 rows=1 width=14) (actual time=0.040..0.040 rows= 0 loops=1) InitPlan 1 (returns $0) -> Index Only Scan using tbl_pkey on public.tbl tbl_1 (cost=0.15..8.17 rows=1 widt h=0) (actual time=0.014..0.014 rows=1 loops=1) Index Cond: (tbl_1.id = 1) Heap Fetches: 1 -> Result (cost=0.15..8.17 rows=1 width=14) (actual time=0.023..0.024 rows=1 loops=1 ) Output: tbl.id, (tbl.value + 1), tbl.ctid One-Time Filter: $0 -> Index Scan using tbl_pkey on public.tbl (cost=0.15..8.17 rows=1 width=14) ( actual time=0.007..0.008 rows=1 loops=1) Output: tbl.id, tbl.value, tbl.ctid Index Cond: (tbl.id = 1) Planning Time: 0.474 ms Execution Time: 0.076 ms (24 rows) Time: 3.547 ms [local]:5432 pg12@testdb=# select * from tbl; id | value ----+------- 1 | 2 (1 row) Time: 2.151 ms [local]:5432 pg12@testdb=# ``` 插入第一条记录，id和value分别是1和1，但结果的value却是2。观察执行计划的输出，发现在执行insert的时候，同时执行了update语句。 也就是说，rule使得插入语句变成了一条insert语句&一条update语句，即： ``` INSERT INTO tbl (id, value) SELECT 1, 1 WHERE NOT ( EXISTS ( SELECT * FROM v WHERE id = 1) ); UPDATE tbl SET value = value + 1 WHERE id = 1 AND ( EXISTS ( SELECT * FROM tbl WHERE id = 1 ) ); ``` **rule的结果不符合预期** rule达不到期望的结果。 比如我们希望编写rule实现数据表的审计。 创建数据表和rule，这个rule的目的是在插入数据时记录插入的动作和数据。 ``` [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# drop table if exists tbl2; NOTICE: table "tbl2" does not exist, skipping DROP TABLE Time: 1.598 ms [local]:5432 pg12@testdb=# drop table if exists tbl2_log; NOTICE: table "tbl2_log" does not exist, skipping DROP TABLE Time: 1.086 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# CREATE TABLE tbl2 ( pg12@testdb(# id SERIAL PRIMARY KEY, pg12@testdb(# created TIMESTAMPTZ NOT NULL, pg12@testdb(# some_value FLOAT pg12@testdb(# ); some_value ) VALUES ( now(), 'INSERT', NEW.id, NEW.created, NEW.some_value ); CREATE TABLE Time: 90.629 ms [local]:5432 pg12@testdb=# CREATE TABLE tbl2_log ( pg12@testdb(# lid SERIAL PRIMARY KEY, pg12@testdb(# lrecorded TIMESTAMPTZ, pg12@testdb(# loperation TEXT, pg12@testdb(# t_id INT4, pg12@testdb(# t_created TIMESTAMPTZ, pg12@testdb(# t_some_value FLOAT pg12@testdb(# ); CREATE TABLE Time: 23.247 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# CREATE RULE rule_tbl2_log AS pg12@testdb-# ON INSERT TO tbl2 pg12@testdb-# DO ALSO pg12@testdb-# INSERT INTO tbl2_log ( lrecorded, loperation, t_id, t_created, t_some_value ) pg12@testdb-# VALUES ( now(), 'INSERT', NEW.id, NEW.created, NEW.some_value ); CREATE RULE Time: 18.186 ms ``` 插入数据 ``` [local]:5432 pg12@testdb=# INSERT INTO tbl2 ( created, some_value ) VALUES ( clock_timestamp(), '123' ); INSERT 0 1 Time: 3.028 ms ``` 查询数据 ``` [local]:5432 pg12@testdb=# select * from tbl2; id | created | some_value ----+------------------------------+------------ 1 | 2019-10-11 11:14:19.17496+08 | 123 (1 row) Time: 0.626 ms [local]:5432 pg12@testdb=# select * from tbl2_log; -[ RECORD 1 ]+------------------------------ lid | 1 lrecorded | 2019-10-11 11:14:19.172915+08 loperation | INSERT t_id | 2 t_created | 2019-10-11 11:14:19.175214+08 t_some_value | 123 Time: 0.549 ms [local]:5432 pg12@testdb=# ``` 日志表中的t_created字段值应与tbl2的created一致，但实际上却不一致。 建议：rule需谨慎使用，能不用最好就不要使用：） **参考资料** [Don't Do This](https://wiki.postgresql.org/wiki/Don't_Do_This) [To rule or not to rule – that is the question](https://www.depesz.com/2010/06/15/to-rule-or-not-to-rule-that-is-the-question/)