On Mon, 23 Jul 2001, Keith Irwin wrote:
> Okay, I've done some experiments and here's what I've found out:
>
> 1. In order to drop columns from a table, you must delete and recreate
> the table.
Right now, yes. Hopefully we'll eventually have drop column (but see past
hackers discussions about it).
> 2. Dropping a table deletes all foreign key triggers to that table,
> even if you recreate that table. (Apparently the backend is using
> OIDs for all this rather than the name of the table.)
No, it's using the name of the table internally (which is wrong, it
actually should use oid to prevent some cases of shadowing with
temp tables), however it also keeps the oid of the tables involved to
handle the drops.
Technically all the drops are supposed to have either cascade or restrict
to say whether to cascade the drop to the referencing objects or to refuse
the drop when something references it. That's obnoxious and we don't
have it (or require it), but unfortunately means different parts of the
system do different things.
There's been talk going on -hackers about how to handle drop and
referencing objects, you may want to look at the message archives for the
last few weeks.
> 3. Dropping a table breaks all the "views" based on that table.
Unfortunately yes. You need to drop and recrate them.
> 4. In order to drop a couple of columns and restore things to the way
> they ought to be, you have to:
>
> a. drop and recreate the table (copying data back and forth from a temp
> table)
> b. recreate (drop then create) all the foreign key references
> c. recreate all the views
> d. recreate all the stored procedures (though I didn't test this).
>
> Is this true?
Technically, you shouldn't need to drop the foreign key references
(excepting the case with the broken dumps from 7.0). I don't think
you need to recreate all of your stored procedures. You may need to
disconnect and reconnect from the db server to flush stored plans.