Re: ALTER TABLE ... IF EXISTS feature?

On Fri, Nov 5, 2010 at 4:20 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Can you give us a self-contained example of the problem you're talking about?

Sure. Consider the following:

CREATE TABLE t1 (
id integer PRIMARY KEY
);

CREATE TABLE t2 (
id integer PRIMARY KEY,
fk integer
);

ALTER TABLE ONLY t2
ADD CONSTRAINT t2_constr FOREIGN KEY (fk) REFERENCES t1(id);

Try something like this:

createdb foo
psql -1f this_ddl.sql foo
pg_dump --clean foo > cleaning_backup.sql
# db wipe
dropdb foo
createdb foo
psql -1f cleaning_backup.sql foo

The last command will return non-zero and abort the xact early on,
because of the following stanza in pg_dump --clean's output:

ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_constr;
ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_pkey;
ALTER TABLE ONLY public.t1 DROP CONSTRAINT t1_pkey;
DROP TABLE public.t2;
DROP TABLE public.t1;

Since there's no public.t1/t2, it's not possible to ALTER them.

I'm not entirely sure why the DROPs CONSTRAINT on pkeys are being
done, as they only introduce an internal (or is it auto?) style
self-dependency. It is more obvious why foreign keys are dropped,
which is to break up the dependencies so that tables can be dropped
without CASCADE.

fdr