Wes wrote:
You could create the fk-constraints _first_, then disable them, load
the data, reindex, and reenable them afterwards.
pg_dump/pg_restore can enable and disable fk-constraints before restoring
the data, I believe. It does so by tweaking the system catalogs.
Are referring to '--disable-triggers' on pg_dump? Will this work for
foreign key constraints? The doc talks about triggers, but doesn't say
anything about FK constraints (are these implemented as triggers?) I don't
use pg_restore, just psql.
Yes, I was referring to "--disable-triggers". I always assumes that it
disables FK-Constraints as well as triggers, but now that you ask I
realize that I might have never actually tried that ;-)
But FK-Constraints _are_ implemented as triggers internally, so I guess
it should work.
The only problem I can see is that creating the fk-constraints might create
some indices too. But maybe you can manually drop those indices afterwards - I
don't know if the fk really _depends_ on the index, or if it creates it only
for convenience.
I don't see any indexes being added to the table beyond what I add, and
those added as a primary key constraint. Currently, pg_dump outputs the FK
constraints after the indexes are built, as the last steps. If I try to add
the FK constraints after loading the database definitions, but without any
indexes, I'm not sure what would happen.
Hm.. it i tried it out, and came to the conclusion that my approach
doesn't work :-(
You can only create an FK if the fields you are referencing in the
foreign table form a PK there. And creating a PK implicitly creates an
index, which you can't drop without dropping the PK :-(
So unless you find a way to force postgres to ignore the index when
inserting data, my suggestion won't work :-(
greetings, Florian Pflug
