From: Jon Clements <jon.clements@trgstrata.co.uk>
To: "pgsql-interfaces@postgresql.org" <pgsql-interfaces@postgresql.org>
Date: Tue, 08 Nov 2005 14:21:01 +0000
Subject: PLPythonU & Out of Memory - Importing Query
Message-ID: <4c05f1b439ce248766e7cd5375a83ddc@readgroup.co.uk>
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable


Hi=20there,

I=20am=20currently=20experimenting=20using=20plpythonu=20with=20postgresql=
=208.0=20for=20Win32.=20It's=20basically=20a=20quick=20script=20that=20imp=
orts=20data=20from=20CSV=20files,=20but=20does=20some=20quite=20complicate=
d=20data=20lookups=20and=20selections.=20The=20area=20in=20which=20I'm=20s=
omewhat=20confunded=20is=20memory=20usage.=20The=20process=20successfully=20=
runs,=20but=20keeps=20climbing=20in=20memory=20usage=20relentlessly,=20suc=
cessfully=20importing=20about=20200k=20records,=20before=20the=20memory=20=
usage=20of=20postgres=20soars=20to=202gb=20and=20of=20course,=20shortly=20=
after=20that,=20grinds=20to=20a=20halt=20with=20a=20"Out=20of=20Memory"=20=
error.=20

I'm=20not=20deliberately=20storing=20anything=20in=20the=20SD/GD=20diction=
aries,=20and=20am=20not=20dealing=20with=20triggers...

create=20function=20blah(text)=20returns=20int8=20as=20
$$
#=20Initialisation=20of=20plans
myplan=20=3D=20plpy.prepare('insert=20into=20tablename=20(var1,var2)=20val=
ues($1,$2)',=20['text','text']=20)
#=20Setup=20external=20CSV=20data=20source
#=20For=20each=20record,=20that=20meets=20certain=20critera,=20execute=20i=
nsert...
for=20rec=20in=20dsource:=20plpy.execute(myplan,=20[Value1,=20Value2]=20)

#=20Finishing=20stuff
return=20some_meaningful_value
$$=20
LANGUAGE=20PLPYTHONU;

Given=20I'm=20importing=20about=20250=20million=20records=20and=20only=20w=
ant=20to=20end=20up=20with=20about=204=20million,=20is
1)=20This=20possible=20using=20the=20above?
2)=20Better=20suited=20to=20something=20else=20(I've=20looked=20at=20COPY=20=
but=20that=20would=20require=20the=20entire=20table=20be=20uploaded=20firs=
t,=20then=20filtered=20and=20I'd=20like=20to=20avoid=20that=20if=20necessa=
ry,=20or=20thinking=20about=20it,=20I=20spose=20a=20trigger=20could=20be=20=
written=20that=20responded=20on=20the=20copy?).=20Also=20the=20other=20thi=
ng=20is=20COPY=20is=20only=20applicable=20to=20simple=20text=20files,=20wh=
ile=20I=20want=20this=20import=20script=20to=20be=20generic=20from=20whate=
ver=20datasource=20it=20may=20be=20importing=20from=20(an=20ODBC/DBF/Berke=
ley=20DB=20format=20etc...)

Anyhow,=20thanks=20in=20advance=20for=20any=20help.
Any=20Qs,=20please=20gimme=20a=20yell.

Regards,

Jon.


________________________________________________________________________
This=20e-mail=20has=20been=20scanned=20for=20all=20viruses=20by=20Star.=20=
The
service=20is=20powered=20by=20MessageLabs.=20For=20more=20information=20on=
=20a=20proactive
anti-virus=20service=20working=20around=20the=20clock,=20around=20the=20gl=
obe,=20visit:
http://www.star.net.uk
________________________________________________________________________