Re: pg_restore order and check constraints

[Moshe Jacobson <moshe@xxxxxxxxxxxx>]

On Sun, Jun 23, 2013 at 5:04 PM, Martín Marqués <martin@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxx | www.neadwerx.com

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