Search Postgresql Archives

Re: pg_restore order and check constraints

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux