Thread: BUG #6061: Progresql.exe memory usage using HOLD cursor.
The following bug has been logged online:
Bug reference: 6061
Logged by: Yann
Email address: yann.delorme@esker.fr
PostgreSQL version: 9.0.4
Operating system: Windows 2008 R2
Description: Progresql.exe memory usage using HOLD cursor.
Details:
Hello,
I use POSTGRESQL 9.0.4 (64bits on windws 2008R2). The code seems to be the
same in 9.1
I execute a query with a « BINARY CURSOR WITH HOLD FOR » cursor.
The resultset contains 20.000 rows, the row size is 20 KB. I fetch result
line per line.
The issue is that in this case all rows are store in memory instead of file
in the process postgresql.exe
I think the issue is in the file tuplestore.c.
When a tuple is added the function static void
tuplestore_puttuple_common(Tuplestorestate *state, void *tuple), USEMEM is
not called with tuple size.
In my postgresql.conf, memory available is 1MB, so to reach the status
TSS_WRITEFILE, the memory tupleStore accept 256.000 rows.
In my test postgresql.exe need more than 400MB to store the resultset, in my
opinion it should use a file to store the result.
I think that, after adding the tuple in the array, a call to USEMEM should
be done.
Can you confirm that it is an issue ?
Regards.
static void
tuplestore_puttuple_common(Tuplestorestate *state, void *tuple)
{
TSReadPointer *readptr;
int i;
ResourceOwner oldowner;
switch (state->status)
{
case TSS_INMEM:
â¦
/* Stash the tuple in the
in-memory array */
state->memtuples[state->memtupcount++] = tuple;
#################################################
#################################################
########## Call USEMEM with the tuple size.
#################################################
#################################################
/*
* Done if we still fit in
available memory and have array slots.
*/
if (state->memtupcount <
state->memtupsize && !LACKMEM(state))
return;
/*
* Nope; time to switch to
tape-based operation. Make sure that
* the temp file(s) are
created in suitable temp tablespaces.
*/
PrepareTempTablespaces();
/* associate the file with
the store's resource owner */
oldowner =
CurrentResourceOwner;
CurrentResourceOwner =
state->resowner;
state->myfile =
BufFileCreateTemp(state->interXact);
CurrentResourceOwner =
oldowner;
/*
* Freeze the decision about
whether trailing length words will be
* used. We can't change this
choice once data is on tape, even
* though callers might drop
the requirement.
*/
state->backward =
(state->eflags & EXEC_FLAG_BACKWARD) != 0;
state->status =
TSS_WRITEFILE;
dumptuples(state);
break;
case TSS_WRITEFILE:
/*
* Update read pointers as
needed; see API spec above. Note:
* BufFileTell is quite cheap,
so not worth trying to avoid
* multiple calls.
*/
readptr = state->readptrs;
for (i = 0; i <
state->readptrcount; readptr++, i++)
{
if
(readptr->eof_reached && i != state->activeptr)
{
readptr->eof_reached = false;
BufFileTell(state->myfile,
&readptr->file,
&readptr->offset);
}
}
WRITETUP(state, tuple);
break;
case TSS_READFILE:
/*
* Switch from reading to
writing.
*/
if
(!state->readptrs[state->activeptr].eof_reached)
BufFileTell(state->myfile,
&state->readptrs[state->activeptr].file,
&state->readptrs[state->activeptr].offset);
if
(BufFileSeek(state->myfile,
state->writepos_file,
state->writepos_offset,
SEEK_SET) != 0)
elog(ERROR,
"tuplestore seek to EOF failed");
state->status =
TSS_WRITEFILE;
/*
* Update read pointers as
needed; see API spec above.
*/
readptr = state->readptrs;
for (i = 0; i <
state->readptrcount; readptr++, i++)
{
if
(readptr->eof_reached && i != state->activeptr)
{
readptr->eof_reached = false;
readptr->file = state->writepos_file;
readptr->offset = state->writepos_offset;
}
}
WRITETUP(state, tuple);
break;
default:
elog(ERROR, "invalid
tuplestore state");
break;
}
}
Yann.
"Yann" <yann.delorme@esker.fr> writes:
> The issue is that in this case all rows are store in memory instead of file
> in the process postgresql.exe
> I think the issue is in the file tuplestore.c.
> When a tuple is added the function static void
> tuplestore_puttuple_common(Tuplestorestate *state, void *tuple), USEMEM is
> not called with tuple size.
Hmm ... yeah, I think there's a leak there.
> I think that, after adding the tuple in the array, a call to USEMEM should
> be done.
No, the callers of tuplestore_puttuple_common are supposed to do that.
But it looks like tuplestore_putvalues() forgot to do so. So data loads
that go through that particular API would miss incrementing the space
counter.
regards, tom lane
Thanks Do you think that it will be fix in future release 9.1 ?=20 Regards,=20 Yann Yann Delorme Senior Software Engineer / Senior Software Engineer Esker SA T=C3=A9l : +33 (0)4 72 83 46 46 Fax : + 33 (0)4 72 83 46 40 mailto:Yann.Delorme@esker.fr http://www.esker.fr/ =E2=96=A0 http://www.flydoc.fr/ CONFIDENTIALITE : Ce message et les =C3=A9ventuelles pi=C3=A8ces jointes so= nt confidentiels. Si vous n'=C3=AAtes pas dans la liste des destinataires, = veuillez informer l'exp=C3=A9diteur imm=C3=A9diatement et ne pas divulguer = le contenu =C3=A0 une tierce personne. Les id=C3=A9es et opinions pr=C3=A9s= ent=C3=A9es dans ce message sont celles de son auteur, et ne repr=C3=A9sent= ent pas n=C3=A9cessairement celles de la soci=C3=A9t=C3=A9. Par ailleurs et= malgr=C3=A9 toutes les pr=C3=A9cautions prises pour =C3=A9viter la pr=C3= =A9sence de virus dans nos envois, nous vous recommandons de prendre, de vo= tre c=C3=B4t=C3=A9, les mesures permettant d'assurer la non-introduction de= virus dans votre syst=C3=A8me informatique. La soci=C3=A9t=C3=A9 ne saurai= t =C3=AAtre tenue pour responsable de tout dommage caus=C3=A9 par la pr=C3= =A9sence d'un virus dans ce message. __________ -----Message d'origine----- De : Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Envoy=C3=A9 : mercredi 15 juin 2011 18:43 =C3=80 : Delorme, Yann Cc : pgsql-bugs@postgresql.org Objet : Re: [BUGS] BUG #6061: Progresql.exe memory usage using HOLD cursor.= =20 "Yann" <yann.delorme@esker.fr> writes: > The issue is that in this case all rows are store in memory instead of=20 > file in the process postgresql.exe > I think the issue is in the file tuplestore.c. > When a tuple is added the function static void=20 > tuplestore_puttuple_common(Tuplestorestate *state, void *tuple),=20 > USEMEM is not called with tuple size. Hmm ... yeah, I think there's a leak there. > I think that, after adding the tuple in the array, a call to USEMEM=20 > should be done. No, the callers of tuplestore_puttuple_common are supposed to do that. But it looks like tuplestore_putvalues() forgot to do so. So data loads th= at go through that particular API would miss incrementing the space counter. regards, tom lane
Yann, 2011/6/16 Delorme, Yann <Yann.Delorme@esker.fr>: > Thanks > > Do you think that it will be fix in future release 9.1 ? Tom commited a fix in all the supported releases where the bug is present including 9.0: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=669ac03af62328e4eb572dacb8ba319414ef1211 You can either build a specific 9.0 release with the patch or wait for the next 9.0.x maintenance release. Have a nice day. -- Guillaume