[BUGS] BUG #1002: Update using rule on view with inheritance alters 0 rows, 1 row expected.

[PostgreSQL Bugs List]

The following bug has been logged online:

Bug reference:      1002
Logged by:          Chris Piker
Email address:      [EMAIL PROTECTED]
PostgreSQL version: 7.4
Operating system:   RedHat Linux 9
Description:        Update using rule on view with inheritance alters 0 rows, 1 row 
expected.
Details: 

Specific Version 
----------------
Using postgres binary distribution that includes the
file: postgresql-7.4-0.3PGDG.i386.rpm


Schema (with data)
------------------

-- Groups and Permissions tables

create table groups(
   gid int4 primary key,
   short_name varchar(32)
);

create table perms(
   gid integer not null references groups(gid)
      on update cascade on delete cascade,
   usename name not null,
   primary key (gid, usename),

   sel boolean not null,
   ins boolean not null,
   up boolean not null,
   del boolean not null
);
        

-- Data Object Tables...

create table base(
   id integer primary key,
   gid integer references groups(gid)
      on update cascade on delete set null,
   obj_name varchar(128) not null
);

create table child_1(
   primary key(id),
   foreign key(gid) references groups(gid)
      on update cascade on delete set null,
   child1_stuff text
) inherits (base);

create table child_2(
   primary key(id),
   foreign key(gid) references groups(gid)
      on update cascade on delete set null,
   child2_stuff text
) inherits (child_1);

-- Data for permission tables:

insert into groups 
   values (1,'users');
insert into perms 
   values (1,'postgres',true,true,true,true);

-- Data for object tables:
insert into base 
   values (1,1,'Base Object');
insert into child_1 
   values (2,1,'Child 1 Object','Stuff');
insert into child_2 
   values (3,1,'Child 2 Object','Stuff','Stuff');


-- Update view on table "base"

create view base_up as select base.* from perms,base where
   perms.gid = base.gid and 
   perms.usename = session_user and
   perms.up = true;
        
create rule R_base_up as on update to base_up 
   do instead update base set 
   gid = new.gid, 
   obj_name = new.obj_name
   where old.id = id;

grant select,update on base_up to public;


Problem Query
-------------
The following query updates 0 rows, when 1 row is 
expected to be updated:

update base_up set obj_name = 'new name' where id = 3;

The following related query updates 1 row, when 1 row
is expected:

update base_up set obj_name = 'new name' where id = 2;

Explain clearly shows which parts of the query plan are
in error.  I can send the explain analyze output if 
needed.

Thanks for your time on this issue.
--
[EMAIL PROTECTED]





---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster