The function is a little big, so i put it in an enclosure file.
The function write regularly in a file to empty the variable theClob. The fact the written is done all 100000 has no signification, it's just to empty theClob before it's full.
We have a look at the memory and it never decreases.
If you have question about code, tell me ?
This code is correct because it functions over a little example.
Best regards
COFRAMI
Nicolas Giroire
on behalf of AIRBUS France
for In Flight & Ground Information Services - Development
Phone : +33 (0)5 67 19 98 74
Mailto:[EMAIL PROTECTED]
-----Message d'origine-----
De : Sean Davis [mailto:[EMAIL PROTECTED]]
Envoyé : jeudi 31 mars 2005 13:15
À : GIROIRE Nicolas (COFRAMI)
Objet : Re: [GENERAL] plperl doesn't release memory
On Mar 31, 2005, at 1:38 AM, GIROIRE Nicolas (COFRAMI) wrote:
> Hi,
> I work with William.
>
> In fact, we have already done the procedure in pl/pgsql but it is too
> slow and we use array which are native in perl.
> The procedure is recursive, and use request on postgreSQL.
> According to the evolution of memory use, it seems that no memory is
> free. I think that comes from the fact we have a recursive procedure.
>
> The execution of the procedure take 3 hours and finishes already by an
> out of memory.
>
> Can we oblige pl/perl to free memory for variable ?
> Or can we configure postgresql to accept this rise in load ?
> Or another idea ?
>
> When the procedure crash, postgresql use 280 MB of memory and 2 Go of
> virtual memory and on the server we have a message "Windows try to
> increase virtual memory".
>
Perhaps, if the function isn't too big, you could post it so that we
might see what you are trying to do. As other folks have mentioned, as
variables go out of scope, the memory is freed. However, if they don't
go out of scope, they won't be freed until the end of the function. My
concern, like that of others, is that your variables are not going out
of scope (or being undefined explicitly). The only way to know is to
go through the code.
Sean
This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
CREATE OR REPLACE FUNCTION nico.create_xml_file(int4, int4, int4, text, text)
RETURNS int4 AS
$BODY$
use strict;
###############################
# CREATE_XML_FILE #
###############################
my $OEID = 'OID';
my $theClob;
# open xml file result of export
select FILE;
open(FILE, '>>'.$_[4].'\\'.$_[3]);
my @params=($_[0], $_[1], $_[2], 0);
GET_XML_FRAG([EMAIL PROTECTED], \$theClob);
WRITE_CLOB_IN_FILE(\$theClob);
close FILE;
###############################
# WRITE_CLOB_IN_FILE #
###############################
sub WRITE_CLOB_IN_FILE {
my $theClob_ref = shift;
# print in file
print $$theClob_ref;
# empty the clob
$$theClob_ref = '';
}
###############################
# GET_XML_FRAG #
###############################
sub GET_XML_FRAG {
my $params_ref = shift;
my $theClob_ref = shift;
if (($$params_ref[1]%100000)==0){
# write clob in file
WRITE_CLOB_IN_FILE($theClob_ref);
# trace of time
my $time_f = $$params_ref[1]. ' ---> '.localtime(time);
elog NOTICE, $time_f;
}
my $attrQuery = 'select * from nico.XDB_ATTR a1
where a1.doc_id = '.$$params_ref[0].'
and a1.ele_id = '.$$params_ref[1].'
and a1.isremoved = 0
and a1.evolution = (select max(evolution) from
nico.XDB_ATTR a2
where
a2.doc_id='.$$params_ref[0].'
and
a2.ele_id='.$$params_ref[1].'
and
a2.evolution<='.$$params_ref[2].'
and
a2.attr_id=a1.attr_id)';
my $attrTab = spi_exec_query($attrQuery);
my $childQuery = 'select * from nico.XDB_CHILD c1
where c1.doc_id = '.$$params_ref[0].'
and c1.ele_id = '.$$params_ref[1].'
and c1.isremoved = 0
and c1.evolution = (select max(evolution)
from nico.XDB_CHILD c2
where
c2.doc_id='.$$params_ref[0].'
and
c2.ele_id='.$$params_ref[1].'
and
c2.evolution<='.$$params_ref[2].'
and
c2.child_id=c1.child_id
and
c2.child_class=c1.child_class) ORDER BY c1.evolution, c1.indx';
my $childTab = spi_exec_query($childQuery);
elog NOTICE, 'GET_XML_FRAG : -----> docId -> '. $$params_ref[0]
.' | eleId -> '. $$params_ref[1].' | evo -> '.$$params_ref[2].' | indx ->
'.$$params_ref[3].' | nbFils -> '.$childTab->{processed};
# Tag
my $tagQuery = 'select tag from nico.XDB_ELE
where doc_id='.$$params_ref[0].'
and ele_id='.$$params_ref[1].'
and isremoved=0
and evolution = (select MAX(evolution)
from nico.XDB_ELE
where doc_id='.$$params_ref[0].'
and ele_id='.$$params_ref[1].'
and evolution<='.$$params_ref[2].')';
my $tagTab = spi_exec_query($tagQuery);
# Formatting element
$$theClob_ref .= '<'.$tagTab->{rows}[0]->{tag};
#Attributes
foreach my $rn (0 .. $attrTab->{processed} - 1) {
$$theClob_ref .= '
'.$attrTab->{rows}[$rn]->{name}.'="'.$attrTab->{rows}[$rn]->{valu}.'"';
}
# Specific root element processing
if ($$params_ref[1]==1){
$$theClob_ref.= ' AID="'.$OEID.'"';
}
$$theClob_ref .= ' '.$OEID.'="'.$$params_ref[1].'">';
#ordering children
if ($childTab->{processed}>1){
SORT_CHILDREN($childTab);
}
# looking for children
foreach my $i (0 .. $childTab->{processed} - 1) {
# obtain child class
my $childClass = $childTab->{rows}[$i]->{child_class};
if ($childClass==0) {
$$params_ref[1] =
$childTab->{rows}[$i]->{child_id};
$$params_ref[3] = $childTab->{rows}[$i]->{indx};
GET_XML_FRAG($params_ref, $theClob_ref);
}
elsif ($childClass==2) {
my $valueQuery = 'select value from
nico.XDB_STR s1
where s1.doc_id = '.$$params_ref[0].'
and s1.cdata_id =
'.$childTab->{rows}[$i]->{child_id}.'
and s1.isremoved = 0
and s1.evolution = (select
MAX(evolution)
from nico.XDB_STR s2
where
s2.doc_id='.$$params_ref[0].'
and s2.cdata_id =
'.$childTab->{rows}[$i]->{child_id}.'
and
s2.evolution<='.$$params_ref[2].')';
my $valueTab = spi_exec_query($valueQuery);
$$theClob_ref .= $valueTab->{rows}[0]->{value};
}
elsif ($childClass==3) {
my $valueQuery = 'select value from
nico.XDB_TEXT t1
where t1.doc_id = '.$$params_ref[0].'
and t1.cdata_id =
'.$childTab->{rows}[$i]->{child_id}.'
and t1.isremoved = 0
and t1.evolution = (select
MAX(evolution)
from nico.XDB_TEXT t2
where
t2.doc_id='.$$params_ref[0].'
and t2.cdata_id =
'.$childTab->{rows}[$i]->{child_id}.'
and
t2.evolution<='.$$params_ref[2].')';
my $valueTab = spi_exec_query($valueQuery);
$$theClob_ref .= $valueTab->{rows}[0]->{value};
}
elsif ($childClass==1) {
my $valueQuery = 'select target, value from
nico.XDB_PI p1
where p1.doc_id =
'.$$params_ref[0].'
and p1.pi_id =
'.$childTab->{rows}[$i]->{child_id}.'
and p1.isremoved = 0
and p1.evolution =
(select MAX(evolution)
from
nico.XDB_PI p2
where
p2.doc_id='.$$params_ref[0].'
and
p2.pi_id = '.$childTab->{rows}[$i]->{child_id}.'
and
p2.evolution<='.$$params_ref[2].')';
my $valueTab = spi_exec_query($valueQuery);
$$theClob_ref .=
'<?'.$valueTab->{rows}[0]->{target}.' '.$valueTab->{rows}[0]->{value}.'?>';
}
}
$$theClob_ref .= '</'. $tagTab->{rows}[0]->{tag} .'>';
}
###############################
# SORT_CHILDREN #
###############################
sub SORT_CHILDREN {
my ($children_ref) = @_;
my $nbChildren = $children_ref->{processed} - 1;
foreach my $i (0 .. $nbChildren) {
my $tmp_row = @{$children_ref->{rows}}[$i];
my $indx = $tmp_row->{indx};
if ($indx < $i) {
# on déplace l'élément vers sa place par
rapport à son index
splice(@{$children_ref->{rows}}, $indx-1, 0,
splice(@{$children_ref->{rows}}, $i, 1));
}
}
}
$BODY$
LANGUAGE 'plperl' VOLATILE;
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
