pg_dump table ordering bug [8.0.1]
Hi!
Our upgrade from 7.4.6 to 8.0.1 only had one small glitch. Two tables
got dumped in the wrong order (before their dependecies) and had to get
their contents added manually after the restore. I've atleast isolated
the part where things go wrong.
Two files are attached, related as follows (all binaries from 8.0.1):
psql -f db-9-spec.sql
pg_dump -n debug database > db-9-dump.sql
[drop schema debug cascade]
psql -f db-9-dump.sql
psql:db-9-dump.sql:302: ERROR: new row for relation "form_a_int"
violates check constraint "form_a_int_qid_check"
CONTEXT: COPY form_a_int, line 1: "1 2 1109 5"
psql:db-9-dump.sql:311: ERROR: new row for relation "form_a_text"
violates check constraint "form_a_text_qid_check"
CONTEXT: COPY form_a_text, line 1: "1 1 1109 foo"
The tables have both check and reference constraints. The errors are
from check constraints but the reference constraints would have kicked
in next as the referenced data is below this table in the dump file...
ida=# \d debug.form_a_int
Table "debug.form_a_int"
Column | Type | Modifiers
-----------+---------+-----------
finstance | integer | not null
qid | integer | not null
uid | integer | not null
a_int | integer |
Indexes:
"form_a_int_pkey" primary key, btree (finstance, qid, uid)
Check constraints:
"form_a_int_check" CHECK (debug.match_q_instance(finstance, qid))
"form_a_int_qid_check" CHECK (debug.get_atype(qid) = 'INT'::text)
Foreign-key constraints:
"form_a_int_qid_fkey" FOREIGN KEY (qid) REFERENCES debug.form_q(qid)
ON UPDATE CASCADE ON DELETE CASCADE
"form_a_int_finstance_fkey" FOREIGN KEY (finstance) REFERENCES
debug.form_instance(finstance) ON UPDATE CASCADE ON DELETE CASCADE
And the dump data order is:
-- Data for Name: form_a_int;
-- Data for Name: form_instance;
-- Data for Name: form_q;
Regards,
Andreas (Not a subscriber of this list)
Andreas Lange wrote :
Hi!
Our upgrade from 7.4.6 to 8.0.1 only had one small glitch. Two tables got
dumped in the wrong order
(before their dependecies) and had to get their contents added manually
after the restore. I've atleast
isolated the part where things go wrong.
I've experienced a similar problem with a data-only dump using 8.0.1's
pg_dump. It seems that the COPYs (or INSERTs) are generated in
alphabetical order of table name, ignoring foreign key contraints and
requiring the output to be post-processed. In a full dump of the database
the constraints are added after the data is inserted so there are no
problems. See the two attached dumps of the same database as an example.
I am running under HP-UX 11.11 on PA-RISC 2.0, (both 32- and 64-bit builds
tried) built using the native ANSI C compiler. All tests passed when
running 'make check'.
Gerard Krupa MBCS
Marconi Corporation
(See attached file: data-only.sql)(See attached file: full.sql)
Import Notes
Resolved by subject fallback
"Gerard Krupa" <Gerard.Krupa@marconi.com> writes:
I've experienced a similar problem with a data-only dump using 8.0.1's
pg_dump. It seems that the COPYs (or INSERTs) are generated in
alphabetical order of table name, ignoring foreign key contraints and
requiring the output to be post-processed. In a full dump of the database
the constraints are added after the data is inserted so there are no
problems. See the two attached dumps of the same database as an example.
This is not a bug. In a data-only dump pg_dump cannot hope to know what
FK constraints may be applied to the data at load time --- there's no
particularly good reason to assume they are the same as what was in the
source database. So it's your responsibility to order the loading steps
properly. See pg_restore's -l/-L switches.
regards, tom lane
Andreas Lange <anlan@ida.liu.se> writes:
Our upgrade from 7.4.6 to 8.0.1 only had one small glitch. Two tables
got dumped in the wrong order (before their dependecies) and had to get
their contents added manually after the restore. I've atleast isolated
the part where things go wrong.
I don't think this is a pg_dump bug: the problem is you are abusing
check constraints to emulate foreign key constraints. pg_dump has no
way to know what those functions are doing and therefore no way to
realize that the check constraints impose a data load ordering
dependency. Furthermore, the check constraints are fundamentally wrong
anyway because they don't create a two-way relationship --- that is,
altering the referenced tables won't raise an error if the check is now
violated for something in the referencing table.
It would be best to find a way to express these relationships with
ordinary foreign keys. Maybe you could add a column to form_a_int
that is a foreign key reference to both of form_instance.fid and
form_q.fid, for example?
regards, tom lane
On Thu, 2005-03-24 at 17:27 -0500, Tom Lane wrote:
Andreas Lange <anlan@ida.liu.se> writes:
Our upgrade from 7.4.6 to 8.0.1 only had one small glitch. Two tables
got dumped in the wrong order (before their dependecies) and had to get
their contents added manually after the restore. I've atleast isolated
the part where things go wrong.I don't think this is a pg_dump bug: the problem is you are abusing
check constraints to emulate foreign key constraints. pg_dump has no
way to know what those functions are doing and therefore no way to
realize that the check constraints impose a data load ordering
dependency. Furthermore, the check constraints are fundamentally wrong
anyway because they don't create a two-way relationship --- that is,
altering the referenced tables won't raise an error if the check is now
violated for something in the referencing table.
(Sorry for the long delay here!)
Could this be resolved simply by having pg_dump write out all constraint
statements after all insert and trigger statements?
Then no data-order-dependent constraints will be triggered when the dump
is loaded, and even constraints that aren't met when when the dump is
taken won't be triggered when the data is re-loaded.
(I would say that would be a feature not a bug, since as I understand it
the point of pg_dump is to replicate a db setup, with it also being a
separate sanity checker merely a possible benefit. And in any event, if
a few "special" rows don't meet constraints, having had to have been
entered before the constraints were put into place, those rows could
still be restored without problems. Whether that's indicative of poor
schema design is a separate issue.)
Are there any downsides to changing the order of pg_dump output with
respect to constraints? (Versus requiring users to alter their schema
design.)
--
Mark Shewmaker
mark@primefactor.com