Hi guys,

I notice that we're still dumping CHECK constraints as part of the CREATE
TABLE statement, and not as an ALTER TABLE statement after the data has been
loaded.

Should we move it to after the data for speed purposes, like we have with
all other constraints?

Chris



Wed, 26 Feb 2003 14:47:33 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
CHECK constraints in pg_dump
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> I notice that we're still dumping CHECK constraints as part of the CREATE
> TABLE statement, and not as an ALTER TABLE statement after the data has been
> loaded.

> Should we move it to after the data for speed purposes, like we have with
> all other constraints?

Why would there be any speed advantage?

			regards, tom lane

Wed, 26 Feb 2003 01:56:48 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: CHECK constraints in pg_dump 

> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > I notice that we're still dumping CHECK constraints as part of the
CREATE
> > TABLE statement, and not as an ALTER TABLE statement after the data has
been
> > loaded.
>
> > Should we move it to after the data for speed purposes, like we have
with
> > all other constraints?
>
> 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?

Chris



Wed, 26 Feb 2003 15:53:52 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: CHECK constraints in pg_dump 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.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.

			regards, tom lane

Wed, 26 Feb 2003 09:54:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: CHECK constraints in pg_dump 
On Wed, 2003-02-26 at 14:54, Tom Lane wrote:
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.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.Elphick@lfix.co.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 


28 Feb 2003 23:14:08 +0000
Oliver Elphick <olly@lfix.co.uk>
Re: CHECK constraints in pg_dump