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

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

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: BUG #4204: COPY to table with FK has memory leak
Date: 2008-05-28 18:22:36
Message-ID: 873ao2qoer.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
[moving to -hackers]

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "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.

Hm, it occurs to me that we could still do a join against the pending event
trigger list... I wonder how feasible it would be to store the pending trigger
event list in a temporary table instead of in ram.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

In response to

Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2008-05-28 20:13:31
Subject: Re: Packages in oracle Style
Previous:From: Gregory StarkDate: 2008-05-28 17:55:27
Subject: Re: Avoiding second heap scan in VACUUM

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-05-28 20:28:27
Subject: Re: BUG #4204: COPY to table with FK has memory leak
Previous:From: Martin DrescherDate: 2008-05-28 16:56:28
Subject: BUG #4206: function xpath gives wrong results

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