Re: pg_restore order and check constraints

On Jun 24, 2013, at 3:47, Moshe Jacobson <moshe(at)neadwerx(dot)com> wrote:

> 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$

Isn't that just an FK constraint on (in_vendor, in_vendor_type) ?
The vendor must be related to a vendor_type somewhere, and since they're in separate tables they're probably even unique. Sounds like an FK constraint would do the job.

Back to the original question; You're hiding for the planner that there is a relation to another table by using plpgsql. That's why pg_dump/restore don't know that these tables need to be restored in a specific order. That said, I was under the impression that constraints are implemented as triggers to begin with, so I'm a bit surprised that the constraint is causing you issues on restore.

As Martin suggests, if you put the entire check constraint as a trigger on the table, then the trigger will be added after the table has been created and copied to (with an ALTER TABLE) and the rows inside don't get checked by the trigger function and thus the "constraint" won't fire too early.

It's a choice between abusing a check constraint for something it wasn't entirely meant for or using a trigger.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.