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
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 |