Re: Rules and Views

> > Seems more accurate, but actually you may also have two or more
> > conditional rules that cover all possibilities if taken together.
> > Maybe
> > ERROR: Cannot insert into a view
> > You need an ON INSERT DO INSTEAD rule that matches your INSERT
> > Which covers both cases.
>
> Actually not: the system insists that you provide an unconditional
> DO INSTEAD rule. The other would require trying to prove (during
> rule expansion) a theorem that the conditions of the available
> conditional rules cover all possible cases.
>
> Alternatively we could move the test for insertion-into-a-view out of
> the rewriter and into a low level of the executor, producing an error
> message only if some inserted tuple actually gets past the rule
> conditions. I don't much care for that answer because (a) it turns a
> once-per-query overhead check into once-per-tuple overhead, and

Since I see a huge benefit in allowing conditional rules for a view,
I think it is worth finding a solution.

The current rewriter test could still catch the case where no instead rule
exists at all.

The utility is "Table Partitioning by expression".

Basically you have a union view like:
create view history as
select * from history2000 where yearcol=2000
union all
select * from history2001 where yearcol=2001

You get the idea.
Now you need conditional insert and update rules to act on the
correct table.

Maybe we would also need additional intelligence in the planner
to eliminate the history2000 table in a select * from history where
yearcol=2001.

But that is all you need for a really useful feature for large databases.

> (b) if you fail to span the full space of possibilities in your rule
> conditions, you might not find out about it until your application goes
> belly-up in production. There's some version of Murphy's Law that says
> rare conditions arise with very low probability during testing, and very
> high probability as soon as you go live...

This is true for other db's table partitioning capabilities as well, and they
still implement the feature.

Andreas