Re: BUG #4204: COPY to table with FK has memory leak

"Tomasz Rybak" <bogomips(at)post(dot)pl> writes:
> I tried to use COPY to import 27M rows to table:
> CREATE TABLE sputnik.ccc24 (
> station CHARACTER(4) NOT NULL REFERENCES sputnik.station24 (id),
> moment INTEGER NOT NULL,
> flags INTEGER NOT NULL
> ) INHERITS (sputnik.sputnik);
> COPY sputnik.ccc24(id, moment, station, strength, sequence, flags)
> FROM '/tmp/24c3' WITH DELIMITER AS ' ';

This is expected to take lots of memory because each row-requiring-check
generates an entry in the pending trigger event list. Even if you had
not exhausted memory, the actual execution of the retail checks would
have taken an unreasonable amount of time. The recommended way to do
this sort of thing is to add the REFERENCES constraint *after* you load
all the data; that'll be a lot faster in most cases because the checks
are done "in bulk" using a JOIN rather than one-at-a-time.

regards, tom lane