Re: Check Constraints and pg_dump

Tom,

I have another instance of a possible function being used as a check constraint: a function that makes sure there is
onerow, and only one row in a table. 
 

At table creation, and the creation of the constraint, there are no rows in the table. So, even if the constraint is a
validone to have, it will fail. 
 

We use check constraint functions in a few places, and they work just fine for us (minus the case of trying to restore
thedatabase from a pg_dump archive). Is it impossible to treat check constraints similar to other constraints and make
themdeferrable, specifically in the restoration from a pg_dump archive? Is there a specific reason check constraints
arehandled differently from other constraints?
 

Jonathan Scott


On Thu, 26 Feb 2004 08:54:59 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Jonathan Scott <jwscott@vanten.com> writes:
> > The functions and tables create just fine, but when it gets to the
> > COPY part of the sql script, it tries to load tables in what really is
> > the wrong order. The check constraint is making sure there is a "plan"
> > before there is a "contract", yet pg_dump is trying to load the
> > contract table before there is anything in the plan table.
> 
> Shouldn't you be using a foreign key for that?
> 
> I don't see any reasonable way that pg_dump can be expected to
> understand that a check constraint expresses a relationship between two
> tables.  The semantics of check constraints aren't right for it anyway.
> 
> All else being equal, I think the tables will be loaded in OID order,
> so a possible workaround is to create the plan table first.  But an
> FK seems like a better answer.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 


-- 
Jonathan Scott, Programmer, Vanten K.K.
jwscott@vanten.com    Tel: 03-5919-0266
http://www.vanten.com    Fax: 03-5919-0267