"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,