Обсуждение: Does dropping a column from a table mess up foreign keys?

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;

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.

If I use pg_restore with --orig-order, I get a failure saying "Accounts"
doesn't exist. If I use pg_restore with --oid-order, I get an error about a
sequence that doesn't exist, or something like that.  This tells me that
pg_dump --format=c is really screwed up, or that I fundamentally don't
understand how to backup/restore my database.  I used to do:

    pgdump > dump.file

and then

    cat dump.file | psql database

but that no longer works either, as the pgdump dumps the grant statements
BEFORE the tables they're supposed to grant permissions to.

Is there some command I should do BEFORE the changes due to the fact that
other tables have contraints based on "accounts"?  (Now that I think about
it, I'm amazed that I can drop a table with data dependent on it.)

 Clues?

Keith


----
Keith Irwin
Development Ecosystem Team
Architecture & Development Services
E-Services Deployment & Operations
Email: keith_irwin@non.hp.com
Phone: 541.715.0032

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.

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

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.