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

From IRWIN,KEITH (Non-HP-Corvallis,ex1)
Subject Does dropping a column from a table mess up foreign keys?
Date
Msg-id 8A5088D4530AD511870E00D0B7AF539A402F49@xcor03.cv.hp.com
Whole thread Raw
Responses Re: Does dropping a column from a table mess up foreign keys?
List pgsql-general
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

pgsql-general by date:

Previous
From: "Thalis A. Kalfigopoulos"
Date:
Subject: shared_buffers revisited
Next
From: Bruce Momjian
Date:
Subject: Re: shared_buffer=2*max_connections?