Professional Web Applications Themes

pg_dump dependencies - PostgreSQL / PGSQL

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

  1. #1

    Default pg_dump dependencies

    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

    ---------------------------(end of broadcast)---------------------------
    TIP 8: explain yze is your friend

    Eric E Guest

  2. #2

    Default Re: pg_dump dependencies

    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
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Michael Fuhr Guest

  3. #3

    Default Re: pg_dump dependencies

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

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Eric E Guest

  4. #4

    Default Re: pg_dump dependencies

    Michael Fuhr <mikefuhr.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

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Tom Lane Guest

Similar Threads

  1. pg_dump in 7.2.4 with trigger functions
    By Steve Wampler in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: January 31st, 05:58 PM
  2. pg_dump shell script with ~/.pgpass
    By MargaretGillon@chromalloy.com in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: January 27th, 06:24 PM
  3. Bug in pg_dump in 7.4.6?
    By Greg Stark in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: January 12th, 05:34 PM
  4. pg_dump and pgpool
    By Scott Marlowe in forum PostgreSQL / PGSQL
    Replies: 17
    Last Post: December 31st, 06:44 PM
  5. DELETE versus TRUNCATE during pg_dump....
    By Patrick Hatcher in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: December 22nd, 04:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139