PolarDB:Rules on INSERT, UPDATE, and DELETE

Step by step: tracing a rule through query rewriting

To trace what happens internally, consider a rule that logs changes to the sl_avail column in the shoelace_data table. Start by creating a log table and the rule:

CREATE TABLE shoelace_log (
    sl_name    text,          -- shoelace changed
    sl_avail   integer,       -- new available value
    log_who    text,          -- who made the change
    log_when   timestamp      -- when the change occurred
);

CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
    WHERE NEW.sl_avail <> OLD.sl_avail
    DO INSERT INTO shoelace_log VALUES (
                                    NEW.sl_name,
                                    NEW.sl_avail,
                                    current_user,
                                    current_timestamp
                                );

Run an update:

UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';

Check the log:

SELECT * FROM shoelace_log;

 sl_name | sl_avail | log_who | log_when
---------+----------+---------+----------------------------------
 sl7     |        6 | Al      | Tue Oct 20 16:14:45 1998 MET DST
(1 row)

Here is what happens at each stage. The parser produces:

UPDATE shoelace_data SET sl_avail = 6
  FROM shoelace_data shoelace_data
 WHERE shoelace_data.sl_name = 'sl7';

The rule log_shoelace is a qualified ALSO rule, so the system returns two query trees: a modified rule action and the original query tree.

Step 1 — Incorporate the original query's range table into the rule action:

INSERT INTO shoelace_log VALUES (
       new.sl_name, new.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data;

Step 2 — Add the rule qualification to restrict output to rows where sl_avail changes:

INSERT INTO shoelace_log VALUES (
       new.sl_name, new.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data
 WHERE new.sl_avail <> old.sl_avail;

Step 3 — Add the original query's qualification to restrict output to rows the original query would have touched:

INSERT INTO shoelace_log VALUES (
       new.sl_name, new.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data
 WHERE new.sl_avail <> old.sl_avail
   AND shoelace_data.sl_name = 'sl7';

Step 4 — Replace NEW references with target list entries or matching column references from the result relation:

INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data
 WHERE 6 <> old.sl_avail
   AND shoelace_data.sl_name = 'sl7';

Step 5 — Replace OLD references with result relation references:

INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data new, shoelace_data old,
       shoelace_data shoelace_data
 WHERE 6 <> shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';

Because the rule is ALSO, the system also outputs the original query tree. The final output is two query trees equivalent to:

INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE 6 <> shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';

UPDATE shoelace_data SET sl_avail = 6
 WHERE sl_name = 'sl7';

These execute in this order, which is exactly what the rule is designed to do.

The substitutions and added qualifications also handle edge cases correctly. If the original query updated sl_color instead of sl_avail:

UPDATE shoelace_data SET sl_color = 'green'
 WHERE sl_name = 'sl7';

No log entry would be written. Because the original query's target list has no entry for sl_avail, NEW.sl_avail is replaced by shoelace_data.sl_avail, making the rule action's WHERE clause evaluate to shoelace_data.sl_avail <> shoelace_data.sl_avail, which is never true.

The rule also handles bulk updates correctly. For example:

UPDATE shoelace_data SET sl_avail = 0
 WHERE sl_color = 'black';

This affects four rows (sl1, sl2, sl3, sl4), but sl3 already has sl_avail = 0. The rule generates the extra query tree:

INSERT INTO shoelace_log
SELECT shoelace_data.sl_name, 0,
       current_user, current_timestamp
  FROM shoelace_data
 WHERE 0 <> shoelace_data.sl_avail
   AND shoelace_data.sl_color = 'black';

This inserts exactly three log entries — correct, because sl3 had no change. The execution order matters here: if the UPDATE ran first, all rows would already be set to zero, and the logging INSERT would find no rows matching 0 <> shoelace_data.sl_avail.

Protecting views from modification

To prevent INSERT, UPDATE, or DELETE on a view, create INSTEAD NOTHING rules that discard the incoming query tree:

CREATE RULE shoe_ins_protect AS ON INSERT TO shoe
    DO INSTEAD NOTHING;
CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe
    DO INSTEAD NOTHING;
CREATE RULE shoe_del_protect AS ON DELETE TO shoe
    DO INSTEAD NOTHING;

Any attempt to modify the shoe view produces an empty query tree list — nothing is optimized or executed.

Making views updatable

A more flexible approach is to rewrite queries on the view into operations on the underlying tables. For the shoelace view:

CREATE RULE shoelace_ins AS ON INSERT TO shoelace
    DO INSTEAD
    INSERT INTO shoelace_data VALUES (
           NEW.sl_name,
           NEW.sl_avail,
           NEW.sl_color,
           NEW.sl_len,
           NEW.sl_unit
    );

CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
    DO INSTEAD
    UPDATE shoelace_data
       SET sl_name = NEW.sl_name,
           sl_avail = NEW.sl_avail,
           sl_color = NEW.sl_color,
           sl_len = NEW.sl_len,
           sl_unit = NEW.sl_unit
     WHERE sl_name = OLD.sl_name;

CREATE RULE shoelace_del AS ON DELETE TO shoelace
    DO INSTEAD
    DELETE FROM shoelace_data
     WHERE sl_name = OLD.sl_name;

Supporting RETURNING queries on views

To support RETURNING on the view, include a RETURNING clause in the rule. For a single-table view this is straightforward:

CREATE RULE shoelace_ins AS ON INSERT TO shoelace
    DO INSTEAD
    INSERT INTO shoelace_data VALUES (
           NEW.sl_name,
           NEW.sl_avail,
           NEW.sl_color,
           NEW.sl_len,
           NEW.sl_unit
    )
    RETURNING
           shoelace_data.*,
           (SELECT shoelace_data.sl_len * u.un_fact
            FROM unit u WHERE shoelace_data.sl_unit = u.un_name);

One rule supports both INSERT and INSERT RETURNING queries on the view — the RETURNING clause is ignored for plain INSERT.

For join views, the RETURNING clause is more complex to construct.

Chaining rules across multiple tables

Rules can chain across several tables and views. The following example uses two auxiliary tables to handle batch inventory updates without manually updating the shoelace view each time a shipment arrives:

CREATE TABLE shoelace_arrive (
    arr_name    text,
    arr_quant   integer
);

CREATE TABLE shoelace_ok (
    ok_name     text,
    ok_quant    integer
);

CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok
    DO INSTEAD
    UPDATE shoelace
       SET sl_avail = sl_avail + NEW.ok_quant
     WHERE sl_name = NEW.ok_name;

Load the arriving inventory into shoelace_arrive:

SELECT * FROM shoelace_arrive;

 arr_name | arr_quant
----------+-----------
 sl3      |        10
 sl6      |        20
 sl8      |        20
(3 rows)

Check the current state of the shoelace inventory:

SELECT * FROM shoelace;

 sl_name  | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
----------+----------+----------+--------+---------+-----------
 sl1      |        5 | black    |     80 | cm      |        80
 sl2      |        6 | black    |    100 | cm      |       100
 sl7      |        6 | brown    |     60 | cm      |        60
 sl3      |        0 | black    |     35 | inch    |      88.9
 sl4      |        8 | black    |     40 | inch    |     101.6
 sl8      |        1 | brown    |     40 | inch    |     101.6
 sl5      |        4 | brown    |      1 | m       |       100
 sl6      |        0 | brown    |    0.9 | m       |        90
(8 rows)

Move the arrived inventory in:

INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;

Check the updated inventory and log:

SELECT * FROM shoelace ORDER BY sl_name;

 sl_name  | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
----------+----------+----------+--------+---------+-----------
 sl1      |        5 | black    |     80 | cm      |        80
 sl2      |        6 | black    |    100 | cm      |       100
 sl7      |        6 | brown    |     60 | cm      |        60
 sl4      |        8 | black    |     40 | inch    |     101.6
 sl3      |       10 | black    |     35 | inch    |      88.9
 sl8      |       21 | brown    |     40 | inch    |     101.6
 sl5      |        4 | brown    |      1 | m       |       100
 sl6      |       20 | brown    |    0.9 | m       |        90
(8 rows)

SELECT * FROM shoelace_log;

 sl_name | sl_avail | log_who| log_when
---------+----------+--------+----------------------------------
 sl7     |        6 | Al     | Tue Oct 20 19:14:45 1998 MET DST
 sl3     |       10 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 sl6     |       20 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 sl8     |       21 | Al     | Tue Oct 20 19:25:16 1998 MET DST
(4 rows)

A single INSERT ... SELECT triggered a four-step rule chain. The query-tree transformation proceeds as follows:

1. The parser produces an INSERT INTO shoelace_ok query tree.

2. The rule shoelace_ok_ins rewrites it into an UPDATE on the shoelace view, discarding the original INSERT.

3. The rule shoelace_upd rewrites the UPDATE on shoelace into an UPDATE on shoelace_data, discarding the previous query tree.

4. The _RETURN rule is applied, expanding the view reference.

5. The rule log_shoelace generates an additional INSERT INTO shoelace_log query tree.

The final output is two query trees — equivalent to an INSERT INTO shoelace_log and an UPDATE shoelace_data — that the planner and executor process.

Data from one relation, inserted into a second, converted to updates on a third, then on a fourth, and logged in a fifth is reduced to two queries.

Performance note: The rule system may introduce duplicate entries in the range table. The planner does not eliminate these extra entries, which can result in additional sequential scans. In this example, shoelace_data appears twice in the range table, causing one unnecessary extra scan — the same redundant scan occurs in the UPDATE as well. This is a known trade-off of the rule system's rewriting approach.

The execution plan for the INSERT step is:

Nested Loop
  ->  Merge Join
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on s
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on shoelace_arrive
  ->  Seq Scan on shoelace_data

Without the extra range table entry, the plan would be:

Merge Join
  ->  Seq Scan
        ->  Sort
              ->  Seq Scan on s
  ->  Seq Scan
        ->  Sort
              ->  Seq Scan on shoelace_arrive

Rewriting DELETE through multiple view layers

To demonstrate the depth of the rule system's rewriting capability, add some shoelaces with unusual colors:

INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);

Create a view to identify shoelaces that do not match any shoe color:

CREATE VIEW shoelace_mismatch AS
    SELECT * FROM shoelace WHERE NOT EXISTS
        (SELECT shoename FROM shoe WHERE slcolor = sl_color);

Its output:

SELECT * FROM shoelace_mismatch;

 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
---------+----------+----------+--------+---------+-----------
 sl9     |        0 | pink     |     35 | inch    |      88.9
 sl10    |     1000 | magenta  |     40 | inch    |     101.6

Create a second view to identify out-of-stock mismatches:

CREATE VIEW shoelace_can_delete AS
    SELECT * FROM shoelace_mismatch WHERE sl_avail = 0;

Delete the out-of-stock mismatches through this view chain:

DELETE FROM shoelace WHERE EXISTS
    (SELECT * FROM shoelace_can_delete
             WHERE sl_name = shoelace.sl_name);

Check the result:

SELECT * FROM shoelace;

 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
---------+----------+----------+--------+---------+-----------
 sl1     |        5 | black    |     80 | cm      |        80
 sl2     |        6 | black    |    100 | cm      |       100
 sl7     |        6 | brown    |     60 | cm      |        60
 sl4     |        8 | black    |     40 | inch    |     101.6
 sl3     |       10 | black    |     35 | inch    |      88.9
 sl8     |       21 | brown    |     40 | inch    |     101.6
 sl10    |     1000 | magenta  |     40 | inch    |     101.6
 sl5     |        4 | brown    |      1 | m       |       100
 sl6     |       20 | brown    |    0.9 | m       |        90
(9 rows)

sl9 (pink, zero stock) is deleted. sl10 (magenta, 1000 in stock) is retained because it does not meet the zero-stock condition.

A DELETE on a view, qualified by a subquery that spans four nested or joined views — one of which has its own subquery qualification containing another view and uses calculated view columns — is rewritten into a single query tree that deletes the target rows from the underlying table.