PostgreSQL DBA(104) - pgAdmin(Don't do this：Rule)

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, skippingDROP TABLETime: 3.118 ms[local]:5432 pg12@testdb=# CREATE TABLE tbl (pg12@testdb(#     id       INT4 PRIMARY KEY,pg12@testdb(#     value    INT4 NOT NULLpg12@testdb(# );CREATE TABLETime: 212.508 ms

创建规则

[local]:5432 pg12@testdb=#  CREATE RULE rule_tbl_update AS ON INSERT TO tblpg12@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 RULETime: 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 width=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 width=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 tblSET value = value + 1WHERE 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, skippingDROP TABLETime: 1.598 ms[local]:5432 pg12@testdb=# drop table if exists tbl2_log;NOTICE:  table "tbl2_log" does not exist, skippingDROP TABLETime: 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   FLOATpg12@testdb(# );some_value )        VALUES ( now(), 'INSERT', NEW.id, NEW.created, NEW.some_value );CREATE TABLETime: 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 FLOATpg12@testdb(# );CREATE TABLETime: 23.247 ms[local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# CREATE RULE rule_tbl2_log ASpg12@testdb-#     ON INSERT TO tbl2pg12@testdb-#     DO ALSOpg12@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 RULETime: 18.186 ms

插入数据

[local]:5432 pg12@testdb=# INSERT INTO tbl2 ( created, some_value ) VALUES ( clock_timestamp(), '123' );INSERT 0 1Time: 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          | 1lrecorded    | 2019-10-11 11:14:19.172915+08loperation   | INSERTt_id         | 2t_created    | 2019-10-11 11:14:19.175214+08t_some_value | 123Time: 0.549 ms[local]:5432 pg12@testdb=#

日志表中的t_created字段值应与tbl2的created一致，但实际上却不一致。

建议：rule需谨慎使用，能不用最好就不要使用：）

参考资料
Don't Do This
To rule or not to rule - that is the question