RE: ALTER TABLE DROP COLUMN
От | Hiroshi Inoue |
---|---|
Тема | RE: ALTER TABLE DROP COLUMN |
Дата | |
Msg-id | 000b01bfd40f$3b3091e0$2801007e@tpf.co.jp обсуждение исходный текст |
Ответ на | Re: ALTER TABLE DROP COLUMN (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > >> Seems we have 4 DROP COLUMN ideas: > >> Method Advantage > >> ----------------------------------------------------------------- > >> 1 invisible column marked by negative attnum fast > >> 2 invisible column marked by is_dropped column fast > >> 3 make copy of table without column col removed > >> 4 make new tuples in existing table without column col removed > Hmm,I've received no pg-ML mails for more than 1 day. What's happened with pgsql ML ? > Bruce and I talked about this by phone yesterday, and we realized that > none of these are very satisfactory. #1 and #2 both have the flaw that > applications that examine pg_attribute will probably break: they will > see a sequence of attnum values with gaps in it. And what should the > rel's relnatts field be set to? #3 and #4 are better on that point, > but they leave us with the problem of renumbering references to columns > after the dropped one in constraints, rules, PL functions, etc. > > Furthermore, there is a closely related problem that none of these > approaches give us much help on: recursive ALTER TABLE ADD COLUMN. > Right now, ADD puts the new column at the end of each table it's added > to, which often means that it gets a different column number in child > tables than in parent tables. That leads to havoc for pg_dump. > Inheritance is one of the reason why I didn't take #2. I don't understand marking is_dropped is needed or not when pg_attribute is overhauled for inheritance. I myself have never wanted to use current inheritance functionality mainly because of this big flaw. Judging from the recent discussion about oo(though I don't understand details),the change seems to be needed in order to make inheritance functionality really useful. > I think the only clean solution is to create a clear distinction between > physical and logical column numbers. Each pg_attribute tuple would need > two attnum fields, and pg_class would need two relnatts fields as well. > A column once created would never change its physical column number, but I don't understand inheritance well. In the near future wouldn't the implementation require e.g. attid which is common to all children of a parent and is never changed ? If so,we would need the third attid field which is irrevalent to physical/logical position. If not, physical column number would be sufficient . > its logical column number might change as a consequence of adding or > dropping columns before it. ADD COLUMN would ensure that a column added > to child tables receives the same logical column number as it has in the > parent table, thus solving the dump/reload problem. DROP COLUMN would > assign an invalid logical column number to dropped columns. They could > be numbered zero except that we'd probably still want a unique index on > attrelid+attnum, and the index would complain. I'd suggest using > Hiroshi's idea: give a dropped column a logical attnum equal to > -(physical_attnum + offset). > > With this approach, internal operations on tuples would all use > physical column numbers, but operations that interface to the outside > world would present a view of only the valid logical columns. For > example, the parser would only allow logical columns to be referenced > by name; "SELECT *" would expand to valid logical columns in logical- > column-number order; COPY would send or receive valid logical columns > in logical-column-number order; etc. > > Stored rules and so forth probably should store physical column numbers > so that they need not be modified during column add/drop. > > This would require looking at all the places in the backend to determine > whether they should be working with logical or physical column numbers, > but the design is such that most all places would want to be using > physical numbers, so I don't think it'd be too painful. > > Although I'd prefer to give the replacement columns two new names > (eg, "attlnum" and "attpnum") to ensure we find all uses, this would > surely break applications that examine pg_attribute. For compatibility > we'd have to recycle "attnum" and "relnatts" to indicate logical column > number and logical column count, while adding new fields (say "attpnum" > and "relnpatts") for the physical number and count. > I agree with you that we would add attpnum and change the meaing of attnum as logical column number for backward compatibility. Regards. Hiroshi Inoue Inoue@tpf.co.jp
В списке pgsql-hackers по дате отправления: