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

Wed, 31 Jul 2002 16:13:28 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Rules and Views 
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> Since I see a huge benefit in allowing conditional rules for a view,
> I think it is worth finding a solution.

We do allow conditional rules for a view.  You just have to write an
unconditional one too (which can be merely DO INSTEAD NOTHING).

			regards, tom lane

Wed, 31 Jul 2002 10:37:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rules and Views 
On Wed, 31 Jul 2002, Zeugswetter Andreas SB SD wrote:

> 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 want to be careful with this sort of stuff, since the query planner
sometimes won't do the view as efficiently as it would do the fully
specified equivalant query. I've posted about this here before.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Thu, 1 Aug 2002 10:27:51 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Rules and Views 
Curt Sampson <cjs@cynic.net> writes:
> You want to be careful with this sort of stuff, since the query planner
> sometimes won't do the view as efficiently as it would do the fully
> specified equivalant query. I've posted about this here before.

Please provide an example.  AFAIK a view is a query macro, and nothing
else.

			regards, tom lane

Thu, 01 Aug 2002 00:00:39 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rules and Views 
On Thu, 1 Aug 2002, Tom Lane wrote:

> Curt Sampson <cjs@cynic.net> writes:
> > You want to be careful with this sort of stuff, since the query planner
> > sometimes won't do the view as efficiently as it would do the fully
> > specified equivalant query. I've posted about this here before.
>
> Please provide an example.  AFAIK a view is a query macro, and nothing
> else.

I already did provide an example, and you even replied to it. :-)
See the appended message.

BTW, this page

    http://archives.postgresql.org/pgsql-general/2002-06/threads.php

does not display in Navigator 4.78. Otherwise I would have provided a
reference to the thread in the archive.

Maybe we need a web based form for reporting problem pages in the archives.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Thu, 1 Aug 2002 13:17:07 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Rules and Views 
Curt Sampson <cjs@cynic.net> writes:
> On Thu, 1 Aug 2002, Tom Lane wrote:
>> Curt Sampson <cjs@cynic.net> writes:
> You want to be careful with this sort of stuff, since the query planner
> sometimes won't do the view as efficiently as it would do the fully
> specified equivalant query. I've posted about this here before.
>> 
>> Please provide an example.  AFAIK a view is a query macro, and nothing
>> else.

> I already did provide an example, and you even replied to it. :-)

But that isn't an "equivalent query".  You've manually transformed
    SELECT * FROM (SELECT something UNION SELECT somethingelse) WHERE foo;
into
    (SELECT something WHERE foo) UNION (SELECT somethingelse WHERE foo);
As has been pointed out repeatedly, it's not entirely obvious whether
this is a valid transformation in the general case.  (The knee-jerk
reaction that it's obviously right should be held in check, since SQL's
three-valued notion of boolean logic tends to trip up the intuition.)
If you can provide a proof that it's always safe, or that it's safe
under such-and-such conditions, I'll see what I can do about making it
happen.

			regards, tom lane

Thu, 01 Aug 2002 00:44:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rules and Views 
On Thu, 1 Aug 2002, Tom Lane wrote:

> But that isn't an "equivalent query".  You've manually transformed
>     SELECT * FROM (SELECT something UNION SELECT somethingelse) WHERE foo;
> into
>     (SELECT something WHERE foo) UNION (SELECT somethingelse WHERE foo);

Right.

> As has been pointed out repeatedly, it's not entirely obvious whether
> this is a valid transformation in the general case.

Right. And I agreed that it as soon as you first pointed it out.
And still do.

But the message I was replying to was a similar union query, and I was
thinking that that person might be having a similar initial intuitive
reaction, "well, it looks kinda the same." I just wanted to note that
you need to check this stuff with explain, rather than blindly assuming
you know what's going on.

> If you can provide a proof that it's always safe, or that it's safe
> under such-and-such conditions, I'll see what I can do about making it
> happen.

It's on my list of things to do, but not high enough that it's
likely I'll ever get to it. :-)

BTW, if anybody can think of a way to make a view that really does
represent my original query, I'd appreciate a hint.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Thu, 1 Aug 2002 13:56:55 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Rules and Views 