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