Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Tomasz Rybak" <bogomips(at)post(dot)pl>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4204: COPY to table with FK has memory leak
Date: 2008-05-27 21:36:39
Message-ID: 13982.1211924199@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
"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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-05-27 21:43:12
Subject: Re: Outer joins and equivalence
Previous:From: Bruce MomjianDate: 2008-05-27 21:31:52
Subject: Re: ERRORDATA_STACK_SIZE panic crashes on Windows

pgsql-bugs by date

Next:From: Bomken BasarDate: 2008-05-27 22:32:58
Subject: BUG #4205: Unable to restore database
Previous:From: Tom LaneDate: 2008-05-27 20:24:05
Subject: Re: BUG #4203: perform dblink() in begin/exception returns wrong SQLSTATE code

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group