Thread: pg_dump not in object dependency order for sequences used for pri mary keys

pg_dump not in object dependency order for sequences used for pri mary keys

From
"Garris, Nicole"
Date:

I am running PostgreSQL 8.0.1 on Max OS X. Rather than using the serial data type for a table’s primary key, we use integer and default it to the nexval of a sequence with the name of <tablename>_seq (this syntax seems to work best with Apple’s WebObjects Enterprise Object Modeler). Therefore, the table depends on the sequence, and in a reload of the database, the sequence must be created first. However, pg_dump lists the “create sequence” after the “create table”. Of course I cannot use the pg_dump file as-is to reload the database, it fails on the “create table” statement when referencing the (as-yet) non-existent sequence.

 

It would be nice if pg_dump understood the dependency and listed the “create sequence” before the “create table”. (If there is some way to get it to do this, please let me know.)

 

In the meantime, I see from the manual that pg_restore can be made to produce an archive.list which is a table of contents for the dump file. I can then reorder the archive.list and feed it to pg_restore to force a restore in the correct order.

 

If this has already been discussed, please point me to the list, year and month and I’ll go to the list archives and learn there. Thanks!!

"Garris, Nicole" <Nicole.Garris@dof.ca.gov> writes:
> I am running PostgreSQL 8.0.1 on Max OS X. Rather than using the serial data
> type for a table's primary key, we use integer and default it to the nexval
> of a sequence with the name of <tablename>_seq (this syntax seems to work
> best with Apple's WebObjects Enterprise Object Modeler). Therefore, the
> table depends on the sequence, and in a reload of the database, the sequence
> must be created first. However, pg_dump lists the "create sequence" after
> the "create table". Of course I cannot use the pg_dump file as-is to reload
> the database, it fails on the "create table" statement when referencing the
> (as-yet) non-existent sequence.

How so?  The nextval only references the sequence as a string; it's not
going to trigger any failure at create time.  Please show a specific
test case instead of assuming you know what the problem is.

            regards, tom lane