Re: Return of INSTEAD rules

> > > I am confused how yours differs from mine. I don't see how the last
> > > matching tagged query would not be from an INSTEAD rule.
> >
> > You could have both INSTEAD and non-INSTEAD rules firing for the same
> > original query. If the alphabetically-last rule is a non-INSTEAD rule,
> > then there's a difference.
>
> How do we get multiple rules on a query? I thought it was mostly
> INSERT/UPDATE/DELETE, and those all operate on a single table.

I have yet another use case, that might be of great interest to VLDB users,
Partitioned tables:

create table atab2000 (b text unique);
create table atab2001 (b text unique);
create table atab2002 (b text unique);

create view atab (a, b) as
select 2000, b from atab2000 union all
select 2001, b from atab2001 union all
select 2002, b from atab2002;

create rule atab_ins2000 as on insert to atab
where a=2000 do instead insert into atab2000 values (new.b);
create rule atab_ins2001 as on insert to atab
where a=2001 do instead insert into atab2001 values (new.b);
create rule atab_ins2002 as on insert to atab
where a=2002 do instead insert into atab2002 values (new.b);

create rule atab_insZZZZ as on insert to atab
do instead nothing;

create rule atab_upd2000_u as on update to atab
where old.a=2000 and new.a=2000 do instead
update atab2000 set b= new.b where b = old.b;
create rule atab_upd2000_m as on update to atab
where old.a=2000 and new.a <> 2000 do instead (
insert into atab values (new.a, new.b);
delete from atab2000 where b = old.b);

... if same for 2001 and 2002 I get
postgres=# explain update atab set a=2002 where a=2000 and b='2000 row 1';
ERROR: query rewritten 10 times, may contain cycles
Cannot understand why though :-(

create rule atab_updZZZZ as on update to atab
do instead nothing;

(I have not been able to successfully do the delete in a non-INSTEAD
rule and the insert in a INSTEAD rule, thus don't count DELETE's, see below)

... on delete .. :-)

It is ***really amazing*** how well this actually works in PostgreSQL !!!
The only thing to fix is the row count, and above "query rewritten" problem :-)

A small improvement for the planner/optimizer would probably be to do the
One-Time Filter: false evaluation earlier (before planning a Subquery
that is not needed).

I was one of those suggesting all, but viewing above I think DELETE is only
supposed to be counted in an original DELETE statement, Thus:

DELETE: DELETE's + UPDATE's (update the row to invisible)
INSERT: INSERT's + UPDATE's (allow an insert or update rule)
UPDATE: INSERT's + UPDATE's (allow partitioned tables)

I do not think there is a way around "messy", thus I think we have to
load off the responsibility to the rule creator. The creator can choose what counts by
creating INSTEAD rules for actions that are supposed to count, and non-INSTEAD
rules for those that should not.

> I think you just modified the second part of that to restrict it to
> queries that were added by INSTEAD rules. This is doable but it's
> not a trivial change --- in particular, I think it implies adding
> another field to Query data structure so we can mark INSTEAD-added
> vs non-INSTEAD-added queries. Which means an initdb because it breaks
> stored rules.

Yes Tom, I think we need to differentiate rule actions from INSTEAD vs non-INSTEAD
actions inside one statement, non-INSTEAD should never count INSTEAD should always count.

Andreas