Re: plpgsql constraint checked data fails to restore

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/