Skip site navigation (1) Skip section navigation (2)

pg_dump ordering in 8.1.3

From: Sue Fitt <sue(at)inf(dot)ed(dot)ac(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: pg_dump ordering in 8.1.3
Date: 2006-07-27 09:54:10
Message-ID: 44C88D42.6060506@inf.ed.ac.uk (view raw or flat)
Thread:
Lists: pgsql-novice
Hi all,

I wrote a while ago about ordering problems in pg_dump, which turned out 
to be due to using version 7.4.

I'm now using 8.1.3 and have another, more subtle, ordering problem in 
pg_dump....

I am working on a database of gradually increasing complexity, with 
several tables connected by foreign keys. Validity of some fields is 
also checked by triggers etc, some in plpgsql and some in plperl. I now 
have the situation where I have a table X with a field which uses a 
domain 'word'. The characters possible in this domain (a-z plus a number 
of others) are listed in table Y. I then have a plperl function 
check_word which uses a query to get the characters from table Y, and 
compiles these characters into a variable which is used to check the 
contents of the domain. These characters are used elsewhere, and 
occasionally updated, which is why they are read from a table rather 
than hardwired into the function.

So, we have
    Table Y: list of characters
    Domain word
    Function check_word: checks that 'word' contains only characters 
from Table Y
    Table X: data set to domain 'word'
   
I hope that's clear enough. But, in the dump file, the order is
    Function check_word
    Domain word
    Table X: data set to domain 'word'
    Table Y: list of characters

So when I try to recreate the database from the dump file it fails, 
because Table Y is queried by the function, which ends up with an empty 
variable.

How can I get round this? The dump file is too big to manually reorder. 
Even if I can do this, or if I can specify a way to load certain tables 
first, this seems an error-prone way of working - the database is under 
construction and the same problem may arise elsewhere. I do want to find 
a fairly fool-proof way of restoring from back-ups so I can have a 
simple way of passing a copy of the database on to other people. Is 
there a way to do this?

Thanks,
Sue Fitt

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2006-07-27 12:39:35
Subject: Re: What order in primary key definition ?
Previous:From: Brendon GleesonDate: 2006-07-27 08:00:20
Subject: query help

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group