Re: Re: Changing the default value of an inherited column

Peter Eisentraut wrote:
>Tom Lane writes:
>
>> It seems that in pre-7.0 Postgres, this works:
>>
>> create table one(id int default 1, descr text);
>> create table two(id int default 2, tag text) inherits (one);
>>
>> with the net effect that table "two" has just one "id" column with
>> default value 2.
>
>Although the liberty to do anything you want seems appealing at first, I
>would think that allowing this is not correct from an OO point of view.

I don't agree; this is equivalent to redefinition of a feature (=method) in
a descendant class, which is perfectly acceptable so long as the feature's
signature (equivalent to column type) remains unchanged.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)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
========================================
"Trust in the Lord with all your heart and lean not on
your own understanding; in all your ways acknowledge
him, and he will direct your paths." Proverbs 3:5,6

Oliver Elphick writes:

> Peter Eisentraut wrote:
> >Tom Lane writes:
> >
> >> It seems that in pre-7.0 Postgres, this works:
> >>
> >> create table one(id int default 1, descr text);
> >> create table two(id int default 2, tag text) inherits (one);
> >>
> >> with the net effect that table "two" has just one "id" column with
> >> default value 2.
> >
> >Although the liberty to do anything you want seems appealing at first, I
> >would think that allowing this is not correct from an OO point of view.
>
> I don't agree; this is equivalent to redefinition of a feature (=method) in
> a descendant class, which is perfectly acceptable so long as the feature's
> signature (equivalent to column type) remains unchanged.

The SQL equivalent of redefining a method would the redefinition of a
method [sic]. But since we don't have anything close to that, feel
free...

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/

"Oliver Elphick" <olly(at)lfix(dot)co(dot)uk> writes:
>>>> Although the liberty to do anything you want seems appealing at first, I
>>>> would think that allowing this is not correct from an OO point of view.

> I don't agree; this is equivalent to redefinition of a feature (=method) in
> a descendant class, which is perfectly acceptable so long as the feature's
> signature (equivalent to column type) remains unchanged.

Well, that does bring up the question of exactly what is signature and
exactly what is implementation. Clearly we cannot allow the column type
to be redefined. But what about typmod? Is it OK to replace char(32)
with char(64)? How about vice versa? How about replacing numeric(9,0)
with numeric(7,2)?

The pre-7.0 code only checked that the type ID is the same, but I wonder
whether it wouldn't be a good idea to demand typmod the same as well.
For the existing types that use typmod I don't think this is absolutely
necessary (ie, I don't think the system might crash if typmods are
inconsistent in inherited tables) ... but I'm not comfortable about it
either.

regards, tom lane

On Thu, Mar 29, 2001 at 02:29:38PM +0100, Oliver Elphick wrote:
> Peter Eisentraut wrote:
> >Tom Lane writes:
> >
> >> It seems that in pre-7.0 Postgres, this works:
> >>
> >> create table one(id int default 1, descr text);
> >> create table two(id int default 2, tag text) inherits (one);
> >>
> >> with the net effect that table "two" has just one "id" column with
> >> default value 2.
> >
> >Although the liberty to do anything you want seems appealing at first, I
> >would think that allowing this is not correct from an OO point of view.
>
> I don't agree; this is equivalent to redefinition of a feature (=method) in
> a descendant class, which is perfectly acceptable so long as the feature's
> signature (equivalent to column type) remains unchanged.

The O-O principle involved here is Liskov Substitution: if the derived
table is used in the context of code that thinks it's looking at the
base table, does anything break?

Changing the default value of a column should not break anything,
because the different default value could as well have been entered
in the column manually.

Nathan Myers
ncm(at)zembu(dot)com

ncm(at)zembu(dot)com (Nathan Myers) writes:
> The O-O principle involved here is Liskov Substitution: if the derived
> table is used in the context of code that thinks it's looking at the
> base table, does anything break?

Good point. That answers my concern about how to handle typmod: an
application *could* be broken by a change in typmod (eg, suppose it's
allocated a buffer just big enough for a char(N) attribute, using the N
of the parent table). Therefore we must disallow changes in typmod in
child tables.

Further study of creatinh.c shows that we have inconsistent behavior at
the moment, as it will allow columns of the same name to be inherited
from multiple parents and (silently) combined --- how is that really
different from combining with an explicit specification?

I propose the following behavior:

1. A table can have only one column of a given name. If the same
column name occurs in multiple parent tables and/or in the explicitly
specified column list, these column specifications are combined to
produce a single column specification. A NOTICE will be emitted to
warn the user that this has happened. The ordinal position of the
resulting column is determined by its first appearance.

2. An error will be reported if columns to be combined do not all have
the same datatype and typmod value.

3. The new column will have a default value if any of the combined
column specifications have one. The last-specified default (the one
in the explicitly given column list, or the rightmost parent table
that gives a default) will be used.

4. All relevant constraints from all the column specifications will
be applied. In particular, if any of the specifications includes NOT
NULL, the resulting column will be NOT NULL. (But the current
implementation does not support inheritance of UNIQUE or PRIMARY KEY
constraints, and I do not have time to add that now.)

This behavior differs from prior versions as follows:

1. We return to the pre-7.0 behavior of allowing an explicit
specification of a column name that is also inherited (7.0 rejects this,
thereby preventing the default from being changed in the child).
However, we will now issue a warning NOTICE, to answer the concern that
prompted this change of behavior.

2. We will now enforce uniformity of typmod as well as type OID when
combining columns.

3. In both 7.0 and prior versions, if a column appeared in multiple
parents but not in the explicit column list, the first parent's default
value (if any) and NOT NULL state would be used, ignoring those of later
parents. Failing to "or" together the NOT NULL flags is clearly wrong,
and I believe it's inconsistent to use an earlier rather than later
parent's default value when we want an explicitly-specified default to
win out over all of them. The explicit column specifications are
treated as coming after the last parent for other purposes, so we should
define the default to use as the last one reading left-to-right.

Comments? I'm going to implement and commit this today unless I hear
loud squawks ...

regards, tom lane

On Fri, Mar 30, 2001 at 12:10:59PM -0500, Tom Lane wrote:
> Comments? I'm going to implement and commit this today unless I hear
> loud squawks ...

I like it in general and I think it opens some interesting
possibilities. I don't know much about how the inheritance system is
implemented, so I will put out this scenario in case it makes a
difference.

We recently decided to refactor our schema a bit, using inheritance.
All of our tables have a primary key called "seq" along with some
other common fields such as entry time, etc. We realized that moving
them into a "base" table allowed us to create functions on "base"
that would work on every derived table. The main problem was that
we needed fields like "seq" to have distinct sequences, which was
not possible without the ability to override the default value in
each derived table. It seems like this would be easily doable with
this change.

Another thing that seems kind of interesting would be to have:

CREATE TABLE base (table_id CHAR(8) NOT NULL [, etc.]);
CREATE TABLE foo (table_id CHAR(8) NOT NULL DEFAULT 'foo');
CREATE TABLE bar (table_id CHAR(8) NOT NULL DEFAULT 'foo');

Then a function on "base" could look at table_id and know which
table it's working on. A waste of space, but I can think of
uses for it.
--
Christopher Masto Senior Network Monkey NetMonger Communications
chris(at)netmonger(dot)net info(at)netmonger(dot)net http://www.netmonger.net

Free yourself, free your machine, free the daemon -- http://www.freebsd.org/

Tom Lane writes:

> 3. The new column will have a default value if any of the combined
> column specifications have one. The last-specified default (the one
> in the explicitly given column list, or the rightmost parent table
> that gives a default) will be used.

This seems pretty random. It would be more reasonable if multiple
(default) inheritance weren't allowed unless you explicitly specify a new
default for the new column, but we don't have a syntax for this.

> 4. All relevant constraints from all the column specifications will
> be applied. In particular, if any of the specifications includes NOT
> NULL, the resulting column will be NOT NULL. (But the current
> implementation does not support inheritance of UNIQUE or PRIMARY KEY
> constraints, and I do not have time to add that now.)

This is definitely a violation of that Liskov Substitution. If a context
expects a certain table and gets a more restricted table, it will
certainly notice.

> Comments? I'm going to implement and commit this today unless I hear
> loud squawks ...

If we're going to make changes to the inheritance logic, we could
certainly use some more thought than a few hours. If you want to revert
the patch that was installed in 7.0 then ok, but the rest is not
appropriate right now, IMHO.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> 4. All relevant constraints from all the column specifications will
>> be applied. In particular, if any of the specifications includes NOT
>> NULL, the resulting column will be NOT NULL. (But the current
>> implementation does not support inheritance of UNIQUE or PRIMARY KEY
>> constraints, and I do not have time to add that now.)

> This is definitely a violation of that Liskov Substitution. If a context
> expects a certain table and gets a more restricted table, it will
> certainly notice.

Au contraire --- I'd say that if the child table fails to adhere to the
constraints set for the parent table, *that* is a violation of
inheritance. In particular, a table containing NULLs that is a child of
a table in which the same column is marked NOT NULL is likely to blow up
an application that is not expecting to get any nulls back.

In any case, we have already been inheriting general constraints from
parent tables. Relaxing that would be a change of behavior. The
failure to inherit NOT NULL constraints some of the time (in some cases
they were inherited, in some cases not) cannot be construed as anything
but a bug.

> If we're going to make changes to the inheritance logic, we could
> certainly use some more thought than a few hours.

The primary issue here is to revert the 7.0 behavior to what it had been
for many years before that, and secondarily to make NOT NULL inheritance
behave consistently with itself and with other constraints. It doesn't
take hours of thought to justify either.

I will agree that left-to-right vs. right-to-left precedence of
inherited default values is pretty much a random choice, but it's
doubtful that anyone is really depending on that. The existing behavior
was not self-consistent anyway, since it was actually not "the first
specified default" but "the default or lack of same attached to the
first parent containing such a field". For example, if we do not change
this behavior then

create table p1 (f1 int);
create table p2 (f1 int default 1) inherits(p1);

results in p2.f1 having a default, while

create table p1 (f1 int);
create table p2 (f1 int default 1, f2 int);
create table p3 (f3 int) inherits(p1, p2);

results in p3.f1 not having a default. I don't think that can be argued
to be anything but a bug either (consider what happens if p2 also says
NOT NULL for f1).

regards, tom lane

On Fri, Mar 30, 2001 at 12:10:59PM -0500, Tom Lane wrote:
> ncm(at)zembu(dot)com (Nathan Myers) writes:
> > The O-O principle involved here is Liskov Substitution: if the derived
> > table is used in the context of code that thinks it's looking at the
> > base table, does anything break?
>
> I propose the following behavior:
>
> 1. A table can have only one column of a given name. If the same
> column name occurs in multiple parent tables and/or in the explicitly
> specified column list, these column specifications are combined to
> produce a single column specification. A NOTICE will be emitted to
> warn the user that this has happened. The ordinal position of the
> resulting column is determined by its first appearance.

Treatment of like-named members of multiple base types is not done
consistently in the various O-O languages. It's really a snakepit, and
anything you do automatically will cause terrible problems for somebody.
Nonetheless, for any given circumstances some possible approaches are
clearly better than others.

In C++, as in most O-O languages, the like-named members are kept
distinct. When referred to in the context of a base type, the member
chosen is the "right one". Used in the context of the multiply-derived
type, the compiler reports an ambiguity, and you are obliged to qualify
the name explicitly to identify which among the like-named inherited
members you meant. You can declare which one is "really inherited".
Some other languages presume to choose automatically which one they
think you meant. The real danger is from members inherited from way
back up the trees, which you might not know one are there.

Of course PG is different from any O-O language. I don't know if PG
has an equivalent to the "base-class context". I suppose PG has a long
history of merging like-named members, and that the issue is just of
the details of how the merge happens.

> 4. All relevant constraints from all the column specifications will
> be applied. In particular, if any of the specifications includes NOT
> NULL, the resulting column will be NOT NULL. (But the current
> implementation does not support inheritance of UNIQUE or PRIMARY KEY
> constraints, and I do not have time to add that now.)

Sounds like a TODO item...

Do all the triggers of the base tables get applied, to be run one after
another?

--
Nathan Myers
ncm(at)zembu(dot)com

On Fri, Mar 30, 2001 at 11:05:53PM +0200, Peter Eisentraut wrote:
> Tom Lane writes:
>
> > 3. The new column will have a default value if any of the combined
> > column specifications have one. The last-specified default (the one
> > in the explicitly given column list, or the rightmost parent table
> > that gives a default) will be used.
>
> This seems pretty random. It would be more reasonable if multiple
> (default) inheritance weren't allowed unless you explicitly specify a new
> default for the new column, but we don't have a syntax for this.

I agree, but I thought the original issue was that PG _does_ now have
syntax for it. Any conflict in default values should result in either
a failure, or "no default". Choosing a default randomly, or according
to an arbitrary and complicated rule (same thing), is a source of bugs.

> > 4. All relevant constraints from all the column specifications will
> > be applied. In particular, if any of the specifications includes NOT
> > NULL, the resulting column will be NOT NULL. (But the current
> > implementation does not support inheritance of UNIQUE or PRIMARY KEY
> > constraints, and I do not have time to add that now.)
>
> This is definitely a violation of that Liskov Substitution. If a context
> expects a certain table and gets a more restricted table, it will
> certainly notice.

Not so. The rule is that the base-table code only has to understand
the derived table. The derived table need not be able to represent
all values possible in the base table.

Nathan Myers
ncm(at)zembu(dot)com

At 12:10 30/03/01 -0500, Tom Lane wrote:
>
>Comments? I'm going to implement and commit this today unless I hear
>loud squawks ...
>

Not a squawk as such, but does this have implications for pg_dump?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> Not a squawk as such, but does this have implications for pg_dump?

Good point. With recently-committed changes, try:

regression=# create table p1 (f1 int default 42 not null, f2 int);
CREATE
regression=# create table c1 (f1 int, f2 int default 7) inherits (p1);
NOTICE: CREATE TABLE: merging attribute "f1" with inherited definition
NOTICE: CREATE TABLE: merging attribute "f2" with inherited definition
CREATE
regression=# create table c2 (f1 int default 43, f2 int not null) inherits (p1);
NOTICE: CREATE TABLE: merging attribute "f1" with inherited definition
NOTICE: CREATE TABLE: merging attribute "f2" with inherited definition
CREATE

pg_dump dumps both c1 and c2 like this:

CREATE TABLE "c2" (

)
inherits ("p1");

which is OK as far as the field set goes, but it loses the additional
DEFAULT and NOT NULL information for the child table. Any thoughts on
the best way to fix this?

regards, tom lane

At 01:36 31/03/01 -0500, Tom Lane wrote:
>
>which is OK as far as the field set goes, but it loses the additional
>DEFAULT and NOT NULL information for the child table. Any thoughts on
>the best way to fix this?
>

Can pg_dump easily detect overridden attrs? If so, we just treat them as
table attrs and let the backend do it's stuff.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

ncm(at)zembu(dot)com (Nathan Myers) writes:
> Of course PG is different from any O-O language. I don't know if PG
> has an equivalent to the "base-class context". I suppose PG has a long
> history of merging like-named members, and that the issue is just of
> the details of how the merge happens.

Yes; AFAICT that behavior goes back to PostQUEL. It was partially
disabled (without adequate discussion I guess) in 7.0, but it's been
around for a long time.

>> 4. All relevant constraints from all the column specifications will
>> be applied. In particular, if any of the specifications includes NOT
>> NULL, the resulting column will be NOT NULL. (But the current
>> implementation does not support inheritance of UNIQUE or PRIMARY KEY
>> constraints, and I do not have time to add that now.)

> Sounds like a TODO item...

There's something about it in TODO already. There are some definitional
issues though (should uniqueness be across ALL tables of the inheritance
hierarchy, or per-table? If the former, how would we implement it?).
I believe you can find past discussions about this in the archives.

> Do all the triggers of the base tables get applied, to be run one after
> another?

Triggers aren't inherited either. Possibly they should be, but again
I think some forethought is needed...

regards, tom lane

ncm(at)zembu(dot)com (Nathan Myers) writes:
>> This seems pretty random. It would be more reasonable if multiple
>> (default) inheritance weren't allowed unless you explicitly specify a new
>> default for the new column, but we don't have a syntax for this.

> I agree, but I thought the original issue was that PG _does_ now have
> syntax for it. Any conflict in default values should result in either
> a failure, or "no default". Choosing a default randomly, or according
> to an arbitrary and complicated rule (same thing), is a source of
> bugs.

Well, we *do* have a syntax for specifying a new default (the same one
that worked pre-7.0 and does now again). I guess what you are proposing
is the rule "If conflicting default values are inherited from multiple
parents that each define the same column name, then an error is reported
unless the child table redeclares the column and specifies a new default
to override the inherited ones".

That is:

create table p1 (f1 int default 1);
create table p2 (f1 int default 2);
create table c1 (f2 float) inherits(p1, p2);

would draw an error about conflicting defaults for c1.f1, but

create table c1 (f1 int default 3, f2 float) inherits(p1, p2);

would be accepted (and 3 would become the default for c1.f1).

This would take a few more lines of code, but I'm willing to do it if
people think it's a safer behavior than picking one of the inherited
default values.

regards, tom lane

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> At 01:36 31/03/01 -0500, Tom Lane wrote:
>> which is OK as far as the field set goes, but it loses the additional
>> DEFAULT and NOT NULL information for the child table. Any thoughts on
>> the best way to fix this?

> Can pg_dump easily detect overridden attrs? If so, we just treat them as
> table attrs and let the backend do it's stuff.

Well, it's already detecting inherited attrs so it can suppress them
from the explicit column list. Perhaps we should just hack that code
to not suppress inherited attrs when they have default values and/or
NOT NULL that's not in the parent.

regards, tom lane

At 20:02 31/03/01 -0500, Tom Lane wrote:
>
>Perhaps we should just hack that code
>to not suppress inherited attrs when they have default values and/or
>NOT NULL that's not in the parent.

That's what I meant; can we easily do the 'not in the parent' part, since
we may have to go up a long hierarchy to find the parent?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> At 20:02 31/03/01 -0500, Tom Lane wrote:
>> Perhaps we should just hack that code
>> to not suppress inherited attrs when they have default values and/or
>> NOT NULL that's not in the parent.

> That's what I meant; can we easily do the 'not in the parent' part, since
> we may have to go up a long hierarchy to find the parent?

pg_dump must already contain code to traverse the inheritance hierarchy
(I haven't looked to see where). Couldn't we just extend it to believe
that it's found a match only if the default value and NOT NULL state
match, as well as the column name?

regards, tom lane

At 20:25 31/03/01 -0500, Tom Lane wrote:
>
>> That's what I meant; can we easily do the 'not in the parent' part, since
>> we may have to go up a long hierarchy to find the parent?
>
>pg_dump must already contain code to traverse the inheritance hierarchy
>(I haven't looked to see where). Couldn't we just extend it to believe
>that it's found a match only if the default value and NOT NULL state
>match, as well as the column name?
>

You are correct; flagInhAttrs in common.c does the work, and it should be
easy to change. At the moment it extracts all tables attrs then looks for
an attr with the same name in any parent table. We can extend this to check
NOT NULL and DEFAULT. Should I also check TYPEDEFN - can that be changed?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> You are correct; flagInhAttrs in common.c does the work, and it should be
> easy to change. At the moment it extracts all tables attrs then looks for
> an attr with the same name in any parent table. We can extend this to check
> NOT NULL and DEFAULT. Should I also check TYPEDEFN - can that be changed?

We presently disallow change of type in child tables, but you might as
well check that too, if it's just one more strcmp ...

regards, tom lane

At 20:40 31/03/01 -0500, Tom Lane wrote:
>Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
>> You are correct; flagInhAttrs in common.c does the work, and it should be
>> easy to change. At the moment it extracts all tables attrs then looks for
>> an attr with the same name in any parent table. We can extend this to check
>> NOT NULL and DEFAULT. Should I also check TYPEDEFN - can that be changed?
>
>We presently disallow change of type in child tables, but you might as
>well check that too, if it's just one more strcmp ...

Looks like it; and just to confirm, based on previous messages, I assume I
should look at the parents from right to left?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> Looks like it; and just to confirm, based on previous messages, I assume I
> should look at the parents from right to left?

At the moment that would be the right thing to do.

If we change the code again based on the latest discussion, then pg_dump
would have to detect whether there are conflicting defaults, which would
mean looking at all the parents not just the rightmost one. Ugh. That
might be a good reason not to change...

regards, tom lane

At 21:00 31/03/01 -0500, Tom Lane wrote:
>
>If we change the code again based on the latest discussion, then pg_dump
>would have to detect whether there are conflicting defaults, which would
>mean looking at all the parents not just the rightmost one. Ugh. That
>might be a good reason not to change...
>

Shall I hold off on this for a day or two to let the other discussion
settle down? It seems whatever happens, we should check NOT NULL.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Christopher Masto <chris(at)netmonger(dot)net> writes:
> Another thing that seems kind of interesting would be to have:
> CREATE TABLE base (table_id CHAR(8) NOT NULL [, etc.]);
> CREATE TABLE foo (table_id CHAR(8) NOT NULL DEFAULT 'foo');
> CREATE TABLE bar (table_id CHAR(8) NOT NULL DEFAULT 'bar');
> Then a function on "base" could look at table_id and know which
> table it's working on. A waste of space, but I can think of
> uses for it.

This particular need is superseded in 7.1 by the 'tableoid'
pseudo-column. However you can certainly imagine variants of this
that tableoid doesn't handle, for example columns where the subtable
creator can provide a useful-but-not-always-correct default value.

regards, tom lane

Tom Lane writes:

> Well, we *do* have a syntax for specifying a new default (the same one
> that worked pre-7.0 and does now again). I guess what you are proposing
> is the rule "If conflicting default values are inherited from multiple
> parents that each define the same column name, then an error is reported
> unless the child table redeclares the column and specifies a new default
> to override the inherited ones".

This was the idea. If it's to complicated to do now, let's at least keep
it in mind.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane writes:
>> Well, we *do* have a syntax for specifying a new default (the same one
>> that worked pre-7.0 and does now again). I guess what you are proposing
>> is the rule "If conflicting default values are inherited from multiple
>> parents that each define the same column name, then an error is reported
>> unless the child table redeclares the column and specifies a new default
>> to override the inherited ones".

> This was the idea. If it's to complicated to do now, let's at least keep
> it in mind.

You and Nathan appear to like it, and no one else has objected.
I shall make it so.

Philip: the rule that pg_dump needs to apply w.r.t. defaults for
inherited fields is that if an inherited field has a default and
either (a) no parent table supplies a default, or (b) any parent
table supplies a default different from the child's, then pg_dump
had better emit the child field explicitly.

regards, tom lane

On Sat, Mar 31, 2001 at 07:44:30PM -0500, Tom Lane wrote:
> ncm(at)zembu(dot)com (Nathan Myers) writes:
> >> This seems pretty random. It would be more reasonable if multiple
> >> (default) inheritance weren't allowed unless you explicitly specify a new
> >> default for the new column, but we don't have a syntax for this.
>
> > I agree, but I thought the original issue was that PG _does_ now have
> > syntax for it. Any conflict in default values should result in either
> > a failure, or "no default". Choosing a default randomly, or according
> > to an arbitrary and complicated rule (same thing), is a source of
> > bugs.
>
> Well, we *do* have a syntax for specifying a new default (the same one
> that worked pre-7.0 and does now again). I guess what you are proposing
> is the rule "If conflicting default values are inherited from multiple
> parents that each define the same column name, then an error is reported
> unless the child table redeclares the column and specifies a new default
> to override the inherited ones".
>
> That is:
>
> create table p1 (f1 int default 1);
> create table p2 (f1 int default 2);
> create table c1 (f2 float) inherits(p1, p2); # XXX
>
> would draw an error about conflicting defaults for c1.f1, but
>
> create table c1 (f1 int default 3, f2 float) inherits(p1, p2);
>
> would be accepted (and 3 would become the default for c1.f1).
>
> This would take a few more lines of code, but I'm willing to do it if
> people think it's a safer behavior than picking one of the inherited
> default values.

I do.

Allowing the line marked XXX above, but asserting no default for
c1.f1 in that case, would be equally safe. (A warning would be
polite, anyhow.) User code that doesn't rely on the default wouldn't
notice. You only need to choose a default if somebody adding rows to
c1 uses it.

Nathan Myers
ncm(at)zembu(dot)com

On Sun, Apr 01, 2001 at 03:15:56PM -0400, Tom Lane wrote:
> Christopher Masto <chris(at)netmonger(dot)net> writes:
> > Another thing that seems kind of interesting would be to have:
> > CREATE TABLE base (table_id CHAR(8) NOT NULL [, etc.]);
> > CREATE TABLE foo (table_id CHAR(8) NOT NULL DEFAULT 'foo');
> > CREATE TABLE bar (table_id CHAR(8) NOT NULL DEFAULT 'bar');
> > Then a function on "base" could look at table_id and know which
> > table it's working on. A waste of space, but I can think of
> > uses for it.
>
> This particular need is superseded in 7.1 by the 'tableoid'
> pseudo-column. However you can certainly imagine variants of this
> that tableoid doesn't handle, for example columns where the subtable
> creator can provide a useful-but-not-always-correct default value.

A bit of O-O doctrine... when you find yourself tempted to do something
like the above, it usually means you're trying to do the wrong thing.
You may not have a choice, in some cases, but you should know you are
on the way to architecture meltdown. "She'll blow, Cap'n!"

Nathan Myers
ncm(at)zembu(dot)com

On Mon, Apr 02, 2001 at 01:27:06PM -0400, Tom Lane wrote:
> Philip: the rule that pg_dump needs to apply w.r.t. defaults for
> inherited fields is that if an inherited field has a default and
> either (a) no parent table supplies a default, or (b) any parent
> table supplies a default different from the child's, then pg_dump
> had better emit the child field explicitly.

The rule above appears to work even if inherited-default conflicts
are not taken as an error, but just result in a derived-table column
with no default.

Nathan Myers
ncm(at)zembu(dot)com

ncm(at)zembu(dot)com (Nathan Myers) writes:
> On Sat, Mar 31, 2001 at 07:44:30PM -0500, Tom Lane wrote:
>> That is:
>>
>> create table p1 (f1 int default 1);
>> create table p2 (f1 int default 2);
>> create table c1 (f2 float) inherits(p1, p2); # XXX
>>
>> would draw an error about conflicting defaults for c1.f1, but
>>
>> create table c1 (f1 int default 3, f2 float) inherits(p1, p2);
>>
>> would be accepted (and 3 would become the default for c1.f1).
>>
>> This would take a few more lines of code, but I'm willing to do it if
>> people think it's a safer behavior than picking one of the inherited
>> default values.

> Allowing the line marked XXX above, but asserting no default for
> c1.f1 in that case, would be equally safe. (A warning would be
> polite, anyhow.)

The trouble with that is that we don't have such a concept as "no
default", if by that you mean "INSERTs *must* specify a value".
What would really happen would be that the effective default would
be NULL, which I think would be fairly surprising behavior, since
none of the three tables involved asked for that.

I have committed code that raises an error in cases such as XXX above.
Let's try it like that for awhile and see if anyone complains ...

regards, tom lane

At 13:27 2/04/01 -0400, Tom Lane wrote:
>
>Philip: the rule that pg_dump needs to apply w.r.t. defaults for
>inherited fields is that if an inherited field has a default and
>either (a) no parent table supplies a default, or (b) any parent
>table supplies a default different from the child's, then pg_dump
>had better emit the child field explicitly.
>

What is happening with IS NULL constraints (and type names)? I presume the
above rule should be applied to each of these fields?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> At 13:27 2/04/01 -0400, Tom Lane wrote:
>> Philip: the rule that pg_dump needs to apply w.r.t. defaults for
>> inherited fields is that if an inherited field has a default and
>> either (a) no parent table supplies a default, or (b) any parent
>> table supplies a default different from the child's, then pg_dump
>> had better emit the child field explicitly.

> What is happening with IS NULL constraints (and type names)?

NOT NULL on a child field would only force it to be dumped if none
of the parents say NOT NULL. Type name really is not an issue since
it will have to be the same in all the tables anyway; I wouldn't bother
expending any code there.

regards, tom lane

At 23:57 2/04/01 -0400, Tom Lane wrote:
>
>NOT NULL on a child field would only force it to be dumped if none
>of the parents say NOT NULL. Type name really is not an issue since
>it will have to be the same in all the tables anyway; I wouldn't bother
>expending any code there.
>

I've made tha changes and it all seems to work, bu there is a minor
inconsistency:

create table p3_def1(f1 int default 1, f2 int);
create table c5(f1 int not null, f3 int) inherits(p3_def1);

c5 gets dumped as:

CREATE TABLE "c5" (
"f1" integer DEFAULT 1 NOT NULL,
"f3" integer
)
inherits ("p3_def1");

since the NOT NULL forces the field to dump, and it is dumps as though it
were a real field.

Similarly,

create table p2_nn(f1 int not null, f2 int not null);
create table c6(f1 int default 2, ,f3 int) inherits(p2_nn);

results in C6 being dumped as:

CREATE TABLE "c6" (
"f1" integer DEFAULT 2 NOT NULL,
"f3" integer
)
inherits ("p2_nn");

I think it needs to dump ONLY the overridden settigns, since a change to
the overriding behaviour of a child seems like a bad thing.

What do you think?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> I think it needs to dump ONLY the overridden settigns, since a change to
> the overriding behaviour of a child seems like a bad thing.

I was about to say it's not worth the trouble, but I see you already
did it ...

regards, tom lane