Обсуждение: 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@inf.ed.ac.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@inf.ed.ac.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 > >