bug - NEW and OLD in sub-selects in rules

Thanks to Tom's excellent work in producing a patch in response to our
question on Monday, we have been able to move forward and uncover
another problem.

We had been avoiding sub-selects within rules because they could not
reference the NEW and OLD pseudo-relations (it would tell us `Relation
"*NEW*" does not exist' and so forth), which we assumed was because of
some obscure scoping limitation with respect to those two relations.
Imagine our surprise when we discovered that, once we started using
the EXCEPT clauses that Tom's Monday patch fixed, we were also able to
use NEW and OLD within sub-queries - but *only* if the parent select
contained an EXCEPT clause!

The following complete test case attempts to create four rules, which
are identical except that the SELECTS of the first and third are
supplemented with EXCEPT clauses. The result? The first and third
can use NEW and OLD just fine, while the second and fourth cannot.
Our guess is that this is a bug.

CREATE TABLE numbers ( number INTEGER );

CREATE RULE number_insert AS
ON INSERT TO numbers DO
SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub
EXCEPT SELECT 1;

CREATE RULE number_insert_fails AS
ON INSERT TO numbers DO
SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub;

CREATE RULE number_delete AS
ON DELETE TO numbers DO
SELECT * FROM (SELECT * FROM numbers WHERE number = OLD.number) AS sub
EXCEPT SELECT 1;

CREATE RULE number_delete2 AS
ON DELETE TO numbers DO
SELECT * FROM (SELECT * FROM numbers WHERE number = OLD.number) AS sub;

--
Brandon Craig Rhodes http://www.rhodesmill.org/brandon
Georgia Tech brandon(at)oit(dot)gatech(dot)edu

Brandon Craig Rhodes <brandon(at)oit(dot)gatech(dot)edu> writes:
> We had been avoiding sub-selects within rules because they could not
> reference the NEW and OLD pseudo-relations (it would tell us `Relation
> "*NEW*" does not exist' and so forth), which we assumed was because of
> some obscure scoping limitation with respect to those two relations.

I hate to disappoint you, but they were doing the right thing. In
general, NEW and OLD are relations implicitly added to rule queries,
and so you were effectively doing something like

... FROM tab1 AS new, (select ... where ... x = new.x) AS sub

which is an illegal cross-FROM-entry reference.

With some just-committed patches, the error message is now along the
lines of "Subselect in FROM may not refer to other relations of same
query level" which may be more illuminating than "*OLD* does not exist".

> CREATE RULE number_insert AS
> ON INSERT TO numbers DO
> SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub
> EXCEPT SELECT 1;

But it's annoying that this case doesn't work. In an INSERT rule,
NEW.number isn't really a relation reference but a sort of macro formal
parameter, which will be replaced by the value inserted into the number
column. So, at least in the case where we're doing INSERT...VALUES,
the expanded query would be well-defined. I'm not convinced it would
work for INSERT...SELECT though :-(

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Brandon Craig Rhodes <brandon(at)oit(dot)gatech(dot)edu> writes:

> > We had been avoiding sub-selects within rules because they could not
> > reference the NEW and OLD pseudo-relations ...

> I hate to disappoint you, but they were doing the right
> thing. ... you were effectively doing ... an illegal
> cross-FROM-entry reference.
...
> > CREATE RULE number_insert AS
> > ON INSERT TO numbers DO
> > SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub
> > EXCEPT SELECT 1;
>
> But it's annoying that this case doesn't work. In an INSERT rule,
> NEW.number isn't really a relation reference but a sort of macro
> formal parameter, which will be replaced by the value inserted into
> the number column.

My original posting must not have been clear: the case you quote
above, which you are annoyed `doesn't work', is in fact one of the two
rules in my example which *does* work. To quote from my original
email, I should reiterate that

>CREATE RULE number_insert AS
>ON INSERT TO numbers DO
> SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub
> EXCEPT SELECT 1;

*does* work, while

>CREATE RULE number_insert_fails AS
>ON INSERT TO numbers DO
> SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub;

does *not* work.

My confusion is that in both cases NEW.number is used in a scalar
context and thus, according to your `macro' argument, *both* of these
cases should work, not just in the first case. I fail to see how the
slight difference between these two cases makes the first rule's
reference to NEW legitimate and the second one's not.

In other words these two rules are similar enough that it seems to me
they should stand or fall together: either your argument about the
cross-FROM expression should eliminate both of them as possibilities,
or your argument about NEW.number being a macro should allow both.

Thanks for looking at this,
--
Brandon Craig Rhodes http://www.rhodesmill.org/brandon
Georgia Tech brandon(at)oit(dot)gatech(dot)edu

Brandon Craig Rhodes <brandon(at)oit(dot)gatech(dot)edu> writes:
> My original posting must not have been clear: the case you quote
> above, which you are annoyed `doesn't work', is in fact one of the two
> rules in my example which *does* work.

Not any more ;-). As of CVS tip:

regression=# create table numbers (number int);
CREATE TABLE
regression=# CREATE RULE number_insert_fails AS
regression-# ON INSERT TO numbers DO
regression-# SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub;
ERROR: Subselect in FROM may not refer to other relations of same query level
regression=# CREATE RULE number_insert AS
regression-# ON INSERT TO numbers DO
regression-# SELECT * FROM (SELECT * FROM numbers WHERE number = NEW.number) AS sub
regression-# EXCEPT SELECT 1;
ERROR: UNION/INTERSECT/EXCEPT member statement may not refer to other relations of same query level

In my opinion this restriction is essential for the OLD case. It might
not be essential for NEW in an ON INSERT rule, but I'm not sure.

regards, tom lane