Proposal / proof of concept: Triggers on VIEWs - Mailing list pgsql-hackers

I've been thinking about the idea of allowing triggers on views as a
way of implementing updateable views. This would not be a replacement
for rules in all situations, but rather a complementary feature.
It would also complement the SQL standard feature allowing updates to
simple views, which is fine except that in practice I tend to have
more complex views, that would need custom code to update, and often
this is very difficult to achieve with rules.

The way this is done in some other databases is "INSTEAD OF"
triggers. These triggers are neither BEFORE nor AFTER, but
INSTEAD. And they are also typically ROW-level triggers, eg:

CREATE TRIGGER my_trig INSTEAD OF UPDATE ON my_view
  FOR EACH ROW EXECUTE PROCEDURE my_trig_fn();

The trigger fires for each row that needs to be updated in the view,
and has access to OLD and NEW tuples containing all the columns from
the view. The trigger function takes full responsibility for updating
the base tables, which works OK provided that the view exposes
sufficient key columns to allow the relevant tuples in the base tables
to be identified.

I've used this feature in Oracle in the past, and found it very
useful. What I'm proposing is something similar to that. The main
restrictions placed on INSTEAD OF triggers in Oracle are:

1). They're only allowed on views not tables.
2). They may only be ROW-level triggers.
3). They don't support WHEN conditions.
4). They can't be used with for UPDATE OF <column list>.

1 and 2 could possibly be relaxed, but IMO they are sensible
restrictions at least in the first pass. 3 and 4 ensure that there is
an *unconditional* action to take instead of the VIEW update.

One thing that I think I would do differently from Oracle is the
following: in Oracle the return value of an INSTEAD OF trigger is
ignored, and it always just assumes that the trigger performed the
required update. In PostgreSQL, I think it might be more consistent
with the existing triggers to have the trigger return the OLD tuple
for DELETEs and the (possibly modified) NEW tuple for INSERT and
UPDATE. This would allow RETURNING clauses to show what was actually
added to the view by the trigger. A return value of NULL would
indicate that the trigger did nothing.



I've been thinking about how I would implement this, and attached is a
proof-of-concept patch. This patch doesn't actually have any trigger
definition or execution code (although I think writing that part
should be fairly mechanical). It's purpose is to get an idea of the
necessary changes to the rewriter, planner and executor. The patch
just raises NOTICEs in the executor at the point where the INSTEAD OF
triggers would be fired, showing what data would be available to those
triggers.

It's quite a small patch, which I hope isn't a sign that I've vastly
oversimplified this or overlooked something crucial. It works
basically as follows:

- In the rewriter, if the target of an UPDATE or DELETE is a VIEW,
then instead of simply replacing that view with its subquery, it now
adds the subquery to the end of the original query's rtable list, and
leaves the original RTE for the VIEW in place as the query's
resultRelation and for any returningList Vars. All other parts of the
query, including the jointree fromlist are modified to refer to the
VIEW's subquery, not the original VIEW RTE. For an INSERT, the
rewriter does nothing, leaving the VIEW RTE as the query
resultRelation.

- The planner largely ignores the VIEW relation RTE, since it does not
appear in the jointree fromlist. It is only used as the query target,
giving a plan with a ModifyTable node at the top and a subquery from
the view's base tables, joined to any other tables in the query,
together with any view conditions combined with any user conditions.

- In the executor, nodeModifyTable needs a few changes to be able to
handle a VIEW as the target relation. BEFORE and AFTER ROW triggers
are disallowed on views (I don't see a need for them if we have
INSTEAD OF triggers, and it's not clear how they would work anyway, in
the absence of a real table CTID). BEFORE and AFTER STATEMENT triggers
on the other hand, ought to work as-is. The INSTEAD OF triggers would
fire for each tuple coming from the subquery instead of the normal
heap_update/insert/delete().


Does this sound like a useful feature? Is this a sane approach to
implementing it? If not, has anyone else given any thought as to how
it might be implemented?

If this approach is valid, I believe that I should have time to put
together a more complete patch for the next commitfest.

Regards,
Dean

---

P.S. Here's some test output (note: the table is never actually updated
in this test, the NOTICEs just show what triggers would have done):

CREATE TABLE foo(a int PRIMARY KEY, b int, c int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
INSERT INTO foo
  SELECT g, g*10, g*100 FROM generate_series(1,10000) AS g;
INSERT 0 10000
CREATE VIEW foo_v AS
  SELECT b AS bb, c AS cc, a AS aa FROM foo WHERE a%2 = 0;
CREATE VIEW
INSERT INTO foo_v (aa,bb,cc)
  SELECT g, g*10, g*100 FROM generate_series(10001,10005) AS g;
NOTICE:  Trigger would INSERT into view "foo_v" NEW=(bb=100010,
cc=1000100, aa=10001)
NOTICE:  Trigger would INSERT into view "foo_v" NEW=(bb=100020,
cc=1000200, aa=10002)
NOTICE:  Trigger would INSERT into view "foo_v" NEW=(bb=100030,
cc=1000300, aa=10003)
NOTICE:  Trigger would INSERT into view "foo_v" NEW=(bb=100040,
cc=1000400, aa=10004)
NOTICE:  Trigger would INSERT into view "foo_v" NEW=(bb=100050,
cc=1000500, aa=10005)
INSERT 0 5
UPDATE foo_v SET cc=0 WHERE aa < 10;
NOTICE:  Trigger would UDPATE view "foo_v" OLD=(20,200,2) NEW=(bb=20,
cc=0, aa=2)
NOTICE:  Trigger would UDPATE view "foo_v" OLD=(40,400,4) NEW=(bb=40,
cc=0, aa=4)
NOTICE:  Trigger would UDPATE view "foo_v" OLD=(60,600,6) NEW=(bb=60,
cc=0, aa=6)
NOTICE:  Trigger would UDPATE view "foo_v" OLD=(80,800,8) NEW=(bb=80,
cc=0, aa=8)
UPDATE 4
EXPLAIN UPDATE foo_v SET cc=0 WHERE aa < 10;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Update  (cost=70.93..188.18 rows=18 width=18)
   ->  Bitmap Heap Scan on foo  (cost=70.93..188.18 rows=18 width=18)
         Recheck Cond: (a < 10)
         Filter: ((a % 2) = 0)
         ->  Bitmap Index Scan on foo_pkey  (cost=0.00..70.93 rows=3557 width=0)
               Index Cond: (a < 10)
(6 rows)

DELETE FROM foo_v WHERE aa = 50;
NOTICE:  Trigger would DELETE from view "foo_v" OLD=(500,5000,50)
DELETE 1
EXPLAIN DELETE FROM foo_v WHERE aa = 50;
                                QUERY PLAN
---------------------------------------------------------------------------
 Delete  (cost=0.00..8.27 rows=1 width=18)
   ->  Index Scan using foo_pkey on foo  (cost=0.00..8.27 rows=1 width=18)
         Index Cond: (a = 50)
         Filter: ((a % 2) = 0)
(4 rows)

INSERT INTO foo_v (aa,bb,cc)
  SELECT g, g*10, g*100 FROM generate_series(10001,10005) AS g
  RETURNING aa,bb,cc;
NOTICE:  Trigger would INSERT into view "foo_v" NEW=(bb=100010,
cc=1000100, aa=10001)
NOTICE:  Trigger would INSERT into view "foo_v" NEW=(bb=100020,
cc=1000200, aa=10002)
NOTICE:  Trigger would INSERT into view "foo_v" NEW=(bb=100030,
cc=1000300, aa=10003)
NOTICE:  Trigger would INSERT into view "foo_v" NEW=(bb=100040,
cc=1000400, aa=10004)
NOTICE:  Trigger would INSERT into view "foo_v" NEW=(bb=100050,
cc=1000500, aa=10005)
  aa   |   bb   |   cc
-------+--------+---------
 10001 | 100010 | 1000100
 10002 | 100020 | 1000200
 10003 | 100030 | 1000300
 10004 | 100040 | 1000400
 10005 | 100050 | 1000500
(5 rows)

INSERT 0 5
CREATE VIEW foo_vv AS
  SELECT cc AS c, aa AS a, bb AS b FROM foo_v WHERE aa%3 = 0;
CREATE VIEW
SELECT COUNT(*) FROM foo_vv;
 count
-------
  1666
(1 row)

SELECT * FROM foo_vv ORDER BY a LIMIT 5;
  c   | a  |  b
------+----+-----
  600 |  6 |  60
 1200 | 12 | 120
 1800 | 18 | 180
 2400 | 24 | 240
 3000 | 30 | 300
(5 rows)

UPDATE foo_vv SET b=a*11 WHERE a=6;
NOTICE:  Trigger would UDPATE view "foo_vv" OLD=(600,6,60) NEW=(c=600,
a=6, b=66)
UPDATE 1
EXPLAIN UPDATE foo_vv SET b=a*11 WHERE a=6;
                                QUERY PLAN
---------------------------------------------------------------------------
 Update  (cost=0.00..8.28 rows=1 width=18)
   ->  Index Scan using foo_pkey on foo  (cost=0.00..8.28 rows=1 width=18)
         Index Cond: (a = 6)
         Filter: (((a % 2) = 0) AND ((a % 3) = 0))
(4 rows)

CREATE VIEW foo_vvv AS
  SELECT a, b, (SELECT 1 FROM foo_vv AS vv2 WHERE vv2.a = vv1.b/12) AS c
    FROM foo_vv AS vv1;
CREATE VIEW
DELETE FROM foo_vvv WHERE a=12;
NOTICE:  Trigger would DELETE from view "foo_vvv" OLD=(12,120,)
DELETE 1
EXPLAIN DELETE FROM foo_vvv WHERE a=12;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Delete  (cost=0.00..16.56 rows=1 width=14)
   ->  Index Scan using foo_pkey on foo  (cost=0.00..16.56 rows=1 width=14)
         Index Cond: (a = 12)
         Filter: (((a % 2) = 0) AND ((a % 3) = 0))
         SubPlan 1
           ->  Index Scan using foo_pkey on foo  (cost=0.00..8.28
rows=1 width=0)
                 Index Cond: (a = (public.foo.b / 12))
                 Filter: (((a % 2) = 0) AND ((a % 3) = 0))
(8 rows)

UPDATE foo_vvv SET c=NULL WHERE a=36;
NOTICE:  Trigger would UDPATE view "foo_vvv" OLD=(36,360,1) NEW=(a=36,
b=360, c=null)
UPDATE 1
EXPLAIN UPDATE foo_vvv SET c=NULL WHERE a=36;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Update  (cost=0.00..16.56 rows=1 width=14)
   ->  Index Scan using foo_pkey on foo  (cost=0.00..16.56 rows=1 width=14)
         Index Cond: (a = 36)
         Filter: (((a % 2) = 0) AND ((a % 3) = 0))
         SubPlan 1
           ->  Index Scan using foo_pkey on foo  (cost=0.00..8.28
rows=1 width=0)
                 Index Cond: (a = (public.foo.b / 12))
                 Filter: (((a % 2) = 0) AND ((a % 3) = 0))
(8 rows)

INSERT INTO foo_vvv VALUES (1,2,3);
NOTICE:  Trigger would INSERT into view "foo_vvv" NEW=(a=1, b=2, c=3)
INSERT 0 1
EXPLAIN INSERT INTO foo_vvv VALUES (1,2,3);
                   QUERY PLAN
------------------------------------------------
 Insert  (cost=0.00..0.01 rows=1 width=0)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
(2 rows)

CREATE RULE foo_vv_rule AS ON UPDATE TO foo_vv
  DO INSTEAD SELECT 'foo_vv_rule doing nothing';
CREATE RULE
UPDATE foo_vv SET b=a*11 WHERE a=6;
         ?column?
---------------------------
 foo_vv_rule doing nothing
(1 row)

UPDATE 0
EXPLAIN UPDATE foo_vv SET b=a*11 WHERE a=6;
                             QUERY PLAN
--------------------------------------------------------------------
 Index Scan using foo_pkey on foo  (cost=0.00..8.28 rows=1 width=0)
   Index Cond: (a = 6)
   Filter: (((a % 2) = 0) AND ((a % 3) = 0))
(3 rows)

UPDATE foo_vvv SET b=a*11 WHERE a=6;
NOTICE:  Trigger would UDPATE view "foo_vvv" OLD=(6,60,) NEW=(a=6, b=66, c=null)
UPDATE 1
EXPLAIN UPDATE foo_vvv SET b=a*11 WHERE a=6;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Update  (cost=0.00..24.84 rows=1 width=14)
   ->  Index Scan using foo_pkey on foo  (cost=0.00..24.84 rows=1 width=14)
         Index Cond: (a = 6)
         Filter: (((a % 2) = 0) AND ((a % 3) = 0))
         SubPlan 1
           ->  Index Scan using foo_pkey on foo  (cost=0.00..8.28
rows=1 width=0)
                 Index Cond: (a = (public.foo.b / 12))
                 Filter: (((a % 2) = 0) AND ((a % 3) = 0))
         SubPlan 2
           ->  Index Scan using foo_pkey on foo  (cost=0.00..8.28
rows=1 width=0)
                 Index Cond: (a = (public.foo.b / 12))
                 Filter: (((a % 2) = 0) AND ((a % 3) = 0))
(12 rows)

CREATE RULE foo_vvv_rule AS ON UPDATE TO foo_vvv
  DO INSTEAD UPDATE foo_v SET bb=NEW.b, cc=NEW.c WHERE aa=OLD.a;
CREATE RULE
UPDATE foo_vvv SET b=a*11 WHERE a >= 30 AND a <= 36;
NOTICE:  Trigger would UDPATE view "foo_v" OLD=(300,3000,30)
NEW=(bb=330, cc=null, aa=30)
NOTICE:  Trigger would UDPATE view "foo_v" OLD=(360,3600,36)
NEW=(bb=396, cc=1, aa=36)
UPDATE 2
EXPLAIN UPDATE foo_vvv SET b=a*11 WHERE a >= 30 AND a <= 36;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Update  (cost=4.78..79.30 rows=1 width=32)
   ->  Nested Loop  (cost=4.78..79.30 rows=1 width=32)
         ->  Bitmap Heap Scan on foo  (cost=4.78..62.73 rows=1 width=14)
               Recheck Cond: ((a >= 30) AND (a <= 36))
               Filter: (((a % 2) = 0) AND ((a % 3) = 0))
               ->  Bitmap Index Scan on foo_pkey  (cost=0.00..4.78
rows=53 width=0)
                     Index Cond: ((a >= 30) AND (a <= 36))
         ->  Index Scan using foo_pkey on foo  (cost=0.00..8.27 rows=1 width=18)
               Index Cond: (a = public.foo.a)
               Filter: ((a % 2) = 0)
         SubPlan 1
           ->  Index Scan using foo_pkey on foo  (cost=0.00..8.28
rows=1 width=0)
                 Index Cond: (a = (public.foo.b / 12))
                 Filter: (((a % 2) = 0) AND ((a % 3) = 0))
(14 rows)

CREATE OR REPLACE RULE foo_vvv_rule AS ON UPDATE TO foo_vvv
  DO INSTEAD UPDATE foo_v SET bb=NEW.b, cc=NEW.c WHERE aa=OLD.a
             RETURNING NEW.a, NEW.b, NEW.c;
CREATE RULE
UPDATE foo_vvv SET b=a*11 WHERE a >= 30 AND a <= 36 RETURNING a, b, c;
NOTICE:  Trigger would UDPATE view "foo_v" OLD=(300,3000,30)
NEW=(bb=330, cc=null, aa=30)
NOTICE:  Trigger would UDPATE view "foo_v" OLD=(360,3600,36)
NEW=(bb=396, cc=1, aa=36)
 a  |  b  | c
----+-----+---
 30 | 330 |
 36 | 396 | 1
(2 rows)

UPDATE 2
EXPLAIN UPDATE foo_vvv SET b=a*11 WHERE a >= 30 AND a <= 36 RETURNING a, b, c;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Update  (cost=4.78..79.30 rows=1 width=32)
   ->  Nested Loop  (cost=4.78..79.30 rows=1 width=32)
         ->  Bitmap Heap Scan on foo  (cost=4.78..62.73 rows=1 width=14)
               Recheck Cond: ((a >= 30) AND (a <= 36))
               Filter: (((a % 2) = 0) AND ((a % 3) = 0))
               ->  Bitmap Index Scan on foo_pkey  (cost=0.00..4.78
rows=53 width=0)
                     Index Cond: ((a >= 30) AND (a <= 36))
         ->  Index Scan using foo_pkey on foo  (cost=0.00..8.27 rows=1 width=18)
               Index Cond: (a = public.foo.a)
               Filter: ((a % 2) = 0)
         SubPlan 1
           ->  Index Scan using foo_pkey on foo  (cost=0.00..8.28
rows=1 width=0)
                 Index Cond: (a = (public.foo.b / 12))
                 Filter: (((a % 2) = 0) AND ((a % 3) = 0))
   SubPlan 2
     ->  Index Scan using foo_pkey on foo  (cost=0.00..8.28 rows=1 width=0)
           Index Cond: (a = (public.foo.b / 12))
           Filter: (((a % 2) = 0) AND ((a % 3) = 0))
(18 rows)