Thread: Changing the default value of an inherited column
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):
> ... 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
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. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Tom Lane wrote: >I just had a discussion with a user who doesn't want to update from >6.4.something to 7.0.* because 7.0broke 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(idint default 2, tag text) inherits (one); > >with the net effect that table "two" has just one "id" column with >defaultvalue 2.... >The question of the day: should we put this back the way it was? >If so, should we try to squeeze itinto 7.1, or wait another release >cycle? (I can see about equally good arguments for considering this >a feature additionor 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. -- 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 ======================================== "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
Peter Eisentraut wrote: >Tom Lane writes: > >> It seems that in pre-7.0 Postgres, this works: >> >> create table one(id intdefault 1, descr text); >> create table two(id int default 2, tag text) inherits (one); >> >> with the net effect thattable "two" has just one "id" column with >> default value 2. > >Although the liberty to do anything you want seems appealingat 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.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 ======================================== "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@gmx.net http://yi.org/peter-e/
"Oliver Elphick" <olly@lfix.co.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@zembu.com
ncm@zembu.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
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@gmx.net http://yi.org/peter-e/
Peter Eisentraut <peter_e@gmx.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@zembu.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@zembu.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@zembu.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@rhyme.com.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@zembu.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@zembu.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@rhyme.com.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@rhyme.com.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
Philip Warner <pjw@rhyme.com.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
Philip Warner <pjw@rhyme.com.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 20:40 31/03/01 -0500, Tom Lane wrote: >Philip Warner <pjw@rhyme.com.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 |/
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@netmonger.net info@netmonger.net http://www.netmonger.net Free yourself, free your machine, free the daemon -- http://www.freebsd.org/
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 |/
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@netmonger.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@gmx.net http://yi.org/peter-e/
Peter Eisentraut <peter_e@gmx.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@zembu.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@zembu.com
On Sun, Apr 01, 2001 at 03:15:56PM -0400, Tom Lane wrote: > Christopher Masto <chris@netmonger.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@zembu.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@zembu.com
ncm@zembu.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@rhyme.com.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@rhyme.com.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