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