Re: plpgsql constraint checked data fails to restore

On 6/20/2005 1:23 PM, Lee Harr wrote:

>>>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?
>
> You ask some great questions. Thanks.

But not the really important one :-)

>
> I think maybe I just got a little constraint-happy. The way I have
> it, there is definitely a possibility for the other data to change
> out from under the constraint. That can't be good.
>
> Right now, I don't really see another way to check what I wanted
> to check, so I am just going to remove the constraint.
>
> When I get a few minutes, I will post my simplified example and
> maybe someone will have a good idea.

The question I have is how exactly you manage to get the trigger fired
when restoring the dump. By default, the dump created by pg_dump will
create the table, fill in the data and create the trigger(s) only after
that. From that I conclude that you are taking a data-only dump and
restore the schema first either from a text file or a separate pg_dump
schema only.

If you do keep your schema in external ascii files and do data-only
dumps, you have to split the schema into table creation (without
constraints, indexes, etc.) and a second part that adds all the
constraints and indexes after the data is loaded.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #