Re: Does dropping a column from a table mess up foreign keys? - Mailing list pgsql-general

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.

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.)

3. Dropping a table breaks all the "views" based on that table.

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?

I have to admit, this seems a bit overmuch for such a simple thing.

Is there some simpler way?  I've got a lot of tables and views, and
though I admit that deleting columns is rare, well...

Keith

Stephan Szabo wrote:
> On Fri, 20 Jul 2001, IRWIN,KEITH (Non-HP-Corvallis,ex1) wrote:
>
>
>>Hi--
>>
>>I'm getting the following error:
>>
>>    ERROR:  Relation "accounts" with OID 72496 no longer exists
>>
>>What I did was to drop a couple of columns using the example Bruce
>>provides in his book on page 264.  Briefly, it's something like:
>>
>>    create table temp as select * from accounts;
>>    drop table accounts;
>>    create table accounts (etc with columns missing);
>>    insert into accounts select <all except dropped cols> from temp;
>>    drop table temp;
>>    grant update,select,insert,delete on accounts to user;
>>
>
> If you've dumped and restored with 7.0's pg_dump (I'm not sure when it was
> fixed, may have been in 7.1.2) there was a problem with the dumped trigger
> statements which caused the relationship that tells when to drop the
> triggers for fk to not exist after the restore.  Theoretically, your
> constraints should have gone away at the drop table accounts;.  I.e.,
> even in the best case, the above will not preserve foreign key constraints
> pointing to the changed table, you'd need to use alter table to re-add
> the constraints.
>
>
>>And so on.  I've also updated an "accounts_view" based on this table so
>>that the dropped columns wouldn't appear (the view being defined with an
>>asterisk field list).
>>
>>I have lots of tables with "references accounts(id)" in them.  Are these
>>going to be screwed up because of the drop/create above?
>>
>>I even tried dumping the DB after the changes, then pg_restoring them, but
>>I get a message something like, Relation ACCOUNTS doesn't exist.  I was
>>thinking that restoring the db in this way would recalculate the OIDs.
>>
>
> When do you get the relation ACCOUNTS doesn't exist message?  When you try
> to do an insert/update?
>
> I'd suggest starting by looking pg_trigger and dropping the constraint
> triggers (warning, you need to double quote the constraint name, the
> case is significant) that reference accounts and use alter table add
> constraint to add the constraints back.
>
>