Re: COPY performance - Mailing list pgsql-general

On Sat, 13 Apr 2002, Nigel J. Andrews wrote:
> On Sat, 13 Apr 2002, Tom Lane wrote:
>
> > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > > On Sat, 13 Apr 2002, Nigel J. Andrews wrote:
> > >> I should have mentioned that I'm doing the copy in to the table in a
> > >> transaction block with all constraints deferred. That should mean it's only at
> > >> the commit stage that foreign key will be checked right?
> >
> > > With the definition shown, I believe your constraint is not deferrable so
> > > setting the constraint mode to deferred won't help. In any case it'd still
> > > need to be saving the information on the triggers to run.
> >
> > In any case the RI trigger firings will be postponed till end of query.
> > I suspect that the memory growth is due to the list of pending trigger
> > firings.  The advice to add the REFERENCES constraint after you've
> > loaded the table seems good to me.
> >
> > Another possibility is that there's some memory leak associated with the
> > txtidx data type; I dunno how thoroughly that type has been tested...
>
> I believe I have seen large memory footprints at other times and I haven't used
> the txtidx type before. However, I will also do a test loading into a table
> with just the standard types. If it turns out to be associated with the new
> column I'll sort out who to email and probably also report on here just so
> people can get 'closure'.
>
> I'll do the test of loading the table without the foreign ket set also, of
> course.

Right, I'm not even going to bother doing the \N test to stick a null in the
final column of the table. Missing the foreign key constraint from the table
definition and leaving the txtidx typed column in does indeed enable the entire
data set to be loaded in a few minutes with the memory footprint staying short
of 6MB, with and without the load being done within a transaction.

So, the slowness and the large memory usage was due to the foreign key. I note
that the SQL reference manual for 7.2.1 says about SET CONSTRAINTS
... 'Currently, only foreign key contraints are affected by this setting. Check
and unique constraints are always effectively initially immediate not
deferrable.'  I see from the CREATE TABLE page that although foreign keys
are the only constraints that currently accept the deferrable/not deferrable
setting that it is the not deferrable setting that is set by default. This was
my problem, I didn't read this page only the SET CONSTRAINTS page and jumped to
the assumption that foreign keys were defaulted to deferrable.

A case of read TFM. Thanks for the help though folks,


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants