BUG #14106: Large memory client and server consumption in the insert of big values. - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      14106
Logged by:          Nikolay Nikitin
Email address:      Nikolay.Nikitin@infowatch.com
PostgreSQL version: 9.5.2
Operating system:   Red Hat server 6.7
Description:

Hi,

If I insert big value with X size by using C and libpq then client takes 2X
memory and server process takes 370M (empty session process memory size) +
4X.

The 1G value insert demands 6G + 370M if the client works on same server as
postgres. It is very big.

I think there are two ways to solve this problem:

1. The best way is modify client libpq interface to support server prepared
statements and its parameter binding before execution.
Also in this parameter binding a partial loading is needed of text and bytea
parameters same as large object partial loading.

We can not use large objects because our data is partitioned in many
tablespaces.

2. Second way is the reducing memory consumption in the existing code.
I do not understand why client takes one size of parameter in addition.
And 4X server process memory consumption is very surprisingly.

This bug intersects with BUG #14100: Large memory consumption in a
partitioning insert of great values.
But that bug is about internal server process memory taking.
This bug is about client-server memory consumption.

sample of code:

Table:

create table test_insert
(
  b bytea
);


C code:


int main(int argc, char **argv)
{
    PGconn     *conn;
    PGresult   *res;

    conn = PQconnectdb("user='postgres' password='...' host='192.168.198.20'
port='5432' dbname='postgres'");

    int buffer_size = 500 * 1024 * 1024;
    char *buffer = malloc(buffer_size);

    FILE* fd = fopen("some_file_path", "rb");

    fread(buffer, buffer_size, 1, fd);
    fclose(fd);

    const char *paramValues[1];
    int         paramLengths[1];
    int         paramFormats[1];
    paramValues[0] = buffer;
    paramLengths[0] = buffer_size;
    paramFormats[0] = 1;

    res = PQexecParams(conn, "insert into test_insert(b) values ($1)", 1,
NULL, paramValues, paramLengths, paramFormats, 1);
    PQclear(res);

    free(buffer);

    PQfinish(conn);
    printf("Ok");

    return 0;
}

Best regards, Nikitin Nikolay.