Thread: "ON INSERT rule may not use OLD" on simple query

Postgresql 7.3.2 gives the error "ON INSERT rule may not use OLD" when
trying to define the following rule:

        CREATE TABLE numbers ( number INTEGER );
        CREATE TABLE other_numbers ( number INTEGER );

        CREATE OR REPLACE RULE number_rule AS
        ON INSERT TO numbers
        DO INSERT INTO other_numbers
          SELECT 1 EXCEPT SELECT 2;

although the rule, even upon close inspection, seems to make no
reference at all to the OLD pseudo-relation.  Given the above table
definitions, the query:

        SELECT 1 EXCEPT SELECT 2;

by itself runs fine and returns 1 as expected.  (We have tested this
against both the unstable Debian package for 7.3.2, and against a copy
compiled locally using the standard source archive.)

--
Brandon Craig Rhodes                         http://www.rhodesmill.org/brandon
Georgia Tech                                            brandon@oit.gatech.edu

Brandon Craig Rhodes <brandon@oit.gatech.edu> writes:
> Postgresql 7.3.2 gives the error "ON INSERT rule may not use OLD" when
> trying to define the following rule:

Hm, I guess no one's tried to use UNION/INTERSECT/EXCEPT in ON INSERT
rules lately :-(

I've applied the attached patch, which seems to fix this problem without
introducing any new ones (leftmostRTI is one anyway in the better-tested
cases).  There may be some other issues lurking in this general area,
though...

Thanks for the report!  (BTW, CVS tip has some code in it to address
your outer-join optimization complaint of a couple months back.)

            regards, tom lane

*** src/backend/parser/analyze.c.orig    Mon Oct 21 18:06:19 2002
--- src/backend/parser/analyze.c    Mon Feb 10 22:59:32 2003
***************
*** 1796,1801 ****
--- 1796,1806 ----
       * leftmost select and common datatypes of topmost set operation. Also
       * make lists of the dummy vars and their names for use in parsing
       * ORDER BY.
+      *
+      * Note: we use leftmostRTI as the varno of the dummy variables.
+      * It shouldn't matter too much which RT index they have, as long
+      * as they have one that corresponds to a real RT entry; else funny
+      * things may happen when the tree is mashed by rule rewriting.
       */
      qry->targetList = NIL;
      targetvars = NIL;
***************
*** 1814,1820 ****
                              -1,
                              colName,
                              false);
!         expr = (Node *) makeVar(1,
                                  leftResdom->resno,
                                  colType,
                                  -1,
--- 1819,1825 ----
                              -1,
                              colName,
                              false);
!         expr = (Node *) makeVar(leftmostRTI,
                                  leftResdom->resno,
                                  colType,
                                  -1,