BUG #6061: Progresql.exe memory usage using HOLD cursor. - Mailing list pgsql-bugs

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.