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帶來不必要的復(fù)雜性，有時候會讓人難以理解。

rule的副作用
rule會帶來副作用，產(chǎn)生“奇怪”的結(jié)果。下面舉例說明：
創(chuàng)建數(shù)據(jù)表

[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

創(chuàng)建規(guī)則

[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

該規(guī)則希望實現(xiàn)在插入時如碰到相同的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，但結(jié)果的value卻是2。觀察執(zhí)行計劃的輸出，發(fā)現(xiàn)在執(zhí)行insert的時候，同時執(zhí)行了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的結(jié)果不符合預(yù)期
rule達不到期望的結(jié)果。
比如我們希望編寫rule實現(xiàn)數(shù)據(jù)表的審計。
創(chuàng)建數(shù)據(jù)表和rule，這個rule的目的是在插入數(shù)據(jù)時記錄插入的動作和數(shù)據(jù)。

[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

插入數(shù)據(jù)

[local]:5432 pg12@testdb=# INSERT INTO tbl2 ( created, some_value ) VALUES ( clock_timestamp(), '123' );
INSERT 0 1
Time: 3.028 ms

查詢數(shù)據(jù)

[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字段值應(yīng)與tbl2的created一致，但實際上卻不一致。

建議：rule需謹(jǐn)慎使用，能不用最好就不要使用：）

參考資料
Don’t Do This
To rule or not to rule – that is the question