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