The following bug has been logged online:
Bug reference: 1474
Logged by: Gerard Krupa
Email address: gerard.krupa@marconi.com
PostgreSQL version: 8.0.1
Operating system: HP-UX B.11.11 U 9000/800 650319392 unlimited-user
license PA-RISC 2.0 (64-bit)
Description: pg_dump outputs tables in wrong order
Details:
pg_dump outputs table data COPYs and INSERTs in alphabetical order ignoring
any foreign key constraints that exist between those tables. In a full dump
this is not a problem as the constraints are removed and then re-added after
the data but using a data-only dump the data will be restored in the wrong
order causing FK constraint violations.
In 7.4.6 the output was always sorted in such a way that FK violations would
not occur.
To reproduce:
1. Add the schema and data below to a clean database
2. Dump up the data using pg_dump --data-only
3. Delete all tuples from the two relations
4. Use the dump generated in step 2 to restore the data
CREATE TABLE b_first (
id integer NOT NULL PRIMARY KEY
);
CREATE TABLE a_second (
id integer NOT NULL PRIMARY KEY,
ref integer NOT NULL
);
ALTER TABLE a_second
ADD CONSTRAINT the_link FOREIGN KEY (ref)
REFERENCES b_first(id);
COPY b_first (id) FROM stdin;
1
2
3
\.
COPY a_second (id, ref) FROM stdin;
1 1
2 2
3 3
\.
Compiled using native HP C compiler in both 64- and 32- bit modes - the
problem always occurs.