Skip site navigation (1) Skip section navigation (2)

Re: CHECK constraints in pg_dump

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>,Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: CHECK constraints in pg_dump
Date: 2003-02-28 23:14:08
Message-ID: 1046474048.1700.8.camel@linda.lfix.co.uk (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, 2003-02-26 at 14:54, Tom Lane wrote:
> "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> writes:
> >> Why would there be any speed advantage?
> 
> > Is it not faster to add it when all the data is there, rather than
> > evaluating it as each row is inserted, like indexes?
> 
> I don't see why.  There are good algorithmic reasons why bulk-loading
> an index is faster than retail insertions --- mainly that btree goes
> out of its way to make it so, with a special code path.  But I see
> no reason why checking a constraint expression is going to be any
> faster as a post-pass than when done while loading the data.  If
> anything, I'd guess it to be slower because you have to re-read the
> table.

One reason for delaying constraint checks until after all data is loaded
is that any CHECK constraints against other tables must be hidden in
functions.  For example, we cannot say:

   CHECK (col1 > othertable.col2 WHERE id = othertable.id).  

Since such checks are hidden, I suppose it will not be possible to
arrange the order of loading in pg_dump to ensure that such checks
succeed; therefore it would be better for any check constraint involving
a function to be delayed till after all data is loaded.

-- 
Oliver Elphick                                Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "These things have I written unto you that believe on 
      the name of the Son of God; that ye may know that ye 
      have eternal life, and that ye may believe on the name
      of the Son of God."        I John 5:13 


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2003-02-28 23:54:13
Subject: CLUSTER loses nulls (was Re: [ADMIN] Still a bug in the VACUUM)
Previous:From: Stephan SzaboDate: 2003-02-28 21:58:49
Subject: Foreign key quandries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group