Re: cannot get CREATE TABLE AS to work

On Fri, 9 Mar 2001, Josh Berkus wrote:

> Robert,
>
> > I suspect that the INSERT INTO SELECT in this case will take longer than a
> > CREATE TABLE AS because of the referential integrity check needed on every
> > INSERT (per Tom Lane).
>
> In that case, what about:
>
> a) dropping the referential integrity check;

Unfortunately if he adds it back in with ALTER TABLE, that's going to be
slow as well. I did it in a fashion I felt was cleaner code, but in
practice, I think the implementation's performance is poor enough that
it might be worth doing in the less clean way (running a single select
looking for failing rows when possible on alter table rather than checking
each row -- less clean because it means keeping information on what the
fk check is in multiple places. :( )

> 2) making the referential integrity check deferrable (there's a way to
> do this, it was discussed a couple weeks ago - ask Tom).

Well, you can always add deferrable initially immediate to the constraint
and run a SET CONSTRAINTS ALL DEFERRED, but I'm not sure that'll actually
be much faster, it still does a check per row I believe.

It's hacky, but I'd say, if you don't have other triggers you care about,
twiddle pg_class.reltriggers for the class to 0, do the insert, set it
back to what it was before and then run selects to make sure the data is
valid (ie, would the constraint have failed).

[
assuming one column, something like:

select * from fktable where not exists
(select * from pktable where pktable.pkcol=fktable.fkcol);
]