The following bug has been logged online: Bug reference: 6061 Logged by: Yann Email address: yann.delo...@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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
