Thread: Apologies if you don't consider this a bug and I've wasted your time.

Hi,

I have a postgresql database that I have added postgis capabilities to.

When I pg_dump out the database, it outputs all the tables, functions, types
as you'd expect, but it outputs table definitions that reference postgis
functions/types *before* the functions/types themselves.

This means that when I restore the database on a different machine,
the table definitions complain because they don't know about the
types that are referenced further down the file.

I checked to see if I could force the pg_dump to be in a particular order,
but that doesn't seem possible (or I'm just blind :) )

I'm guessing the info is just coming out in OID order?

Of course, it may be an impossible task to do anything about this
and in my case I do have workarounds (not to mention I can edit the
file :) ), but I thought I'd mention it anyway.

Wonderful database by the way!

cheers

steve

Steve Ingram <ingram@samsixedd.com> writes:
> When I pg_dump out the database, it outputs all the tables, functions, types
> as you'd expect, but it outputs table definitions that reference postgis
> functions/types *before* the functions/types themselves.

Yeah, this is pretty likely if you did any ALTER TABLE or similar
commands anywhere along the line.  The dump order is dependent on the
original creation order of the objects (tables, functions, etc) and so
it's pretty easy to break it by adding columns or indexes to
pre-existing tables.

8.0's pg_dump attempts to deal with this by paying attention to
dependencies, but no earlier release does anything at all credible.

FWIW, pg_restore does have options to alter the reload order, which
may be an easier workaround than editing a textual dump.

            regards, tom lane

On Friday 27 August 2004 20:21, Tom Lane wrote:
> Steve Ingram <ingram@samsixedd.com> writes:
> > When I pg_dump out the database, it outputs all the tables, functions,
> > types as you'd expect, but it outputs table definitions that reference
> > postgis functions/types *before* the functions/types themselves.
>
> Yeah, this is pretty likely if you did any ALTER TABLE or similar
> commands anywhere along the line.

Duh! Of course!!

OK, this makes a lot more sense now and now I
don't regard it as a bug ;)  I bet you are relieved :) :)

> The dump order is dependent on the
> original creation order of the objects (tables, functions, etc) and so
> it's pretty easy to break it by adding columns or indexes to
> pre-existing tables.
>

Thats cool; I can make sure (I think) that doesn't happen,
and frankly, if a reload breaks, the fact that I am reloading *at all* is
pretty drastic, so I'll be happy to shift the contents to accommodate
any of the changes you mentioned.

> 8.0's pg_dump attempts to deal with this by paying attention to
> dependencies, but no earlier release does anything at all credible.
>
> FWIW, pg_restore does have options to alter the reload order, which
> may be an easier workaround than editing a textual dump.
>

And of course, I could have read ahead :)

Thanks for the explanation Tom

cheers

steve

>             regards, tom lane