
I'm having a weird problem on my " PostgreSQL 7.2.1 on i386--netbsdelf,
compiled by GCC 2.95.3" system. Executing these commands:

CREATE TABLE test_one (id int PRIMARY KEY, value_one text);
CREATE TABLE test_two (id int PRIMARY KEY, value_two text);
CREATE VIEW test AS
    SELECT test_one.id, value_one, value_two
    FROM test_one
    JOIN test_two USING (id);
CREATE RULE test_insert AS
    ON INSERT TO test
    DO (
	INSERT INTO test_one (id, value_one) VALUES (NEW.id, NEW.value_one);
	INSERT INTO test_two (id, value_two) VALUES (NEW.id, NEW.value_two);
	);
INSERT INTO test VALUES (1, 'one', 'onemore');

returns "ERROR:  Cannot insert into a view without an appropriate rule"
for that last statement. The rule does show up in pg_rules, though.

What am I doing wrong here? Is there a bug?

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


Wed, 31 Jul 2002 11:34:20 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Rules and Views
Curt Sampson <cjs@cynic.net> writes:
> CREATE VIEW test AS ...
> CREATE RULE test_insert AS
>     ON INSERT TO test
>     DO  ...
> INSERT INTO test VALUES (1, 'one', 'onemore');
> ERROR:  Cannot insert into a view without an appropriate rule

> What am I doing wrong here? Is there a bug?

Make that "ON INSERT DO INSTEAD".  As coded, the rule leaves the
original insertion into the view still active.

Perhaps the error message could be phrased better --- any thoughts?

			regards, tom lane

Tue, 30 Jul 2002 23:32:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rules and Views 
On Wed, 2002-07-31 at 10:22, Tom Lane wrote:
> Curt Sampson <cjs@cynic.net> writes:
> > On Wed, 31 Jul 2002, Tom Lane wrote:
> >> Well, to my mind that's what the error message says now.  The reason
> >> it didn't help you was that you *did* have a rule ... but it didn't
> >> completely override the view insertion.
> 
> > Right, like I said, my model was wrong. I didn't think of the error
> > message as being an "insert behaviour" that had to be overridden; I
> > thought of it as a "there is no behaviour right now" message.
> 
> Hm.  How about
> 
> ERROR:  Cannot insert into a view
> 	You need an unconditional ON INSERT DO INSTEAD rule

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.

-----------------
Hannu


31 Jul 2002 09:16:49 +0500
Hannu Krosing <hannu@tm.ee>
Re: Rules and Views
On Tue, 30 Jul 2002, Tom Lane wrote:

> Curt Sampson <cjs@cynic.net> writes:
> > CREATE VIEW test AS ...
> > CREATE RULE test_insert AS
> >     ON INSERT TO test
> >     DO  ...
> > INSERT INTO test VALUES (1, 'one', 'onemore');
> > ERROR:  Cannot insert into a view without an appropriate rule
>
> > What am I doing wrong here? Is there a bug?
>
> Make that "ON INSERT DO INSTEAD".  As coded, the rule leaves the
> original insertion into the view still active.

Ah, I see! My model of how this was working was wrong.

> Perhaps the error message could be phrased better --- any thoughts?

Maybe a message that says something along the lines of "cannot insert
into views; you need to override this behaviour with a rule"? Also, some
examples in the manual would be helpful.

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


Wed, 31 Jul 2002 13:40:21 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Rules and Views 
Curt Sampson <cjs@cynic.net> writes:
> ERROR:  Cannot insert into a view without an appropriate rule

>> Perhaps the error message could be phrased better --- any thoughts?

> Maybe a message that says something along the lines of "cannot insert
> into views; you need to override this behaviour with a rule"?

Well, to my mind that's what the error message says now.  The reason
it didn't help you was that you *did* have a rule ... but it didn't
completely override the view insertion.

I'm not sure how to phrase a more useful message.  Note that the place
where the error can be detected doesn't have any good way to know that
a non-INSTEAD rule was in fact processed, so we can't say anything quite
as obvious as "You needed to use INSTEAD in your rule, luser".  Can we
cover both the no-rule-at-all case and the had-a-rule-but-it-wasn't-
INSTEAD case in a single, reasonably phrased error message?  (Just
to make life interesting, there's also the case where you made an
INSTEAD rule but it's conditional.)

> Also, some examples in the manual would be helpful.

Aren't there several already?  But feel free to contribute more...

			regards, tom lane

Wed, 31 Jul 2002 01:03:06 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rules and Views 
On Wed, 31 Jul 2002, Tom Lane wrote:

> Well, to my mind that's what the error message says now.  The reason
> it didn't help you was that you *did* have a rule ... but it didn't
> completely override the view insertion.

Right, like I said, my model was wrong. I didn't think of the error
message as being an "insert behaviour" that had to be overridden; I
thought of it as a "there is no behaviour right now" message.

Maybe it's just me not reading the docs all that well; I wouldn't worry
about this if it's not been a problem for others.

> > Also, some examples in the manual would be helpful.
>
> Aren't there several already?  But feel free to contribute more...

Yeah, but nothing showing these rules on a view across two tables.
I'll try to work it out and send it here for comments.

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


Wed, 31 Jul 2002 14:15:16 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Rules and Views 
Curt Sampson <cjs@cynic.net> writes:
> On Wed, 31 Jul 2002, Tom Lane wrote:
>> Well, to my mind that's what the error message says now.  The reason
>> it didn't help you was that you *did* have a rule ... but it didn't
>> completely override the view insertion.

> Right, like I said, my model was wrong. I didn't think of the error
> message as being an "insert behaviour" that had to be overridden; I
> thought of it as a "there is no behaviour right now" message.

Hm.  How about

ERROR:  Cannot insert into a view
	You need an unconditional ON INSERT DO INSTEAD rule

			regards, tom lane

Wed, 31 Jul 2002 01:22:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rules and Views 
On Wed, 31 Jul 2002, Tom Lane wrote:

> ERROR:  Cannot insert into a view
> 	You need an unconditional ON INSERT DO INSTEAD rule

Sounds great to me!

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


Wed, 31 Jul 2002 14:37:20 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Rules and Views 
Hannu Krosing <hannu@tm.ee> writes:
> On Wed, 2002-07-31 at 10:22, Tom Lane wrote:
>> Hm.  How about
>> 
>> ERROR:  Cannot insert into a view
>> You need an unconditional ON INSERT DO INSTEAD rule

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

			regards, tom lane

Wed, 31 Jul 2002 02:32:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rules and Views 