Rules with Conditions: Still Doesn't Work (Bug Rpt) - Mailing list pgsql-bugs

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