Changing the default value of an inherited column

Changing the default value of an inherited column

Post by Tom La » Fri, 30 Mar 2001 03:30:57



I just had a discussion with a user who doesn't want to update from
6.4.something to 7.0.* because 7.0 broke a feature he likes, namely
the ability to change the default value of a column inherited from
a parent table.  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.

I can recall a number of requests from users to be able to change
the default value when inheriting a column, but I had not realized
that it was actually possible to do this in older Postgres releases.

After digging into the CVS logs and mail archives, I find that Peter E.
changed the behavior in January 2000, apparently without realizing that
he was disabling a feature that some considered useful.  Here's his
comment in pghackers, 26 Jan 2000 19:35:14 +0100 (CET):

Quote:> ... I just looked into item 'Disallow inherited columns
> with the same name as new columns' and it seems that someone actually made
> provisions for this to be allowed, meaning that
> create table test1 (x int);
> create table test2 (x int) inherits (test1);
> would result in test2 looking exactly like test1. No one knows what the
> motivation was. (I removed it anyway.)

Given that Peter was responding to a TODO item, evidently someone had
complained about the lack of any complaint for this construction, but
I wonder whether the someone really understood all the implications.
Allowing this construction allows one to change the default, or add
(but not remove) column constraints, and in general it seems kinda
useful.

The question of the day: should we put this back the way it was?
If so, should we try to squeeze it into 7.1, or wait another release
cycle?  (I can see about equally good arguments for considering this
a feature addition or a bug fix...)  Should there be a NOTICE about
the duplicated column name, or is the old silent treatment okay?

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

 
 
 

Changing the default value of an inherited column

Post by Peter Eisentra » Fri, 30 Mar 2001 06:05:31


Quote: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.
But given that our inheritance system actually has conceivably little
resemblance to real OO, I don't really care.

--

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

Changing the default value of an inherited column

Post by Oliver Elphic » Fri, 30 Mar 2001 22:29:12


  >I just had a discussion with a user who doesn't want to update from
  >6.4.something to 7.0.* because 7.0 broke a feature he likes, namely
  >the ability to change the default value of a column inherited from
  >a parent table.  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.
 ...
  >The question of the day: should we put this back the way it was?
  >If so, should we try to squeeze it into 7.1, or wait another release
  >cycle?  (I can see about equally good arguments for considering this
  >a feature addition or a bug fix...)  Should there be a NOTICE about
  >the duplicated column name, or is the old silent treatment okay?

I would very much like to have this feature restored; I think there should
be a NOTICE, just in case the duplication is caused by mistyping.

--

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  

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

Changing the default value of an inherited column

Post by Oliver Elphic » Fri, 30 Mar 2001 22:29:52


  >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.

--

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  

---------------------------(end of broadcast)---------------------------

 
 
 

Changing the default value of an inherited column

Post by Tom La » Sat, 31 Mar 2001 01:57:49



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

---------------------------(end of broadcast)---------------------------

 
 
 

Changing the default value of an inherited column

Post by Peter Eisentra » Sat, 31 Mar 2001 01:56:53


Oliver Elphick writes:

>   >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...

--

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

Changing the default value of an inherited column

Post by Nathan Mye » Sat, 31 Mar 2001 06:00:03




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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

Changing the default value of an inherited column

Post by Tom La » Sun, 01 Apr 2001 02:27:23



> 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

Changing the default value of an inherited column

Post by Peter Eisentra » Sun, 01 Apr 2001 06:15:55


Quote: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.

Quote:> 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.

Quote:> 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.

--

---------------------------(end of broadcast)---------------------------

 
 
 

Changing the default value of an inherited column

Post by Tom La » Sun, 01 Apr 2001 06:16:48



>> 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.

Quote:> 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

---------------------------(end of broadcast)---------------------------

 
 
 

Changing the default value of an inherited column

Post by Nathan Mye » Sun, 01 Apr 2001 06:30:49




> > 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.  

Quote:> 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

---------------------------(end of broadcast)---------------------------

 
 
 

Changing the default value of an inherited column

Post by Nathan Mye » Sun, 01 Apr 2001 06:41:01



> 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.

Quote:> > 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

---------------------------(end of broadcast)---------------------------

 
 
 

1. Changing the default value of an inherited column

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

---------------------------(end of broadcast)---------------------------

2. Display one record from multiple records

3. Changing the default value of an inherited

4. one other big mysql->postgresql item

5. overriding default value in inherited column

6. Oracle Jobs / Employment Opportunities Web Site

7. Re : overriding default value in inherited column (+ set_value function)

8. PDOX4.5 on W2k Server

9. Re : overriding default value in inherited column (+

10. Alter column changing default value

11. Change the Default value of a Column.

12. How to change the default value of an exisiting column via T-SQL

13. Default value for DECIMAL columns changed when altering table