SPI_cursor_fetch Memory Issue

[Wu Ivy <ivywuyzl@xxxxxxxxx>]

Hi all,

My goal is to get data from the Postgres table into a C-function to be processed. Since the table can be very large, I only want to process one chunk of data per time to keep the memory stable.

To achieve this, I use SPI  cursor(https://www.postgresql.org/docs/current/static/spi-spi-cursor-fetch.html) to fetch row chunks from the table. Following is the pseudocode of my program:

        //Prepare the cursor

        SPI_connect();

        SPIPlanPtr SPIplan = SPI_prepare_cursor(command, 0, NULL, 0);

        Portal cursor= SPI_cursor_open(NULL, SPIplan, NULL, NULL, true);

        // Fetch 500 rows per time from cursor

        SPI_cursor_fetch(cursor, true, 500);

        int row_returned= SPI_processed;

        while(row_returned != 0){

        SPITupleTable *tuptable = SPI_tuptable;

         // …

         // Processing data: write data to a local file…

         // …

        SPI_freetuptable(tuptable);

         // fetch next 500 rows

         SPI_cursor_fetch(cursor, true, 500);

        row_returned= SPI_processed;

        }

        SPI_cursor_close(cursor);

        SPI_finish();

From my understanding, cursor points at the entire result rows on heap. After fetching 500 rows, SPI_tuptable saves information of  the row set. When read from SPI_tuptable, memory increases.

After finishing process one chunk,  I freed it by calling SPI_freetuptable( ) before next fetch.  I expected the memory to be constant through out the program, However, the actual memory kept increasing when I run the program. Can anyone tell me why is it happening?  

Thanks in advance!

Best,

Ivy