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(dot)delorme(at)esker(dot)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.