On Mon, Jun 20, 2005 at 05:49:05AM +0430, Lee Harr wrote: > > I have a database running 8.0.1 > > One of the tables uses a plpgsql function as a > check constraint. There is data in the table that > passed the constraint. > > The problem comes when trying to restore the > database using a file created by pg_dump. > > Some of the data required by the check function > is being restored after the data being checked > by the function and so it all fails the constraint. Are you saying that the check function perform queries against other data? That might not be a good idea -- consider what happens if the data changes: would changes invalidate records that had previously passed the check but that wouldn't pass now if they were checked again? What steps are you taking to ensure integrity not only when a record is inserted, but also when the data the record depends on is changed? > I have a small (200 lines) dump file which shows > the problem if that will help. > > Is there a way to ensure that the checked data > gets listed last in the dump file? Some other > way? It sounds like you've introduced a dependency that PostgreSQL doesn't know about, so pg_dump doesn't know that certain data needs to be restored first. Perhaps you could use the custom dump format and create an automated mechanism to reorder objects at restore time. Another possibility might be to muck around with naming in an attempt to get certain objects dumped first, although that might be subject to breaking again in the future. Yet another possibility would be to perform multiple dumps using options like --table or --schema and make sure you restore them in the proper order. Maybe somebody else can propose other possibilities. Perhaps you should rethink using a check constraint that depends on other data -- what's that check do? Is it something that a foreign key constraint couldn't handle? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
