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

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

From: Decibel! <decibel(at)decibel(dot)org>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: BUG #4204: COPY to table with FK has memory leak
Date: 2008-06-03 20:43:14
Message-ID: 64F489EE-978A-4766-BE42-1D02AA4CEC1F@decibel.org (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
On May 28, 2008, at 1:22 PM, Gregory Stark wrote:
> "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.


Related to that, I really wish that our statement-level triggers  
provided NEW and OLD recordsets like some other databases do. That  
would allow for RI triggers to be done on a per-statement basis, and  
they could aggregate keys to be checked.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828


In response to

pgsql-hackers by date

Next:From: Jeff DavisDate: 2008-06-03 20:53:56
Subject: Re: Case-Insensitve Text Comparison
Previous:From: Zdenek KotalaDate: 2008-06-03 20:18:32
Subject: Re: Case-Insensitve Text Comparison

pgsql-bugs by date

Next:From: Michael MeskesDate: 2008-06-04 12:44:11
Subject: Re: *****SPAM***** BUG #4168: ECPG refuses datestyle SQL
Previous:From: Vivek GuptaDate: 2008-06-03 10:42:01
Subject: Re: BUG #3995: pqSocketCheck doesn't return

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