Thread: Column ADDing issues
I took up the issue of * Alter TABLE ADD COLUMN does not honor DEFAULT, add CONSTRAINT and chances are excellent that this will get all done in the next day or three. First a syntactical issue. We currently allow the following statement, although it is not legal SQL: create table test (a int4,b int4 check (a>b) ); It's not legal because the column constraint for "b" may only reference column "b". Instead you could legally write create table test (a int4,b int4,check (a>b) ); because the check constraint is now a table constraint. Big deal. Now the problem is that because I reuse the same syntactical elements, the following will work: create table test (a int4); alter table test add column b int4 check (a>b); No harm done, but how about: create table test (a int4, b int4); alter table test add column c text check (a>b); I guess this would be sort of equivalent to saying alter table test add column c text; alter table test add constraint check (a>b); So, I guess what I'm saying is whether you want to allow the mentioned weirdness or not. Secondly, an internal question. If I use SearchSysCacheTuple() on a condition with several potential matches, what is the defined behaviour? Can I call it again to get the next tuple? Thirdly, about TODO item * ALTER TABLE ADD COLUMN to inherited table put column in wrong place Actually, according to what I would expect out of the blue, it puts it into the *right* place. Even good ol' SQL, although they naturally do not know about inheritance, seems to agree: "... the degree of [the table] is increased by 1 and the ordinal position of that [new] column is equal to the new degree of [the table] ..." (11.11) What that says to me is that if you add a column to a table (during create or alter) then the new column gets placed after all the others. Thus, we're in compliance without even knowing it. Or maybe look at it this way: create table test1 (a int4); create table test2 (b int4) inherits (test1); ^ col #1 ^ col #2 alter table test1* add column c int4; ^ col #3 Everything has its order and it's not like the inheritance as such is broken. Surely, trying to stick the column in between is going to be three times as much work as dropping columns would be, whichever way you do it. (And making attributes invisible ain't gonna help you. ;) What do you all say? -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut <peter_e@gmx.net> writes:
> Or maybe look at it this way:
> create table test1 (a int4);
> create table test2 (b int4) inherits (test1);
> ^ col #1 ^ col #2
> alter table test1* add column c int4;
> ^ col #3
> Everything has its order and it's not like the inheritance as such is
> broken.
Yes, a whole bunch of stuff is broken after this happens. Go back and
consult the archives --- or maybe Chris Bitmead will fill you in; he's
got plenty of scars to show for this set of problems. (All I recall
offhand is that pg_dump and reload can fail to generate a working
database.) The bottom line is that it would be a lot nicer if column c
had the same column position in both the parent table and the child
table(s).
I suggest you be very cautious about messing with ALTER TABLE until you
understand why inheritance makes it such a headache ;-)
regards, tom lane
Peter Eisentraut wrote: >Thirdly, about TODO item > >* ALTER TABLE ADD COLUMN to inherited table put column in wrong
place> >Actually, according to what I would expect out of the blue, it puts it >into the *right* place. Even good ol'
SQL,although they naturally do not >know about inheritance, seems to agree: > >"... the degree of [the table] is
increasedby 1 and the ordinal position >of that [new] column is equal to the new degree of [the table] ..." >(11.11) >
>Whatthat says to me is that if you add a column to a table (during create >or alter) then the new column gets placed
afterall the others. Thus, >we're in compliance without even knowing it. > >Or maybe look at it this way: >create
tabletest1 (a int4); >create table test2 (b int4) inherits (test1); > ^ col #1 ^ col #2
>altertable test1* add column c int4; > ^ col #3 > >Everything has its order and it's not
likethe inheritance as such is >broken. > >Surely, trying to stick the column in between is going to be three times >as
muchwork as dropping columns would be, whichever way you do it. (And >making attributes invisible ain't gonna help you.
;)
It is:
create table test1 (a int4);
create table test2 (b int4) inherits (test1); ^ col #2 ^ col #1
alter table test1* add column c int4; ^ col #3 but needs to be #2, since it _is_
#2 of test1
As far as inheritance goes, all the descendants are treated as one table,
including those created on a different branch from test2. All of them
have to return the right columns for a single query; the two options for
dealing with this seem to be logical column numbering, or rewriting the
descendant tables. (But I haven't spent enough time in the code to be
sure of that.)
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key
ID32B8FAA1 ======================================== "My little children, let us not love in word,
neither in tongue; but in deed and in truth." I John 3:18
On 2000-01-26, Oliver Elphick mentioned: > As far as inheritance goes, all the descendants are treated as one table, > including those created on a different branch from test2. All of them > have to return the right columns for a single query; the two options for > dealing with this seem to be logical column numbering, or rewriting the > descendant tables. (But I haven't spent enough time in the code to be > sure of that.) Logical column ordering seems like a rather clean solution. The system could also make educated decisions such as storing fixed size attributes before variables sized ones. Kind of like a Cluster within the table. I still think that fixing this in pg_dump might be the path of least resistance, but we've got until autumn(?) to figure it out. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
On 2000-01-25, Tom Lane mentioned: > > Everything has its order and it's not like the inheritance as such is > > broken. > > Yes, a whole bunch of stuff is broken after this happens. Go back and > consult the archives --- or maybe Chris Bitmead will fill you in; he's > got plenty of scars to show for this set of problems. (All I recall > offhand is that pg_dump and reload can fail to generate a working > database.) The bottom line is that it would be a lot nicer if column c > had the same column position in both the parent table and the child > table(s). This should be fixed in pg_dump by infering something via the oids of the pg_attribute entries. No need to mess up the backend for it. Maybe pg_dump should optionally dump schemas in terms of insert into pg_something commands rather than actual DDL. ;) > > I suggest you be very cautious about messing with ALTER TABLE until you > understand why inheritance makes it such a headache ;-) I'm just trying to get the defaults and constraints working. If inheritance stays broken the way it previously was, it's beyond my powers. But I get the feeling that people rather not alter their tables unless they have *perfect* alter table commands. I don't feel like arguing with them, they'll just have to do without then. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> > I suggest you be very cautious about messing with ALTER TABLE until you > > understand why inheritance makes it such a headache ;-) > > I'm just trying to get the defaults and constraints working. If > inheritance stays broken the way it previously was, it's beyond my > powers. But I get the feeling that people rather not alter their tables > unless they have *perfect* alter table commands. I don't feel like arguing > with them, they'll just have to do without then. OK, so am I hearing we don't want ALTER TABLE DROP COLUMN without it working for inhertance. Is this really the way we want things? May as well disable ADD COLUMN too because that doesn't work for inheritance either. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Thu, Jan 27, 2000 at 12:52:43PM -0500, Bruce Momjian wrote: > > > I suggest you be very cautious about messing with ALTER TABLE until you > > > understand why inheritance makes it such a headache ;-) > > > > I'm just trying to get the defaults and constraints working. If > > inheritance stays broken the way it previously was, it's beyond my > > powers. But I get the feeling that people rather not alter their tables > > unless they have *perfect* alter table commands. I don't feel like arguing > > with them, they'll just have to do without then. > > OK, so am I hearing we don't want ALTER TABLE DROP COLUMN without it > working for inhertance. Is this really the way we want things? May as > well disable ADD COLUMN too because that doesn't work for inheritance > either. Bruce, I hope your playing devil's advocate here. What I'm hearing, from this discussion, is a number of people interested in getting psql's object features defined in a useful way. As far as impacting Peter's work on getting ALTER commands working, I hope he understands that getting the commands working for the SQL92 case, and leaving inheritance broken (as it currently is) is far preferable to holding off for the *perfect* problem definition. I interpreted his last sentence to mean "they'll just have to do without *perfect* alter table commands", not "I'm not going to work on this at all anymore". At least, I sure that's what I hope he means :-) If you meant the later, Peter, let me say that, in my opinion, very few people are currently using postgres's inheritence features, and are already having to manage with the broken state they're in. I'm glad to see interest in improving them, but I see that as post 7.0 work. Heck, If Oliver & Co. come up with an interesting, consistent object model, that'd be reason enough for an 8.0 release. ;-) (No, please, not another version number thread!) Certainly might be worth a long range development fork in the CVS, at least. Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
> On Thu, Jan 27, 2000 at 12:52:43PM -0500, Bruce Momjian wrote: > > > > I suggest you be very cautious about messing with ALTER TABLE until you > > > > understand why inheritance makes it such a headache ;-) > > > > > > I'm just trying to get the defaults and constraints working. If > > > inheritance stays broken the way it previously was, it's beyond my > > > powers. But I get the feeling that people rather not alter their tables > > > unless they have *perfect* alter table commands. I don't feel like arguing > > > with them, they'll just have to do without then. > > > > OK, so am I hearing we don't want ALTER TABLE DROP COLUMN without it > > working for inhertance. Is this really the way we want things? May as > > well disable ADD COLUMN too because that doesn't work for inheritance > > either. > > Bruce, I hope your playing devil's advocate here. What I'm hearing, > from this discussion, is a number of people interested in getting psql's > object features defined in a useful way. As far as impacting Peter's work > on getting ALTER commands working, I hope he understands that getting > the commands working for the SQL92 case, and leaving inheritance broken > (as it currently is) is far preferable to holding off for the *perfect* > problem definition. I interpreted his last sentence to mean "they'll > just have to do without *perfect* alter table commands", not "I'm not > going to work on this at all anymore". At least, I sure that's what I > hope he means :-) I interpret it the other way. ALTER TABLE DROP is currently disabled in gram.y, and I believe he thinks that unless it is 100%, we don't want it. Now, I believe that is very wrong, and I think it is fine as it is, but I can see why he would think that after the hard time he was given. This whole thing has wrapped around, and now I am not sure what signal we are sending Peter. I personally like what he has done, seeing that he did exactly what I suggested when he asked on the list months ago. I don't want to do a phantom attribute thing at this point with very little payback. I also am not terribly concerned about inheritance either as it needs work in many areas. However, I am only one voice, and no one is giving direction to him. We had better decide what we want in this area. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > > > I suggest you be very cautious about messing with ALTER TABLE until you > > > understand why inheritance makes it such a headache ;-) > > > > I'm just trying to get the defaults and constraints working. If > > inheritance stays broken the way it previously was, it's beyond my > > powers. But I get the feeling that people rather not alter their tables > > unless they have *perfect* alter table commands. I don't feel like arguing > > with them, they'll just have to do without then. > > OK, so am I hearing we don't want ALTER TABLE DROP COLUMN without it > working for inhertance. Is this really the way we want things? May as > well disable ADD COLUMN too because that doesn't work for inheritance > either. > I think this is not a good idea. Many of us doesn't interest inheritance. ALTER ADD COLUMN is not complete but it is better than nothing. > -- > Bruce Momjian | http://www.op.net/~candle > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ************ -- Jose' Soares Bologna, Italy Jose@sferacarta.com