Tuesday, September 23, 2008

[GENERAL] pg_dump | pg_sql: insert commands and foreign key constraints

I have several .SQL files created from pg_dump, and I find that when I
feed them into psql that I get tons of foreign key errors because the
INSERT statements in the dump are not in the correct order. After
reading the docs, mailing lists, and googling, I see posts saying this
problem was fixed back in the 7.x days. I'm using postgres 8.2.9. This
database doesn't do any "unusual" foreign key constraints like check
constraints, functions in the constraints, or circular foreign keys.
From the looks of the SQL dump, it is just simply in the wrong order
(it might even be alphabetical... the first table starts with c. But I
haven't looked at the entire 26GB dump to see if that is the case).

Since I did a data only dump, I think my only option is to create the
schema, manually disable all the constraints, then restore, then
re-enable the constraints. I'm looking for 2 things:

1) other workarounds
2) someone else who can confirm that this bug is either fixed, or not
fixed. If it is supposedly fixed, then I guess I need to make a smaller
version of my database to demonstrate the problem.

Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:

No comments: