On Fri, Nov 5, 2010 at 4:20 PM, Robert Haas <robertmhaas@gmail.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