From: | "Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>, "Peter Eisentraut" <peter_e(at)gmx(dot)net>, "PostgreSQL Development" <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | RE: ALTER TABLE DROP COLUMN |
Date: | 2000-06-12 01:40:47 |
Message-ID: | 000b01bfd40f$3b3091e0$2801007e@tpf.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> -----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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2000-06-12 01:58:04 | Re: ALTER TABLE DROP COLUMN |
Previous Message | Bruce Momjian | 2000-06-12 01:05:01 | Re: Adding time to DATE type |