Re: pg_restore order and check constraints

On Sun, Jun 23, 2013 at 5:04 PM, Martín Marqués <martin(at)2ndquadrant(dot)com>wrote:

> Is it possible to see the function?

Yes -- It checks that the given vendor has the given vendor_type by calling
fn_get_vendor_types_by_vendor(), which gets its data from another table,
tb_vendor_vendor_type (a join table between tb_vendor and tb_vendor_type):

CREATE OR REPLACE FUNCTION public.fn_vendor_has_vendor_type(in_vendor
integer, in_vendor_type integer)
RETURNS boolean
LANGUAGE plpgsql
STABLE STRICT
AS $function$
BEGIN
IF in_vendor_type IN( SELECT fn_get_vendor_types_by_vendor( in_vendor )
)THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END
$function$

I've installed this function on tb_project_vendor, which has a vendor_type
column:

ALTER TABLE tb_project_vendor
ADD CONSTRAINT "ck_project_vendor_has_vendor_type"
CHECK( fn_vendor_has_vendor_type( vendor, vendor_type ) );

So when the data for tb_project_vendor is restored before the data for
tb_vendor_vendor_type, I get errors on restore.

I know that this is stretching the limit of what a check constraint is
>
> meant to be, but is there a way, short of editing the pg_restore list
>> manually every time, to guarantee that the table used for validation is
>> populated before the table with the data being validated?
>>
>
> What for? If the dumps actually are taken without contraints, data
> restored (much faster as no constraints have to be checked, and just then
> constraints are added via ALTER TABLE.

So you suggest I use a trigger instead of a constraint?

Thanks

--
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com | www.neadwerx.com

"Quality is not an act, it is a habit." -- Aristotle