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

The following bug has been logged online:

Bug reference:      1002
Logged by:          Chris Piker
Email address:      christopher-piker@uiowa.edu
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.
--
christopher-piker@uiowa.edu

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> The following query updates 0 rows, when 1 row is
> expected to be updated:

Nice catch.  The attached patch seems to fix it.

            regards, tom lane


*** src/backend/optimizer/util/clauses.c.orig    Sat Nov 29 14:51:51 2003
--- src/backend/optimizer/util/clauses.c    Mon Dec  8 20:41:07 2003
***************
*** 2960,2995 ****
          RangeTblEntry *rte = (RangeTblEntry *) lfirst(rt);
          RangeTblEntry *newrte;

          switch (rte->rtekind)
          {
              case RTE_RELATION:
              case RTE_SPECIAL:
!                 /* nothing to do, don't bother to make a copy */
                  break;
              case RTE_SUBQUERY:
                  if (!(flags & QTW_IGNORE_RT_SUBQUERIES))
                  {
-                     FLATCOPY(newrte, rte, RangeTblEntry);
                      CHECKFLATCOPY(newrte->subquery, rte->subquery, Query);
                      MUTATE(newrte->subquery, newrte->subquery, Query *);
-                     rte = newrte;
                  }
                  break;
              case RTE_JOIN:
                  if (!(flags & QTW_IGNORE_JOINALIASES))
                  {
-                     FLATCOPY(newrte, rte, RangeTblEntry);
                      MUTATE(newrte->joinaliasvars, rte->joinaliasvars, List *);
-                     rte = newrte;
                  }
                  break;
              case RTE_FUNCTION:
-                 FLATCOPY(newrte, rte, RangeTblEntry);
                  MUTATE(newrte->funcexpr, rte->funcexpr, Node *);
-                 rte = newrte;
                  break;
          }
!         FastAppend(&newrt, rte);
      }
      query->rtable = FastListValue(&newrt);
      return query;
--- 2960,2990 ----
          RangeTblEntry *rte = (RangeTblEntry *) lfirst(rt);
          RangeTblEntry *newrte;

+         FLATCOPY(newrte, rte, RangeTblEntry);
          switch (rte->rtekind)
          {
              case RTE_RELATION:
              case RTE_SPECIAL:
!                 /* we don't bother to copy eref, aliases, etc; OK? */
                  break;
              case RTE_SUBQUERY:
                  if (!(flags & QTW_IGNORE_RT_SUBQUERIES))
                  {
                      CHECKFLATCOPY(newrte->subquery, rte->subquery, Query);
                      MUTATE(newrte->subquery, newrte->subquery, Query *);
                  }
                  break;
              case RTE_JOIN:
                  if (!(flags & QTW_IGNORE_JOINALIASES))
                  {
                      MUTATE(newrte->joinaliasvars, rte->joinaliasvars, List *);
                  }
                  break;
              case RTE_FUNCTION:
                  MUTATE(newrte->funcexpr, rte->funcexpr, Node *);
                  break;
          }
!         FastAppend(&newrt, newrte);
      }
      query->rtable = FastListValue(&newrt);
      return query;