Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    10

    Unanswered: pg_restore/pg_dump order problem

    I have a database with a user defined function which I use in several views.

    My problem is when I use pg_dump -bc -Ft, it puts the definitions for some of the views before the definition of the function. Thus, when I use pg_restore, the database complains that it can't find a definition for the function (becuase it hasn't reached the definition of the funciton).

    pg_dump and pg_restore versions are 7.3.2 and 7.3.4 (depending upon which computer I'm using).

    How do I get pg_dump to kick out the create statements in the right order??

    Thanks!
    Mark

  2. #2
    Join Date
    Aug 2003
    Posts
    32
    I did a wee experiment and found that pg_dump seems to dump views, functions, and tables in the order that they are created. It looks like the IOD may be used to decide the order. That is obviously not the case in your situation. Have your IOD's wrapped around, making the function IOD greater than the views IOD's? Maybe you could try dropping the function and the views manually, then create the function again followed by the views. Once that's done, do a pg_dump and see if they dump in the right order.

  3. #3
    Join Date
    Nov 2003
    Posts
    10
    Aha.

    Interesting. I'm not used to playing with the OIDs.

    I had views defined. Then I thought of a different way to do it and I created the function. When I incorporated the new function into the views, I used the Create or Replace syntax in pgAdmin III, which probably maintain the OID. I'll try dropping and re-creating the views to see if that helps!

    Thanks for the tip!
    Mark

  4. #4
    Join Date
    Nov 2003
    Posts
    10
    BINGO!!

    That was it. Thanks for the pointer.

    Mark

Posting Permissions

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