Re: COPY problem

"Creager, Robert S" <CreagRS(at)LOUISVILLE(dot)STORTEK(dot)COM> writes:
> I'm working with 2.5 million records btw. I've narrowed the situation to
> occur when copying to any one of the 5 referring tables (COPY u FROM stdin).
> The backend process which handles the db connection decides that it needs a
> whole lot of memory, although in a nice controlled manner. The backend
> starts with using 6.5Mb, and at 25000 records copied, it's taken 10Mb and
> has slowed down substantially. Needless to say, this COPY will not finish
> before running out of memory (estimated 300Mb). When executing the COPY to
> the loc table, this problem does not occur. Am I going to have to resort to
> inserts for the referring tables?

It turns out that the main bug here is not that you see a memory leak
for the referring tables, but that you fail to see one for the referred
table :-(. We implement foreign key constraints via AFTER EVENT
triggers, and the current implementation of such triggers requires
saving information about each insert/update/delete event occurring
during the current transaction. So that's where the memory is going.

I noticed that the trigger code was being a little bit stupid about
calculating the amount of memory it needed to allocate, so I modified
that, for a net savings of perhaps a third of the per-tuple memory.
But that's about as much as we can do about the issue for 7.1.

I think the code could be made smarter --- in particular, I doubt that
it's really necessary to save info about INSERT events when you have
only AFTER UPDATE and/or AFTER DELETE triggers. But this seems too
delicate a change to risk making at this point in the 7.1 cycle. We'll
just have to live with it for awhile longer.

In the meantime I'd suggest limiting the number of tuples that you
insert per transaction.

regards, tom lane