Re: pg_dump ordering in 8.1.3

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

Sue Fitt <sue(at)inf(dot)ed(dot)ac(dot)uk> writes:
> 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.

The function is called by a check constraint I take it? We really don't
support check constraints that look at anything except the current row
of the current table. Anything else introduces dependencies that the
system does not know about and can't promise to honor, as you're finding
out :-(. An example of the kind of problem you'll face is that changes
to the content of table Y will not result in rechecking the constraints
on other tables, even though changing Y might've caused those
constraints to fail.

I think you'd be best off hard-wiring the list of allowed characters
into a check constraint associated with the domain 'word', eg

create domain word as text
check (value ~ '^[a-zA-Z0-9_]+$');

The separate table listing the allowed characters might be good for some
things, but not this.

regards, tom lane

Yes it is done using a check constraint. I'm reluctant to hardwire
though as I have more complicated examples as well, using e.g. further
columns in the table of characters to subdivide the characters and
perform parse checks dependent on the type of character. I'll have to
look at this some more.

Sue

Tom Lane wrote:
> Sue Fitt <sue(at)inf(dot)ed(dot)ac(dot)uk> writes:
>
>> 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.
>>
>
> The function is called by a check constraint I take it? We really don't
> support check constraints that look at anything except the current row
> of the current table. Anything else introduces dependencies that the
> system does not know about and can't promise to honor, as you're finding
> out :-(. An example of the kind of problem you'll face is that changes
> to the content of table Y will not result in rechecking the constraints
> on other tables, even though changing Y might've caused those
> constraints to fail.
>
> I think you'd be best off hard-wiring the list of allowed characters
> into a check constraint associated with the domain 'word', eg
>
> create domain word as text
> check (value ~ '^[a-zA-Z0-9_]+$');
>
> The separate table listing the allowed characters might be good for some
> things, but not this.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>