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

From: "Garris, Nicole" <Nicole(dot)Garris(at)dof(dot)ca(dot)gov>
To: "'pgsql-admin(at)postgresql(dot)org'" <pgsql-admin(at)postgresql(dot)org>
Subject: pg_dump not in object dependency order for sequences used for pri mary keys
Date: 2005-04-21 15:39:47
Message-ID: DA1D6951A4720F4FBC5BF398E4D5B122280CD6@dofmail.DOF.FINANCE
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Nuno Souto 2005-04-21 15:53:39 FW: PostgreSQL (real and effective user ids must match) error
Previous Message Nigel Bishop 2005-04-21 15:25:44 PG SQL scripts