Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_dump dependencies

pg_dump dependencies

From
Eric E
Date:
Hi all,
    I have a question about pg_dump.  I am backing up a database using
pg_dump in text mode, which works perfectly for me, with one exception.
I have a view vwDependentView which references another view,
vwIndependentView.  However, when I feed my dump script to psql, it is
attempting to make vwDependentView first and failing.
Short of editing the dump script manually, is there any way to force
checking these kinds of dependencies, or alternately manually specify
vwIndependentView to be restored before vwDependentView?  Are there any
plans to implement this kind of behavior?

By the way, I presume this occurs because vwDependentView is ahead of
vwIndependentView alphabetically, so that to force vwDependentView to be
restored later, I could name it vwZDependentView.  Is this correct?

Thanks,

Eric

Re: pg_dump dependencies

From
Michael Fuhr
Date:
On Thu, Jan 06, 2005 at 12:10:26PM -0500, Eric E wrote:

>    I have a question about pg_dump.  I am backing up a database using
> pg_dump in text mode, which works perfectly for me, with one exception.
> I have a view vwDependentView which references another view,
> vwIndependentView.  However, when I feed my dump script to psql, it is
> attempting to make vwDependentView first and failing.

What version of PostgreSQL are you using?  pg_dump tends to improve
with each release; I couldn't duplicate your problem in 8.0.0rc3
or 7.4.6.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: pg_dump dependencies

From
Eric E
Date:
Hi Michael,
    Well, I dumped the database from two different versions of Postgres
(on different machines), using both pg_dump 8.0.0beta2 and pg_dump 7.4.2
(which correspond to the respective server versions).  I only restored
on the 8.0.0beta machine, though.  Would that make a difference?
I will also poke around in my database to see if perhaps something else
is blocking a proper dump.

Thanks,

EE


Michael Fuhr wrote:

>On Thu, Jan 06, 2005 at 12:10:26PM -0500, Eric E wrote:
>
>
>
>>   I have a question about pg_dump.  I am backing up a database using
>>pg_dump in text mode, which works perfectly for me, with one exception.
>>I have a view vwDependentView which references another view,
>>vwIndependentView.  However, when I feed my dump script to psql, it is
>>attempting to make vwDependentView first and failing.
>>
>>
>
>What version of PostgreSQL are you using?  pg_dump tends to improve
>with each release; I couldn't duplicate your problem in 8.0.0rc3
>or 7.4.6.
>
>
>

Re: pg_dump dependencies

From
Tom Lane
Date:
Michael Fuhr <mike@fuhr.org> writes:
> On Thu, Jan 06, 2005 at 12:10:26PM -0500, Eric E wrote:
>> I have a question about pg_dump.  I am backing up a database using
>> pg_dump in text mode, which works perfectly for me, with one exception.
>> I have a view vwDependentView which references another view,
>> vwIndependentView.  However, when I feed my dump script to psql, it is
>> attempting to make vwDependentView first and failing.

> What version of PostgreSQL are you using?  pg_dump tends to improve
> with each release; I couldn't duplicate your problem in 8.0.0rc3
> or 7.4.6.

8.0 is the first version in which pg_dump really is capable of avoiding
this sort of problem.  In older releases the dump order is basically the
same as the order of original creation of the objects --- so you can
easily confuse it by, for example, doing CREATE OR REPLACE VIEW to
modify a view to reference a table that didn't exist when the view was
first defined.

A workaround that may or may not be worse than the disease is to drop
the dependent view completely and then recreate it.  If there's other
stuff that depends on the dependent view this can cascade into a real
PITA :-(

            regards, tom lane