Tom Lane <tgl@xxxxxxxxxxxxx> writes:
> Polyakov Vladimir <vvpolyakov@xxxxxxxxx> writes:
> > Program written in C using Libpq, which receives large files (BYTEA)
> > has a memory leak.
> > I need to free ALL of the used memory after each sql query.
I discussed this yesterday with Vladimir. I think Vladimir trying to
complain about behavior of libpq input buffer. It is never shrinking, if
i not mistaken, it is only grow.
So, for example, if you select long bytea field at start of the
connection and then select only small fields, you do not need this big
inBuffer in memory, but you are still with it during all lifetime of
this connection.
Here is example:
/* -*- mode: c; indent-tabs-mode: nil; c-file-style: "stroustrup"; -*- */
/*
* gcc -W -Wall -Wextra -pedantic -o pg-test-bytea-free pg-test-bytea-free.c -lpq
*
* create table files(id int, data bytea);
* insert into files values (60, repeat('1234567890', (60 * 1024 * 1024) / 10)::bytea);
* insert into files values (300, repeat('1234567890', (300 * 1024 * 1024) / 10)::bytea);
* insert into files values (100, repeat('1234567890', (100 * 1024 * 1024) / 10)::bytea);
*
*/
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
#include <internal/libpq-int.h>
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
void test_for(PGconn *conn, int pk)
{
PGresult *res;
char query[128];
int i;
sprintf(query, "SELECT data FROM files WHERE id = %d", pk);
printf("=== %d ===\n", pk);
for (i = 0 ; i < 3; ++i)
{
res = PQexec(conn, query);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "%s failed: %s", query, PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
printf("%dMb BEFORE PQclear\n", pk);
system("ps axv | grep [pg]-test-bytea");
PQclear(res);
printf("%dMb AFTER PQclear\n", pk);
system("ps axv | grep [pg]-test-bytea");
printf("*** inBuffer %p inBufSize %10d inStart %10d inCursor %10d inEnd %10d\n",
conn->inBuffer, conn->inBufSize, conn->inStart, conn->inCursor, conn->inEnd);
puts("");
}
}
int main ()
{
PGconn *conn;
conn = PQconnectdb("");
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}
puts("BEFORE PQexec loop");
system("ps axv | grep [pg]-test-bytea");
test_for(conn, 300);
test_for(conn, 100);
test_for(conn, 60);
return 0;
}
$ gcc -g -g3 -ggdb -ggdb3 -O0 -W -Wall -Wextra -pedantic --std=gnu99 -I/usr/include/postgresql -I/usr/include/postgresql/8.4/server/ -lpq pg-test-bytea-free.c -o pg-test-bytea-free
$ ./pg-test-bytea-free
BEFORE PQexec loop
6533 pts/0 S+ 0:00 0 3 60872 2420 0.0 ./pg-test-bytea-free
=== 300 ===
300Mb BEFORE PQclear
6533 pts/0 S+ 0:00 0 3 892368 616848 15.1 ./pg-test-bytea-free
300Mb AFTER PQclear
6533 pts/0 S+ 0:00 0 3 585164 309644 7.6 ./pg-test-bytea-free
*** inBuffer 0x7f2a946cf010 inBufSize 536870912 inStart 314572829 inCursor 314572829 inEnd 314572829
300Mb BEFORE PQclear
6533 pts/0 S+ 0:00 0 3 892368 616848 15.1 ./pg-test-bytea-free
300Mb AFTER PQclear
6533 pts/0 S+ 0:01 0 3 585164 309644 7.6 ./pg-test-bytea-free
*** inBuffer 0x7f2a946cf010 inBufSize 536870912 inStart 314572829 inCursor 314572829 inEnd 314572829
300Mb BEFORE PQclear
6533 pts/0 S+ 0:01 0 3 892368 616848 15.1 ./pg-test-bytea-free
300Mb AFTER PQclear
6533 pts/0 S+ 0:01 0 3 585164 309644 7.6 ./pg-test-bytea-free
*** inBuffer 0x7f2a946cf010 inBufSize 536870912 inStart 314572829 inCursor 314572829 inEnd 314572829
=== 100 ===
100Mb BEFORE PQclear
6533 pts/0 S+ 0:01 0 3 687568 412048 10.1 ./pg-test-bytea-free
100Mb AFTER PQclear
6533 pts/0 S+ 0:01 0 3 585164 309644 7.6 ./pg-test-bytea-free
*** inBuffer 0x7f2a946cf010 inBufSize 536870912 inStart 104857629 inCursor 104857629 inEnd 104857629
100Mb BEFORE PQclear
6533 pts/0 S+ 0:01 0 3 687568 412048 10.1 ./pg-test-bytea-free
100Mb AFTER PQclear
6533 pts/0 S+ 0:01 0 3 585164 309644 7.6 ./pg-test-bytea-free
*** inBuffer 0x7f2a946cf010 inBufSize 536870912 inStart 104857629 inCursor 104857629 inEnd 104857629
100Mb BEFORE PQclear
6533 pts/0 S+ 0:02 0 3 687568 412048 10.1 ./pg-test-bytea-free
100Mb AFTER PQclear
6533 pts/0 S+ 0:02 0 3 585164 309644 7.6 ./pg-test-bytea-free
*** inBuffer 0x7f2a946cf010 inBufSize 536870912 inStart 104857629 inCursor 104857629 inEnd 104857629
=== 60 ===
60Mb BEFORE PQclear
6533 pts/0 S+ 0:02 0 3 646608 371088 9.1 ./pg-test-bytea-free
60Mb AFTER PQclear
6533 pts/0 S+ 0:02 0 3 585164 309644 7.6 ./pg-test-bytea-free
*** inBuffer 0x7f2a946cf010 inBufSize 536870912 inStart 62914589 inCursor 62914589 inEnd 62914589
60Mb BEFORE PQclear
6533 pts/0 S+ 0:02 0 3 646608 371088 9.1 ./pg-test-bytea-free
60Mb AFTER PQclear
6533 pts/0 S+ 0:02 0 3 585164 309644 7.6 ./pg-test-bytea-free
*** inBuffer 0x7f2a946cf010 inBufSize 536870912 inStart 62914589 inCursor 62914589 inEnd 62914589
60Mb BEFORE PQclear
6533 pts/0 S+ 0:02 0 3 646608 371088 9.1 ./pg-test-bytea-free
60Mb AFTER PQclear
6533 pts/0 S+ 0:02 0 3 585164 309644 7.6 ./pg-test-bytea-free
*** inBuffer 0x7f2a946cf010 inBufSize 536870912 inStart 62914589 inCursor 62914589 inEnd 62914589
You are with 309644 RSS at the end.
As i can understand Vladimir, he have many clients reading bytea in one
server like this and he is trying to reduce memory consumption.
--
Sergey Burladyan
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
