Shulou(Shulou.com)06/01 Report--

No zuo no die series, from wiki of pg.

This is the second part, do not use rule.

The reason is:

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 is simply rewriting SQL to bring unnecessary complexity, which is sometimes difficult to understand.

Side effects of rule

Rule can have side effects and produce "strange" results. The following examples are given:

Create a datasheet

[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 (# id INT4 PRIMARY KEY,pg12@testdb (# value INT4 NOT NULLpg12@testdb (#); CREATE TABLETime: 212.508 ms

Create a rule

[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

This rule wants to update value instead of insert if the same ID value is encountered during insertion.

Insert the first record below

[local]: 5432 pg12@testdb=# explain (analyze true,verbose true) insert into tbl (id,value) values 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=#

To insert the first record, the id and value are 1 and 1, respectively, but the resulting value is 2. Observe the output of the execution plan and find that when the insert is executed, the update statement is executed at the same time.

In other words, rule turns the insert statement into an insert statement & a update statement, that is:

INSERT INTO tbl (id, value) SELECT 1, 1WHERE NOT (EXISTS (SELECT * FROM v WHERE id = 1); UPDATE tblSET value = value + 1WHERE id = 1 AND (EXISTS (SELECT * FROM tbl WHERE id = 1))

The result of rule is not as expected.

Rule failed to achieve the desired results.

For example, we want to write rule to audit data tables.

Create a datasheet and rule. The purpose of this rule is to record the inserted actions and data when inserting data.

[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 (# 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

Insert data

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

Query data

[local]: 5432 pg12@testdb=# select * from tbl2; id | created | some_value-+-1 | 2019-10-11 11VO 1415 19.174961508 | 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=#

The value of the t_created field in the log table should be the same as the created of tbl2, but it is actually inconsistent.

Suggestion: rule should be used with caution, it is best not to use it if you don't need it:)

references

Don't Do This

To rule or not to rule-that is the question

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.