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