Thread: pg_restore order and check constraints

El 23/06/13 13:34, Moshe Jacobson escribió:
> Hi,
>
> I recently added a check constraint onto a table in my database, that
> uses a stored procedure to check one of the inserted columns against the
> data of another table.

Is it possible to see the function?

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

> Right now it is restoring out of order, and the table is not getting
> populated correctly.

Why not?

> if I have to rewrite as a trigger, I will do that, but I like the check
> constraint because it checks all of the entries when it's applied. Any
> suggestions?

We'd need to see how the constraint is added, and the function you say
is used for checking the constrants.

--
Martín Marqués                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Jun 24, 2013, at 3:47, Moshe Jacobson <moshe@neadwerx.com> wrote:

> On Sun, Jun 23, 2013 at 5:04 PM, Martín Marqués <martin@2ndquadrant.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
getsits 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
wasunder the impression that constraints are implemented as triggers to begin with, so I'm a bit surprised that the
constraintis 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
afterthe table has been created and copied to (with an ALTER TABLE) and the rows inside don't get checked by the
triggerfunction 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.