Re: [HACKERS] Happy column dropping

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> With caveats, it is now possible to drop columns from tables.
> The implementation is based on copying the old table to a new one minus
> the specified column. This procedure changes the oids of everyone
> involved, so I was wondering if
> a) this is a good reason to tell people to stop using oids as keys,

AFAIK there is nothing particularly magic about OIDs. You could
perfectly well create the new table with the same OIDs as are in the
old table (see COPY WITH OIDS if you are wondering how).

User-level stuff like referential integrity triggers might get unhappy,
but you're not going to let triggers see what you're doing, right ;-) ?

> Is it possible/safe to change to oid of the new pg_class entry back to the
> old one? In that case the trouble of moving over all the constraints, etc.
> would be half the work.

Wrong way to think about it. You should be doing a heap_update of the
catalog tuples that need to change, ISTM.

You could almost get away with doing it like you describe, except that
there is a unique index on pg_class OIDs these days (right, Bruce?)
and that index will kick out an error. But heap_update on the original
table tuple will work.

I think what we may want here is something comparable to what's been
discussed recently for VACUUM: build the new table as a new heap file
and then rename the physical file into place, without really doing
anything to the pg_class tuple --- except of course you'd need to
heap_update it to adjust the number-of-attributes field.

> 2) how do I find out if the dropped column is referenced in a constraint,
> trigger, rule (this is necessary for a correct RESTRICT/CASCADE
> implementation)

Actually it's worse than that: you need to be prepared to renumber the
columns after the dropped one, too. Probably what you will need to do
is read in and deparse all the relevant rules and triggers, then reparse
them against the updated table schema. Ugly. And no, I have no idea
how you even *find* all the relevant rules. (Jan?)

> Oh, btw., heaven help you if you try this on tables that are inherited
> from.

The whole thing should be done inside a recursive routine that applies
the same change to all children of the target table. See ALTER TABLE
ADD COLUMN for an example. (ADD COLUMN is pretty broken too, since it
doesn't preserve consistency of column numbering across child tables ---
want to reimplement it in this same style?)

regards, tom lane