Thread: Rules with Conditions: Still Doesn't Work (Bug Rpt)
(sorry for cross-posting a bug report directly to pgsql-
hackers, but it started here, and I know it's still a
topic of conversation.)
About a week ago, I posted to pgsql-bugs and pgsql-
hackers a VIEW that shows all user table fields
w/comment, if any, as well as 2 RULES that tried to
allow for updates to this view that would either (as
appropriate) INSERT to pg_description, or UPDATE to
pg_description.
Tom Lane pointed out that, given the current handling of
conditional rules, this should be one rule, with two
actions.
For, this either fails to work, or in some circumstances,
or in some circumstances, crashes the backend.
The problems are demonstrated below, with non-system
tables. I show a few possible attempts at a workable
solutions, but all either:
- insert nothing
- complain about "JOIN qualification may not refer
-- to other relations"
- drop connection to backend
Is this an actual bug? Am I missing something still?
Thanks!
------------------------------------------
-- following is a simplified case that uses non-system
-- tables
-- updating existing records works, but not update-
-- this becomes insert
create table p (pid int not null primary key, txt text);
insert into p values (1,'parent1');
insert into p values (2,'parent2');
create table c (pid int, descrip text);
insert into c values (1,'descrip1');
create view v as select p.pid, txt, descrip from p left
outer join c on p.pid=c.pid;
create rule r as
on update to v do instead
( insert into c (pid, descrip) select old.pid,
new.descrip where old.descrip isnull;
update c set descrip=new.descrip where
c.pid=old.pid;
);
update v set descrip='test' where pid=1;
select * from v; -- works fine
update v set descrip='test' where pid=2;
select * from c; -- doesn't work?
-- perhaps the problem is that the update fails on new
-- records, since there was no old pid?
-- try with slightly different rule (update is
-- different):
drop rule r;
create rule r as
on update to v do instead
( insert into c (pid, descrip) select old.pid,
new.descrip where old.descrip isnull;
update c set descrip=new.descrip where
c.pid=new.pid; );
update v set descrip='test' where pid=2;
-- gets error "ERROR: JOIN qualification may not refer
-- to other relations"
-- try again w/no update action at all, just see if the
-- insert action should work:
drop rule r;
create rule r as
on update to v do instead
( insert into c (pid, descrip) select old.pid,
new.descrip );
update v set descrip='test' where pid=2;
-- inserts w/pid=0, so old.pid obviously <> 2 (?)
-- try again w/new.pid instead of old.pid
drop rule r;
create rule r as
on update to v do instead
( insert into c (pid, descrip) select new.pid,
new.descrip );
update v set descrip='test' where pid=2;
-- gets error "ERROR: JOIN qualification may not refer
-- to other relations"
-- try w/o select but w/values
drop rule r;
create rule r as
on update to v do instead
( insert into c (pid, descrip) values( new.pid,
new.descrip ) );
update v set descrip='test' where pid=2;
-- gets error "backend closed the channel unexpectedly."
-- try w/o "new', but with current
drop rule r;
create rule r as
on update to v do instead
( insert into c (pid, descrip) values( current.pid,
current.descrip ) );
update v set descrip='test' where pid=2;
-- gets error "backend closed the channel unexpectedly."
-- try w/different view using UNION rather than OUTER
-- JOIN
drop view v;
create view v as
select p.pid, txt, descrip
from p join c on p.pid=c.pid
union
select p.pid, txt, null
from p
where not exists (select * from c where c.pid=p.pid);
-- recreate rule that didn't work because of "JOIN
-- qualification"
drop rule r;
create rule r as
on update to v do instead
( insert into c (pid, descrip) select new.pid,
new.descrip );
update v set descrip='test' where pid=2;
-- gets error "ERROR: JOIN qualification may not refer
-- to other relations"
-- try w/subselected view rather than UNION or OUTER JOIN
create view v as
select p.pid, txt, (select descrip from c where
c.pid=p.pid) as descrip
from p;
-- recreate rule that didn't work because of "JOIN
-- qualification"
drop rule r;
create rule r as
on update to v do instead
( insert into c (pid, descrip) select new.pid,
new.descrip );
update v set descrip='test' where pid=2;
-- gets error "ERROR: JOIN qualification may not refer
-- to other relations"
--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)
"Joel Burton" <jburton@scw.org> writes:
> The problems are demonstrated below, with non-system tables.
Wow, what a great bunch of test cases!
You've exposed a number of bugs in the current sources. I'm working
on fixes, but have run out of steam for today...
regards, tom lane
"Joel Burton" <jburton@scw.org> writes:
> Tom Lane pointed out that, given the current handling of
> conditional rules, this should be one rule, with two
> actions.
> For, this either fails to work, or in some circumstances,
> or in some circumstances, crashes the backend.
It turns out that all of these failures come from the same place,
which is that I had broken the handling of INSERT ... SELECT actions
inside rules.
I have committed a patch that appears to fix the problem.
I think that Marc has already tagged beta1, however, so the patch
will not be in the beta1 tarball due to appear Real Soon Now.
You can pull current sources from the CVS server, or grab a nightly
snapshot tarball after tonight.
Thanks for the detailed, easy-to-reproduce bug example! These sorts of
reports are by far the nicest from a developer's point of view ;-)
regards, tom lane
> It turns out that all of these failures come from the same place, > which is that I had broken the handling of INSERT ... SELECT actions > inside rules. > > I have committed a patch that appears to fix the problem. > I think that Marc has already tagged beta1, however, so the patch will > not be in the beta1 tarball due to appear Real Soon Now. You can pull > current sources from the CVS server, or grab a nightly snapshot > tarball after tonight. Fast work! (Boy, I sure wish I had a commercial Oracle/M$ contract instead. I'm sure they could have done it faster ;-) ). Thanks! Just to be clear (as much for other as for me): this should fix the syntax as my (more recent) bug report submitted? That is, one non- conditional rule that tries to handle both inserting and updating together, as opposed to 2 different conditional rules that, together, try to handle all cases. > Thanks for the detailed, easy-to-reproduce bug example! These sorts > of reports are by far the nicest from a developer's point of view ;-) My Mom always said I loved finding problems. :-) -- Joel Burton, Director of Information Systems -*- jburton@scw.org Support Center of Washington (www.scw.org)
"Joel Burton" <jburton@scw.org> writes:
> Just to be clear (as much for other as for me): this should fix the
> syntax as my (more recent) bug report submitted? That is, one non-
> conditional rule that tries to handle both inserting and updating
> together, as opposed to 2 different conditional rules that, together,
> try to handle all cases.
Right. The unconditional rule definitely *should* work; you were
just running into bugs in rewriting INSERT...SELECT actions, which
really had nothing to do with whether the rule is conditional or not.
(Had you written INSERT ... VALUES, you'd have seen no problem.)
I'm not convinced yet whether the system should accept conditional
rules to implement views or not.
BTW, the following hack seems to work fine (as of current sources
anyway) if you are really intent on a bunch of conditional rules:
make an unconditional "do instead nothing" plus as many conditional
add-on rules as you want. For example,
create view foo as select * from int4_tbl;
select * from foo;
f1
-------------
0
123456
-123456
2147483647
-2147483647
(5 rows)
insert into foo values (23);
ERROR: Cannot insert into a view without an appropriate rule
-- Just to keep the rewriter from complaining:
create rule foo1 as on insert to foo do instead nothing;
create rule foo2 as on insert to foo where new.f1 > 100
do insert into int4_tbl values(new.f1);
create rule foo3 as on insert to foo where new.f1 < 0
do insert into int4_tbl values(- new.f1);
insert into foo values (123);
INSERT 145131 1
insert into foo values (-1000);
INSERT 145132 1
insert into foo values (10);
INSERT 0 0
select * from foo;
f1
-------------
0
123456
-123456
2147483647
-2147483647
123
1000
(7 rows)
This also gives you well-defined behavior if none of the conditional
rules fire: nothing happens.
regards, tom lane