The following bug has been logged online:
Bug reference: 2335
Logged by: Jozef Behran
Email address: jozef.behran@krs.sk
PostgreSQL version: 8.1.0
Operating system: Linux (Mandriva Linux 10.0)
Description: Order of data in data-only dumps
Details:
When doing a data-only dump, the tables are dumped in alphabetical order.
This is unfortunate as I might want to fetch the file into a fully created
schema (including indices/constraints) for example to prevent bad data from
being fetched without an error ot to accomodate old data in a new schema and
the alphabetical order of the tables may be wrong.
Older versions (7.x particularly) ensure the order of the tables in the dump
is such that it can be fetched into fully created schema without problems.
It is OK to have the tables ordered aplabetically in full dumps as it is
unlikely to fetch such dump into something other than an empty database.
To see some problematic data try:
BEGIN;
CREATE TABLE b (
id int4,
PRIMARY KEY(id)
);
CREATE TABLE a (
id int4,
other int4 references b
on update cascade on delete cascade
);
INSERT INTO b VALUES (1);
INSERT INTO a VALUES (1,1);
COMMIT;
Then try
pg_dump -a <database_name>
The pg_dump will dump a, then b but it should do it in reverse order.
I think the few more CPU cycles needed to sort the tables like in 7.x series
is not worth the reduced usability of the dumps when incorrect table order
is used. Especially when the order of data does not break anything even in
the full dump.