SPI_cursor_fetch Memory overrun

From: Wu Ivy <ivywuyzl(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: SPI_cursor_fetch Memory overrun
Date: 2018-08-22 22:45:24
Message-ID: 9FFFFC83-A709-4671-A528-07C9EFDB4F21@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi all,

I’m creating a C extension to write data from the Postgres to a text file . 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 <https://www.postgresql.org/docs/current/static/spi-spi-cursor-fetch.html>) to fetch row chunks from the table. Following is my program:

char* command;
uint64 proc = 1;

// Prepare a statement without executing it yet
SPI_connect();
command = psprintf("SELECT * FROM %s", tableName);
SPIPlanPtr SPIplan = SPI_prepare_cursor(command, 0, NULL, 0);
pfree(command);

// Set up a cursor using a statement created with SPI_prepare_cursor
Portal cursor= SPI_cursor_open(NULL, SPIplan, NULL, NULL, true);
if (SPIplan == NULL){
elog(ERROR,"couldn't create cursor via SPI");
}

// Writing to local Desktop
clock_t begin = clock();
int count = 0;
FILE *fp = fopen("/home/ubuntu/test.txt", "w");
if(fp == NULL){
return psprintf("Error when open file");
}

while(proc > 0){
// Fetch 200000 rows from a cursor
SPI_cursor_fetch(cursor, true, 200000);
proc = SPI_processed; // number of row returned

if (proc != 0){
TupleDesc tupdesc = SPI_tuptable->tupdesc;
uint64 j;

for (j = 0; j < proc; j++){
HeapTuple tuple = tuptable->vals[j];
int i;
count++;
for (i = 1; i <= tupdesc->natts; i++){ //natts -> number of columns
fprintf(fp, (i == tupdesc->natts) ? "%s\n" : "%s," , SPI_getvalue(tuple, tupdesc, i));
}
}
}else{
break;
}

SPI_freetuptable(SPI_tuptable);
elog(INFO, “freed tuptable”);
sleep(5);
}

From my understanding, cursor points at the entire result rows on heap. After fetching certain number of rows, SPI_tuptable points to the allocated row set. After finishing writing the row set to text file, I freed the memory of it by calling SPI_freetuptable( ) before next fetch. I expected the memory to stay around a constant value through out the program, However, the actual memory kept increasing when I run the program. Also, I only observed memory drop after SPI_freetuptable() of first chunk. After that memory kept going up even SPI_freetuptable() is executed. Any clue of why is it happening?

Thanks in advance!

Best,
Ivy

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2018-08-22 23:13:01 Re: Query is over 2x slower with jit=on
Previous Message Andres Freund 2018-08-22 22:43:20 Re: JIT compiling with LLVM v12