RE: ALTER TABLE DROP COLUMN

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)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(at)tpf(dot)co(dot)jp