Re: Libpq memory leak

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Polyakov Vladimir <vvpolyakov@gmail.com> 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