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

The following bug has been logged online:

Bug reference:      4204
Logged by:          Tomasz Rybak
Email address:      bogomips@post.pl
PostgreSQL version: 8.3.1
Operating system:   Debian Linux
Description:        COPY to table with FK has memory leak
Details:

I tried to use COPY to import 27M rows to table:
CREATE TABLE sputnik.sputnik (
        id INTEGER,
        sequence BIGINT,
        strength INTEGER,
        time TIMESTAMP WITH TIME ZONE,
        tags TEXT ARRAY[1]
);
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 ' ';

When importing on amd64 with !G RAM and 1G swap it takes entire RAM+swap and
gets killed by OOM-killer.
After dividing data into three parts I was able to
import it.
Kernel kills server process, not psql.

On i386 with 512M RAM and !G swap it works, but
takes entire RAM and about 700MB of swap.
Amount of used swap changes, and oscilates between
600M and 900M for almost entire time of import.

So on 32-bit system, where pointers are smaller,
I was able to import, on 64-bit PostreSQL gets killed.

When I dropped FK from sputnik.ccc24, used COPY, and then created FK
everything was correct, and PostgreSQL didn't even touched swap.

In January, on 8.3rc2 I had also problem with the same data set; when I
tried to update:
UPDATE sputnik.ccc24 SET time = to_timestamp(moment);
my PostgreSQL server also was killed by OOM-killer.
Then patch from Tom Lane for ri_FetchConstraintInfo in
src/backend/utils/adt/ri_triggers.c (1.102->1.102) helped.

I think that here situation may be similar.
I even tried to write some patch:
--- src/backend/executor/execMain.c    2008-02-03 12:12:01.000000000 +0100
+++ src/backend/executor/execMain.c    2008-02-03 12:13:09.000000000 +0100
@@ -1905,6 +1905,7 @@
                        return check[i].ccname;
        }

+       FreeExprContext(econtext);
        /* NULL result means no error */
        return NULL;
 }

but after this patch 59 regression tests failed.

But I still think that some code responsible for checking constraints do not
frees usused memory.

"Tomasz Rybak" <bogomips@post.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