Thread: Table Inherit Problem

Hello! Could you check the following testing results and questions?

Thank you!

CN
========
CREATE TABLE tt1 (
f1      INTEGER PRIMARY KEY
)WITHOUT OIDS;

CREATE TABLE tt2 (
PRIMARY KEY (f1,f2),
f2      INTEGER,
f3      VARCHAR(80)
)
INHERITS (tt1)
WITHOUT OIDS;

db1=# insert into tt2 values(1,1,'a');
INSERT 0 1
db1=# insert into tt2 values(1,2,'a');
INSERT 0 1
db1=# insert into tt2 values(2,1,'b');
INSERT 0 1
db1=# select * from tt1;
 f1
----
  1
  1
  2
(3 rows)

db1=# delete from tt2 where f1=1 and f2=2;
DELETE 1
db1=# select * from tt1;
 f1
----
  1
  2
(2 rows)

db1=# insert into tt2 values(1,2,'a');
INSERT 0 1
db1=# select * from tt1;
 f1
----
  1
  2
  1
(3 rows)

db1=# alter table tt1 add remark text;
ALTER TABLE
db1=# select * from tt1;
 f1 | remark
----+--------
  1 |
  2 |
  1 |
(3 rows)

db1=# update tt1 set remark ='xx';
ERROR:  Cannot insert a duplicate key into unique index tt2_pkey

!!! QUESTION HERE: Why update fails? !!!

db1=# select * from tt2;
 f1 | f2 | f3 | remark
----+----+----+--------
  1 |  1 | a  |
  2 |  1 | b  |
  1 |  2 | a  |
(3 rows)

db1=# update tt2 set remark='xx' where f1=1 and f2=1;
UPDATE 1
db1=# select * from tt1;
 f1 | remark
----+--------
  2 |
  1 |
  1 | xx
(3 rows)

db1=# select * from tt2;
 f1 | f2 | f3 | remark
----+----+----+--------
  2 |  1 | b  |
  1 |  2 | a  |
  1 |  1 | a  | xx
(3 rows)

db1=# delete from tt1 where f1=1;
DELETE 2
db1=# select * from tt2;

 f1 | f2 | f3 | remark
----+----+----+--------
  2 |  1 | b  |
(1 row)

db1=# update tt1 set remark='xx';
UPDATE 1
db1=# select * from tt2;
 f1 | f2 | f3 | remark
----+----+----+--------
  2 |  6 | ?  |
(1 row)

!!! Note for the last SELECT: !!!
(1) Column "f2" is "6", which shouldn't be.
(2) "?" in f3 indicates a non-ascii character.

db1=# select * from tt1;
 f1 | remark
----+--------
  2 |
(1 row)

db1=# update tt2 set remark='yy';
UPDATE 1
db1=# select * from tt1;
 f1 | remark
----+--------
  2 | yy
(1 row)

db1=# select * from tt2;
 f1 | f2 | f3 | remark
----+----+----+--------
  2 |  6 | ?  | yy
(1 row)

!!! Note for the last SELECT: !!!
"?" in f3 indicates a non-ascii character.

!!! QUESTION HERE: Why the last 5 results? !!!

My wish:

When the follwing inserts are done to brand new tt1 and tt2,

INSERT INTO tt2 VALUES (1,1,'a');
INSERT INTO tt2 VALUES (1,2,'b');
INSERT INTO tt2 VALUES (2,1,'c');

the following select would return only 2 rows "1" and "2", instead of 3
rows "1", "1", "2":

SELECT * FROM tt1;

--
http://fastmail.fm - Sent 0.000002 seconds ago

"CN" <cnliou9@fastmail.fm> writes:
> db1=# update tt1 set remark ='xx';
> ERROR:  Cannot insert a duplicate key into unique index tt2_pkey

This example works okay in 7.2.3.  I think I broke it in 7.3 while
fixing another problem :-(.  Will work on it.

            regards, tom lane

"CN" <cnliou9@fastmail.fm> writes:
> db1=# update tt1 set remark ='xx';
> ERROR:  Cannot insert a duplicate key into unique index tt2_pkey

The attached patch against 7.3 fixes it.

            regards, tom lane

*** src/backend/optimizer/prep/prepunion.c.orig    Wed Sep 18 17:35:21 2002
--- src/backend/optimizer/prep/prepunion.c    Sat Jan  4 19:56:56 2003
***************
*** 67,72 ****
--- 67,73 ----
                        List *refnames_tlist);
  static Node *adjust_inherited_attrs_mutator(Node *node,
                                 adjust_inherited_attrs_context *context);
+ static List *adjust_inherited_tlist(List *tlist, Oid new_relid);


  /*
***************
*** 770,779 ****
          Query       *newnode;

          FLATCOPY(newnode, query, Query);
-         if (newnode->resultRelation == old_rt_index)
-             newnode->resultRelation = new_rt_index;
          query_tree_mutator(newnode, adjust_inherited_attrs_mutator,
                             (void *) &context, QTW_IGNORE_SUBQUERIES);
          return (Node *) newnode;
      }
      else
--- 771,787 ----
          Query       *newnode;

          FLATCOPY(newnode, query, Query);
          query_tree_mutator(newnode, adjust_inherited_attrs_mutator,
                             (void *) &context, QTW_IGNORE_SUBQUERIES);
+         if (newnode->resultRelation == old_rt_index)
+         {
+             newnode->resultRelation = new_rt_index;
+             /* Fix tlist resnos too, if it's inherited UPDATE */
+             if (newnode->commandType == CMD_UPDATE)
+                 newnode->targetList =
+                     adjust_inherited_tlist(newnode->targetList,
+                                            new_relid);
+         }
          return (Node *) newnode;
      }
      else
***************
*** 888,891 ****
--- 896,997 ----

      return expression_tree_mutator(node, adjust_inherited_attrs_mutator,
                                     (void *) context);
+ }
+
+ /*
+  * Adjust the targetlist entries of an inherited UPDATE operation
+  *
+  * The expressions have already been fixed, but we have to make sure that
+  * the target resnos match the child table (they may not, in the case of
+  * a column that was added after-the-fact by ALTER TABLE).  In some cases
+  * this can force us to re-order the tlist to preserve resno ordering.
+  * (We do all this work in special cases so that preptlist.c is fast for
+  * the typical case.)
+  *
+  * The given tlist has already been through expression_tree_mutator;
+  * therefore the TargetEntry nodes are fresh copies that it's okay to
+  * scribble on.  But the Resdom nodes have not been copied; make new ones
+  * if we need to change them!
+  *
+  * Note that this is not needed for INSERT because INSERT isn't inheritable.
+  */
+ static List *
+ adjust_inherited_tlist(List *tlist, Oid new_relid)
+ {
+     bool        changed_it = false;
+     List       *tl;
+     List       *new_tlist;
+     bool        more;
+     int            attrno;
+
+     /* Scan tlist and update resnos to match attnums of new_relid */
+     foreach(tl, tlist)
+     {
+         TargetEntry *tle = (TargetEntry *) lfirst(tl);
+         Resdom       *resdom = tle->resdom;
+
+         if (resdom->resjunk)
+             continue;            /* ignore junk items */
+
+         attrno = get_attnum(new_relid, resdom->resname);
+         if (attrno == InvalidAttrNumber)
+             elog(ERROR, "Relation \"%s\" has no column \"%s\"",
+                  get_rel_name(new_relid), resdom->resname);
+         if (resdom->resno != attrno)
+         {
+             resdom = (Resdom *) copyObject((Node *) resdom);
+             resdom->resno = attrno;
+             tle->resdom = resdom;
+             changed_it = true;
+         }
+     }
+
+     /*
+      * If we changed anything, re-sort the tlist by resno, and make sure
+      * resjunk entries have resnos above the last real resno.  The sort
+      * algorithm is a bit stupid, but for such a seldom-taken path, small
+      * is probably better than fast.
+      */
+     if (!changed_it)
+         return tlist;
+
+     new_tlist = NIL;
+     more = true;
+     for (attrno = 1; more; attrno++)
+     {
+         more = false;
+         foreach(tl, tlist)
+         {
+             TargetEntry *tle = (TargetEntry *) lfirst(tl);
+             Resdom       *resdom = tle->resdom;
+
+             if (resdom->resjunk)
+                 continue;        /* ignore junk items */
+
+             if (resdom->resno == attrno)
+                 new_tlist = lappend(new_tlist, tle);
+             else if (resdom->resno > attrno)
+                 more = true;
+         }
+     }
+
+     foreach(tl, tlist)
+     {
+         TargetEntry *tle = (TargetEntry *) lfirst(tl);
+         Resdom       *resdom = tle->resdom;
+
+         if (!resdom->resjunk)
+             continue;            /* here, ignore non-junk items */
+
+         if (resdom->resno != attrno)
+         {
+             resdom = (Resdom *) copyObject((Node *) resdom);
+             resdom->resno = attrno;
+             tle->resdom = resdom;
+         }
+         new_tlist = lappend(new_tlist, tle);
+         attrno++;
+     }
+
+     return new_tlist;
  }