Thread: 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.

On Tuesday 23 September 2008, William Garrison <postgres@mobydisk.com>
wrote:
> 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.

AFAIK, the dumps created by pg_dump create all the constraints after the
table data is all loaded - there are no foreign keys in place when the data
is restored, so conflicts are not possible.

You might need to elaborate on how you're restoring this database.

--
Alan

Alan Hodgson wrote:
> On Tuesday 23 September 2008, William Garrison <postgres@mobydisk.com>
> wrote:
>> 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.
>
> AFAIK, the dumps created by pg_dump create all the constraints after the
> table data is all loaded - there are no foreign keys in place when the data
> is restored, so conflicts are not possible.

It's a data only dump; the constraints already exist as part of the
schema, and the SQL generated by pg_dump won't disable them for the load
(as that might permit invalid data to be loaded; there's no guarantee
that the constraints currently defined are the same ones as were present
when the dump was taken).

If you really want to load the data, at present you either need to load
the tables in the right order, either by manually chopping and changing
the dump or by using a -Fc dump and pg_restore, or you need to disable
triggers before the load and accept the risk of invalid data being loaded.

IIRC a patch was circulating (maybe applied to 8.4?) that tries to map
foreign-key relationships and where possible dump data in dependency
order so that data-only dumps without circular foreign key references
will restore correctly with no special user action.

--
Craig Ringer

Craig Ringer <craig@postnewspapers.com.au> writes:
> IIRC a patch was circulating (maybe applied to 8.4?) that tries to map
> foreign-key relationships and where possible dump data in dependency
> order so that data-only dumps without circular foreign key references
> will restore correctly with no special user action.

Yeah.  Historically pg_dump has not worried about foreign keys at all
during data-only dumps.  As of CVS HEAD there is some code in there that
will sort the tables according to foreign key constraints, although it
is possible to have circular constraints or self-referential constraints
that defeat this.

            regards, tom lane

On 2008-09-23 19:03, William Garrison wrote:
> 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.

It is not fixed and is sometimes not possible to fix for data only dumps.

> 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.

Much easier:

1. Create a schema with all constraints etc.
2. Dump this empty database with pg_dump with default options to
empty_database.sql.
3. Split empty_database.sql file to 2 files - tables.sql and
constraints.sql - all constraints will be at the end of empty_database.sql
4. drop database, create empty one, import tables.sql, import your
data-only backup, import constraints.sql.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh

Oh good.  That's almost what I did:

I made a schema only dump, then a data only dump with --inserts.  Then I commented-out the constraints from the schema.  Then I loaded the data.  Unfortunately, the INSERT statements take 24 hours instead of 4 hours to restore.  When you say the "default" options - what format does that write?  Should I have used -Fp to make a plain text backup but not --inserts?  Then it would be doing a COPY instead of an INSERT and maybe that would be faster.  Oh well.

Tomasz Ostrowski wrote:

On 2008-09-23 19:03, William Garrison wrote: 

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.   

It is not fixed and is sometimes not possible to fix for data only dumps.
 

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.   

Much easier:

1. Create a schema with all constraints etc.
2. Dump this empty database with pg_dump with default options to
empty_database.sql.
3. Split empty_database.sql file to 2 files - tables.sql and
constraints.sql - all constraints will be at the end of empty_database.sql
4. drop database, create empty one, import tables.sql, import your
data-only backup, import constraints.sql.

Regards
Tometzky 

On 2008-09-24 18:01, William Garrison wrote:

> Then I commented-out the constraints from the schema.  Then I loaded
> the data.

Don't forget to restore these constraints back after loading data.

> I made a schema only dump, then a data only dump with --inserts. (...)
> Unfortunately, the INSERT statements take 24 hours instead of 4 hours to
> restore.  When you say the "default" options - what format does that
> write?  Should I have used -Fp to make a plain text backup but not
> --inserts?  Then it would be doing a COPY instead of an INSERT and maybe
> that would be faster.

Indeed.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh