Thread: Re: [GENERAL] 7.4Beta

On Fri, 15 Aug 2003, Gavin Sherry wrote:

> On Thu, 14 Aug 2003, Stephan Szabo wrote:
>
> > That really needs to be rewritten to do a single check over the table
> > rather than running the constraint for every row.  I keep meaning to get
> > around to it and never actually do. :(  I'm not sure that in practice
> > you'll get a better plan at restore time depending on what the default
> > statistics give you.
>
> Perhaps it would be easier to allow SKIP VALIDATION (or something) with
> ALTER TABLE ADD .... which can set FkConstraint->skip_validation. If we're
> just handling pg_dump output, then presumably the data is already
> validated. On the other handle, it might encourage users to bypass FKs
> when they feel like it...

We've talked about stuff like that in the past, but we seem to generally
get stuck about how to specify it.  If we add it to the alter table add as
an option then we're generating statements that are almost like a standard
sql statement, but not quite, and some people didn't like that. A set
option that only affected ALTER TABLE ADD CONSTRAINT wouldn't be too bad,
but I'd be worried about making it apply to the normal checks as well
(which I believe was suggested last time this came up).  In any case,
making the full checks faster for when you really do care isn't a bad
plan. :)

> We've talked about stuff like that in the past, but we seem to generally
> get stuck about how to specify it.  If we add it to the alter table add as
> an option then we're generating statements that are almost like a standard
> sql statement, but not quite, and some people didn't like that. A set
> option that only affected ALTER TABLE ADD CONSTRAINT wouldn't be too bad,
> but I'd be worried about making it apply to the normal checks as well
> (which I believe was suggested last time this came up).  In any case,
> making the full checks faster for when you really do care isn't a bad
> plan. :)

Or, this:

1. Add the FK to the table BEFORE COPYing data
2. Use the old update blah set reltriggers = 0 trick
3. restore the data
4. Undo step 2

Chris

> We've talked about stuff like that in the past, but we seem to generally
> get stuck about how to specify it.  If we add it to the alter table add as
> an option then we're generating statements that are almost like a standard
> sql statement, but not quite, and some people didn't like that. A set
> option that only affected ALTER TABLE ADD CONSTRAINT wouldn't be too bad,
> but I'd be worried about making it apply to the normal checks as well
> (which I believe was suggested last time this came up).  In any case,
> making the full checks faster for when you really do care isn't a bad
> plan. :)

How about having an 'i'm restoring' SET var:

SET data_restore = true;

Which means (among other things) that FK's aren't checked?

Chris