child table doesn't inherit PRIMARY KEY?

On Sat, Jun 03, 2000 at 05:22:56PM +0200, Louis-David Mitterrand wrote:

When creating a child (through CREATE TABLE ... INHERIT (parent)) it
seems the child gets all of the parent's contraints _except_ its PRIMARY
KEY. Is this normal? Should I add a PRIMARY KEY(id) statement each time
I create an inherited table?

Following up to my previous message, I found that one can't explicitely
add a PRIMARY KEY on child table referencing a field on the parent
table, for instance:

CREATE TABLE auction (
id SERIAL PRIMARY KEY,
title text,
... etc...
);

then

CREATE TABLE auction_dvd (
zone int4,
PRIMARY KEY("id")
) inherits("auction");

doesn't work:
ERROR: CREATE TABLE: column 'id' named in key does not exist

But the aution_dvd table doesn't inherit the auction table's PRIMARY
KEY, so I can insert duplicates.

Solutions:

1) don't use PRIMARY KEY, use UNIQUE NOT NULL (which will be inherited?)
but the I lose the index,

2) use the OID field, but it's deprecated by PG developers?

What would be the best solution?

TIA

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr

Veni, Vidi, VISA.

Louis-David Mitterrand writes:

When creating a child (through CREATE TABLE ... INHERIT (parent)) it
seems the child gets all of the parent's contraints _except_ its PRIMARY
KEY. Is this normal?

It's kind of a bug.

--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

On Sun, Jun 04, 2000 at 03:46:53AM +0200, Peter Eisentraut wrote:

Louis-David Mitterrand writes:

When creating a child (through CREATE TABLE ... INHERIT (parent)) it
seems the child gets all of the parent's contraints _except_ its PRIMARY
KEY. Is this normal?

It's kind of a bug.

Is it a well-known bug or have I discovered it? ;-)

(I am sending a copy of the bug report to -hackers)

Thanks,

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr

"God is a mathematician of very high order, and he used very
advanced mathematics in constructing the universe." (Dirac)

Can someone comment on this?

[ Charset ISO-8859-1 unsupported, converting... ]

Louis-David Mitterrand writes:

When creating a child (through CREATE TABLE ... INHERIT (parent)) it
seems the child gets all of the parent's contraints _except_ its PRIMARY
KEY. Is this normal?

It's kind of a bug.

--
Peter Eisentraut Sernanders v?g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Peter, I thought I saw a recent CVS commit from you stating that you now
can generate the HISTORY file from the SGML source. Have you tested it?
Is it being done automatically?

Also, I would like to get the release dates into the HISTORY file. Do
you know an easy way to do that?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian writes:

Peter, I thought I saw a recent CVS commit from you stating that you now
can generate the HISTORY file from the SGML source. Have you tested it?

Yes.

Is it being done automatically?

No.

It's basically a semi-simplified way of creating an HTML page with the
release notes and saving it with Netscape. It was more or less a
by-product of automating the INSTALL build. It's still nothing to get
excited about as you would still have to manually polish the resulting
text file in some ways.

If you want to try it out run gmake HISTORY in doc/src/sgml. I've also
tried lynx and w3m for HTML to text conversions but their results where
inferior in several ways.

Also, I would like to get the release dates into the HISTORY file. Do
you know an easy way to do that?

I can't think of anything more particular than just writing it in there
somewhere.

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

Bruce Momjian writes:

Peter, I thought I saw a recent CVS commit from you stating that you now
can generate the HISTORY file from the SGML source. Have you tested it?

Yes.

Is it being done automatically?

No.

It's basically a semi-simplified way of creating an HTML page with the
release notes and saving it with Netscape. It was more or less a
by-product of automating the INSTALL build. It's still nothing to get
excited about as you would still have to manually polish the resulting
text file in some ways.

If you want to try it out run gmake HISTORY in doc/src/sgml. I've also
tried lynx and w3m for HTML to text conversions but their results where
inferior in several ways.

OK, same old way I did it long ago.

Also, I would like to get the release dates into the HISTORY file. Do
you know an easy way to do that?

I can't think of anything more particular than just writing it in there
somewhere.

I have it in the SGML. I just need it transfered into the HISTORY file.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

I can't think of anything more particular than just writing it in there
somewhere.

I have it in the SGML. I just need it transfered into the HISTORY file.

You might recall Bruce that I've done this with Applixware for the last
several releases...

- Thomas

I can't think of anything more particular than just writing it in there
somewhere.

I have it in the SGML. I just need it transfered into the HISTORY file.

You might recall Bruce that I've done this with Applixware for the last
several releases...

So you regenerate the HISTORY file using Applixware. I thought it
looked poor, and I had to re-do it, or am I remembering something else.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

So you regenerate the HISTORY file using Applixware. I thought it
looked poor, and I had to re-do it, or am I remembering something else.

*shrug*

I just looked at the CVS logs and I think you fixed Applix to look
better. Please update HISTORY as part of your documentation changes.
Thanks. I added release dates to release.sgml, so it should have the
dates when you are done.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

So you regenerate the HISTORY file using Applixware. I thought it
looked poor, and I had to re-do it, or am I remembering something else.

*shrug*

- Thomas

Does this bug still exist?

[ Charset ISO-8859-1 unsupported, converting... ]

Louis-David Mitterrand writes:

When creating a child (through CREATE TABLE ... INHERIT (parent)) it
seems the child gets all of the parent's contraints _except_ its PRIMARY
KEY. Is this normal?

It's kind of a bug.

--
Peter Eisentraut Sernanders v?g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Probably, since I see it in near recent sources (and it affects
UNIQUE as well. As I remember it, the last discussion on this couldn't
determine what the correct behavior for unique/primary key constraints
was in the inheritance case (is it a single unique hierarchy through
all the tables [would be needed for fk to inheritance trees] or
separate unique constraints for each table [which would be similar
to how many people seem to currently use postgres inheritance as a
shortcut]).

On Thu, 18 Jan 2001, Bruce Momjian wrote:

OK, what do people want to do with this item? Add to TODO list?

Seems making a separat unique constraint would be easy to do and be of
value to most users.

Probably, since I see it in near recent sources (and it affects
UNIQUE as well. As I remember it, the last discussion on this couldn't
determine what the correct behavior for unique/primary key constraints
was in the inheritance case (is it a single unique hierarchy through
all the tables [would be needed for fk to inheritance trees] or
separate unique constraints for each table [which would be similar
to how many people seem to currently use postgres inheritance as a
shortcut]).

On Thu, 18 Jan 2001, Bruce Momjian wrote:

Does this bug still exist?

[ Charset ISO-8859-1 unsupported, converting... ]

Louis-David Mitterrand writes:

When creating a child (through CREATE TABLE ... INHERIT (parent)) it
seems the child gets all of the parent's contraints _except_ its PRIMARY
KEY. Is this normal?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Wed, 24 Jan 2001, Bruce Momjian wrote:

OK, what do people want to do with this item? Add to TODO list?

Seems making a separat unique constraint would be easy to do and be of
value to most users.

The problem is that doing that will pretty much guarantee that we won't
be doing foreign keys to inheritance trees without changing that behavior
and we've seen people asking about adding that too. I think that this
falls into the general category of "Make inheritance make sense" (Now
there's a todo item :) ) Seriously, I think the work on how inheritance
is going to work will decide this, maybe we end up with a real inheritance
tree system and something that works like the current stuff in which case
I'd say it's probably one unique for the former and one per for the
latter.

On Wed, 24 Jan 2001, Bruce Momjian wrote:

OK, what do people want to do with this item? Add to TODO list?

Seems making a separat unique constraint would be easy to do and be of
value to most users.

The problem is that doing that will pretty much guarantee that we won't
be doing foreign keys to inheritance trees without changing that behavior
and we've seen people asking about adding that too. I think that this
falls into the general category of "Make inheritance make sense" (Now
there's a todo item :) ) Seriously, I think the work on how inheritance
is going to work will decide this, maybe we end up with a real inheritance
tree system and something that works like the current stuff in which case
I'd say it's probably one unique for the former and one per for the
latter.

I smell TODO item. In fact, I now see a TODO item:

* Unique index on base column not honored on inserts from inherited table
INSERT INTO inherit_table (unique_index_col) VALUES (dup) should fail
[inherit]

So it seems the fact the UNIQUE doesn't apply to the new table is just a
manifestion of the fact that people expect UNIQUE to span the entire
inheritance tree. I will add the emails to [inherit] and mark it as
resolved.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

On Wed, 24 Jan 2001, Bruce Momjian wrote:

I smell TODO item. In fact, I now see a TODO item:

* Unique index on base column not honored on inserts from inherited table
INSERT INTO inherit_table (unique_index_col) VALUES (dup) should fail
[inherit]

So it seems the fact the UNIQUE doesn't apply to the new table is just a
manifestion of the fact that people expect UNIQUE to span the entire
inheritance tree. I will add the emails to [inherit] and mark it as
resolved.

Bruce, could you add this text to TODO.detail on the subject of
inherited constraints. I first sent it on Christmas Eve, and I
think most people were too busy holidaying to comment.

=================================================================
Tom Lane wrote:

Hm. The short-term answer seems to be to modify the queries generated
by the RI triggers to say "ONLY foo". I am not sure whether we
understand the semantics involved in allowing a REFERENCES target to be
taken as an inheritance tree rather than just one table, but certainly
the current implementation won't handle that correctly.

May I propose these semantics as a basis for future development:

1. An inheritance hierarchy (starting at any point in a tree) should be
equivalent to an updatable view of all the tables at the point of
reference and below. By default, all descendant tables are combined
with the ancestor for all purposes. The keyword ONLY must be used to
alter this behaviour. Only inherited columns of descendant tables are
visible from higher in the tree. Columns may not be dropped in descendants.
If columns are added to ancestors, they must be inserted correctly in
descendants so as to preserve column ordering and inheritance. If
a column is dropped in an ancestor, it is dropped in all descendants.

2. Insertion into a hierarchy means insertion into the table named in
the INSERT statement; updating or deletion affects whichever table(s)
the affected rows are found in. Updating cannot move a row from one
table to another.

3. Inheritance of a table implies inheriting all its constraints unless
ONLY is used or the constraints are subsequently dropped; again, dropping
operates through all descendant tables. A primary key, foreign key or
unique constraint cannot be dropped or modified for a descendant. A
unique index on a column is shared by all tables below the table for
which it is declared. It cannot be dropped for any descendant.

In other words, only NOT NULL and CHECK constraints can be dropped in
descendants.

In multiple inheritance, a column may inherit multiple unique indices
from its several ancestors. All inherited constraints must be satisfied
together (though check constraints may be dropped).

4. RI to a table implies the inclusion of all its descendants in the
check. Since a referenced column may be uniquely indexed further up
the hierarchy than in the table named, the check must ensure that
the referenced value occurs in the right segment of the hierarchy. RI
to one particular level of the hierarchy, excluding descendants, requires
the use of ONLY in the constraint.

5. Dropping a table implies dropping all its descendants.

6. Changes of permissions on a table propagate to all its descendants.
Permissions on descendants may be looser than those on ancestors; they
may not be more restrictive.

This scheme is a lot more restrictive than C++'s or Eiffel's definition
of inheritance, but it seems to me to make the concept truly useful,
without introducing excessive complexity.

============================================================

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"If anyone has material possessions and sees his
brother in need but has no pity on him, how can the
love of God be in him?"
I John 3:17

Thanks. Done.

Bruce Momjian wrote:

On Wed, 24 Jan 2001, Bruce Momjian wrote:

I smell TODO item. In fact, I now see a TODO item:

* Unique index on base column not honored on inserts from inherited table
INSERT INTO inherit_table (unique_index_col) VALUES (dup) should fail
[inherit]

So it seems the fact the UNIQUE doesn't apply to the new table is just a
manifestion of the fact that people expect UNIQUE to span the entire
inheritance tree. I will add the emails to [inherit] and mark it as
resolved.

Bruce, could you add this text to TODO.detail on the subject of
inherited constraints. I first sent it on Christmas Eve, and I
think most people were too busy holidaying to comment.

=================================================================
Tom Lane wrote:

Hm. The short-term answer seems to be to modify the queries generated
by the RI triggers to say "ONLY foo". I am not sure whether we
understand the semantics involved in allowing a REFERENCES target to be
taken as an inheritance tree rather than just one table, but certainly
the current implementation won't handle that correctly.

May I propose these semantics as a basis for future development:

1. An inheritance hierarchy (starting at any point in a tree) should be
equivalent to an updatable view of all the tables at the point of
reference and below. By default, all descendant tables are combined
with the ancestor for all purposes. The keyword ONLY must be used to
alter this behaviour. Only inherited columns of descendant tables are
visible from higher in the tree. Columns may not be dropped in descendants.
If columns are added to ancestors, they must be inserted correctly in
descendants so as to preserve column ordering and inheritance. If
a column is dropped in an ancestor, it is dropped in all descendants.

2. Insertion into a hierarchy means insertion into the table named in
the INSERT statement; updating or deletion affects whichever table(s)
the affected rows are found in. Updating cannot move a row from one
table to another.

3. Inheritance of a table implies inheriting all its constraints unless
ONLY is used or the constraints are subsequently dropped; again, dropping
operates through all descendant tables. A primary key, foreign key or
unique constraint cannot be dropped or modified for a descendant. A
unique index on a column is shared by all tables below the table for
which it is declared. It cannot be dropped for any descendant.

In other words, only NOT NULL and CHECK constraints can be dropped in
descendants.

In multiple inheritance, a column may inherit multiple unique indices
from its several ancestors. All inherited constraints must be satisfied
together (though check constraints may be dropped).

4. RI to a table implies the inclusion of all its descendants in the
check. Since a referenced column may be uniquely indexed further up
the hierarchy than in the table named, the check must ensure that
the referenced value occurs in the right segment of the hierarchy. RI
to one particular level of the hierarchy, excluding descendants, requires
the use of ONLY in the constraint.

5. Dropping a table implies dropping all its descendants.

6. Changes of permissions on a table propagate to all its descendants.
Permissions on descendants may be looser than those on ancestors; they
may not be more restrictive.

This scheme is a lot more restrictive than C++'s or Eiffel's definition
of inheritance, but it seems to me to make the concept truly useful,
without introducing excessive complexity.

============================================================

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"If anyone has material possessions and sees his
brother in need but has no pity on him, how can the
love of God be in him?"
I John 3:17

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026