6.5.3: Two 'INHERITS' problems. - Mailing list pgsql-general

Hi, folks.

I'm having some trouble with inheritance in 6.5.3; first off, PRIMARY KEY
attributes don't seem to be inherited.

For example,

CREATE TABLE test (i integer primary key);
CREATE TABLE test2 (j integer) INHERITS(test);

then

INSERT INTO test VALUES (1);
INSERT INTO test VALUES (1);

correctly returns an error, while

INSERT INTO test VALUES (1);
INSERT INTO test2 VALUES (1,1);

does not.  Is this proper behavior??  If so, how can I guarantee that
'i' will be a primary (unique) key for the entire inheritance hierarchy?

----------------------------------------------------------------------------

Secondly,

create table superparent (
       super_member_1     INTEGER
);

create table subchild (
       child_member_2 INTEGER
) INHERITS (superparent);

insert into subchild VALUES (1,1);

select * from subchild;
-- should return (1, 1)

select * from superparent;
-- should return null

select * from superparent*;
-- should return (1)

---- test inheritance:

-- add a member specifically to superparent:
alter table superparent add member_2 INTEGER;

-- add a member to superparent + children
alter table superparent* add super_member_3 INTEGER;

-- SELECT * : get good behavior from subchild, good behavior from
-- superparent, and bad (random??) behavior from superparent*:

select * from subchild;
select * from superparent;
select * from superparent*;

-- lesson: either (a) don't add members specifically to superparent,
               or (b) have some other check on the query results!

------------------------------------------------------------------------

Selecting everything from superparent* returns a nonsense value for
'member_2', which was added only to superparent and not to the
full hierarchy.  Again, is this proper behavior??  How can one
guard against it?

Upgrading to PG 7 is not yet an option - I'm using pgACS, which doesn't
yet working with 7 fully AFAIK.  If upgrading is the only solution, I'll put
up with it 'til I can use 7 ;).

Thanks,
--titus

P.S. Pointers to appropriate documentation would be appreciated; I've read
everything I can find...
--
Titus Brown, titus@caltech.edu