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;
}