CODEWITHSUNDEEP
postgresql
zoran
zoran

Reputation: 81

INSERT rule does not always fire

We have a Postgres 11 server. There is table of machines and history table which contains the changes within machines table.

There is insert rule on machines table:

CREATE RULE machine_insert AS ON INSERT TO machines
  DO ALSO
    INSERT INTO history(machine_name, stamp, ...)
      VALUES(new.machine_name, now(), ...);

The insert into history works perfectly if the statement looks like this

INSERT INTO machines(machine_name, ....)
  SELECT machine_name from new_machines; -- No condition

But if there is a JOIN and WHERE condition in the select statement, the rule does not fire at all. e.g.

INSERT INTO machines(machine_name, ....)
  SELECT machine_name 
  FROM new_machines N 
    LEFT OUTER JOIN machines D on D.machine_name = N.machine_name
  WHERE D.machine_name is null -- "Anti Join"

As I understand the documentation, the select statement in my case above should really have no impact on the rule(s). Remark: simple WHERE condition (WHERE machine_type is not null) does not disable the rule. What am I doing wrong?

Upvotes: 1

Views: 74

Answers (1)

zoran
zoran

Reputation: 81

The EXPLAIN ANALYZE revealed the cause of the issue. Here is the output:

QUERY PLAN                                                                                                                                                 
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on machines  (cost=17.88..14522.89 rows=335609 width=227) (actual time=12586.221..12586.223 rows=0 loops=1)                                          
  ->  Hash Anti Join  (cost=17.88..14522.89 rows=335609 width=227) (actual time=0.029..347.021 rows=335959 loops=1)                                        
        Hash Cond: (n.machine_name = d.machine_name)                                                                                                                           
        ->  Seq Scan on new_machines n  (cost=0.00..8588.59 rows=335959 width=105) (actual time=0.013..59.920 rows=335959 loops=1)                    
        ->  Hash  (cost=13.50..13.50 rows=350 width=4) (actual time=0.002..0.003 rows=0 loops=1)                                                           
              Buckets: 1024  Batches: 1  Memory Usage: 8kB                                                                                                 
              ->  Seq Scan on machines d  (cost=0.00..13.50 rows=350 width=4) (actual time=0.002..0.002 rows=0 loops=1)                                     
Planning Time: 1.497 ms                                                                                                                                    
Execution Time: 12586.289 ms                                                                                                                               
                                                                                                                                                           
Insert on history  (cost=0.84..30581.85 rows=121724 width=1493) (actual time=212.911..212.912 rows=0 loops=1)                                              
  ->  Merge Anti Join  (cost=0.84..30581.85 rows=121724 width=1493) (actual time=212.909..212.909 rows=0 loops=1)                                          
        Merge Cond: (n.machine_name = d.machine_name)                                                                                                                          
        ->  Index Scan using new_machines_idx on new_machines n  (cost=0.42..12138.50 rows=335959 width=15) (actual time=0.005..72.153
        ->  Index Only Scan using machines_id_key on machines d  (cost=0.42..11274.51 rows=214235 width=4) (actual time=0.005..81.540 rows=335959 loops=1)   
              Heap Fetches: 335959                                                                                                                         
Planning Time: 0.264 ms                                                                                                                                    
Execution Time: 212.975 ms

The rule does fire (gets executed) and it does work as designed. The query resulting from the RULE (see Insert on history above) really tries to add records to history, but under the WHERE condition that the record must not exist in machines table which is now FALSE for all records as INSERT into machines already went through, note Scan using machines_id_key ... rows=335959.

Upvotes: 1

Related Questions