Rules and Views
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
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
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
Import Notes
Reply to msg id not found: 25080.1028092940@sss.pgh.pa.usReference msg id not found: Pine.NEB.4.44.0207311409540.493-100000@angelic.cynic.netReference msg id not found: 25080.1028092940@sss.pgh.pa.us | Resolved by subject fallback
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 ruleWhat 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
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
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
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
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
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
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
Import Notes
Resolved by subject fallback
"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
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).
Hmm, but you cannot then trow an error, but that is prbbly a minor issue.
Good that we can do Table Partitioning :-)
Andreas
Import Notes
Resolved by subject fallback
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
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
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
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
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
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.
I had a "union all" view, which is actually a quite different animal than
a "union" view which needs to eliminate duplicates before further processing.
Andreas
Import Notes
Resolved by subject fallback
On Thu, 1 Aug 2002, Zeugswetter Andreas SB SD wrote:
I had a "union all" view, which is actually a quite different animal than
a "union" view which needs to eliminate duplicates before further processing.
I had the same problem with UNION ALL.
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
On Thu, 2002-08-01 at 12:29, Curt Sampson wrote:
On Thu, 1 Aug 2002, Zeugswetter Andreas SB SD wrote:
I had a "union all" view, which is actually a quite different animal than
a "union" view which needs to eliminate duplicates before further processing.I had the same problem with UNION ALL.
Could someone give an example where it is not safe to push the WHERE
clause down to individual parts of UNION (or UNION ALL) wher these parts
are simple (non-aggregate) queries?
I can see that it has to be made into HAVING in subquery if UNION's
subqueries are aggregate (GROUP BY) queries, but can anyone give an
example where the meaning of the query changes for non-aggregate
subqueries.
---------------
Hannu
