Memory growth using many named prepared statements, in spite of using DISCARD ALL afterwards.

[Daniel Blanch Bataller]

Subject: Memory Growth Issue in "Backend" after Creating and Executing
Multiple "Named Prepared Statements" with Different Names and Executing
DISCARD ALL Finally.

Product: PostgreSQL 14

Dear Technical Support Team,

We reach out to you to report an issue related to memory growth in
PostgreSQL backend processes when running many Prepared Statements with
different names, even though the "DISCARD ALL" command is executed at the
end of the program execution.

We understand that while Prepared Statements are executed and maintained in
the session, memory may grow since various objects need to be stored in the
session, such as the parsed query, execution plans, etc.

However, what we don't understand is why, when the DISCARD ALL command is
eventually executed, memory is not freed at all.

Could you please provide us with a more detailed explanation of this
behavior? Additionally, we would like to know if there is any other
specific action or configuration that we can perform to address this issue
and ensure that backend memory is reduced after executing many "Named
Prepared Statements".

We appreciate your attention and look forward to your guidance and
suggestions for resolving this problem.

We have attached a small C program with libpq that demonstrates this issue,
along with the program's output and the execution of the "ps aux" program.

Best regards,

Daniel Blanch Bataller
Hoplasoftware DBA

prepared_statement.c program
============================

/*
 * prepared_statement.c
 * This program demonstrates the backend memory growth using a large number
 * of prepared statements, as expected.
 * But surprisingly, after executing DISCARD ALL; memory is not recovered
at all.
 *
 */

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
#include <unistd.h>

#define ITERATIONS 50000
#define PRINT_TIMES 5000

#define HOST "localhost"
#define PORT "9999"
#define DB   "test"
#define USER "test"
#define PASS "test"

int main() {

    // Connect to the database
    const char *conninfo = "host=" HOST " port=" PORT " dbname=" DB "
user=" USER " password=" PASS "";
    printf("Connecting to %s\n", conninfo);
    PGconn *conn = PQconnectdb(conninfo);

    // Check connection result
    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection error: %s\n", PQerrorMessage(conn));
        PQfinish(conn);
        exit(1);
    }

    // Get backend PID
    printf("Getting backend PID \n");
    PGresult *result = PQexec(conn, "SELECT pg_backend_pid();");

    // Check result status
    if (PQresultStatus(result) != PGRES_TUPLES_OK) {
        fprintf(stderr, "Error executing query: %s\n",
PQerrorMessage(conn));
        PQclear(result);
        PQfinish(conn);
        exit(EXIT_FAILURE);
    }

    // Get result
    char *pid = PQgetvalue(result, 0, 0);
    printf("Backend PID: %s\n", pid);

    // Main loop
    printf("Excecuting %d PreparedStatements\n", ITERATIONS);
    for (int i = 0; i <= ITERATIONS; i++) {

        // Prepare "Prepared Statement"
        char stmt_name[50];
        sprintf(stmt_name, "ps_%d", i);
        const char *query = "SELECT 1 WHERE 1 = $1";
        if (i % PRINT_TIMES == 0) printf("Executing PreparedStatement
'%s'\n", stmt_name);
        PGresult *prepare_result = PQprepare(conn, stmt_name, query, 1,
NULL);

        if (PQresultStatus(prepare_result) != PGRES_COMMAND_OK) {
            fprintf(stderr, "Error preparing the PreparedStatement: %s\n",
PQresultErrorMessage(prepare_result));
            PQclear(prepare_result);
            PQfinish(conn);
            exit(1);
        }

        // Preprared Statement parameters
        const char *paramValues[] = {"1"};

        // Execute Prepared Statement
        PGresult *res = PQexecPrepared(conn, stmt_name, 1, paramValues,
NULL, NULL, 0);

        // Check Prepared Statement execution result
        if (PQresultStatus(res) != PGRES_TUPLES_OK) {
            fprintf(stderr, "Error executing query: %s\n",
PQresultErrorMessage(res));
            PQclear(res);
            PQfinish(conn);
            exit(1);
        }

        // Get results
        int numRows = PQntuples(res);
        int numCols = PQnfields(res);

        for (int i = 0; i < numRows; i++) {
            for (int j = 0; j < numCols; j++) {
                PQgetvalue(res, i, j);                       // Do nothing
            }
        }

        // Free Result
        PQclear(res);
    }

    // Close Connection
    PQfinish(conn);

    return 0;
}

./prepared_statement output:
============================
Connecting to host=localhost port=9999 dbname=test user=test password=test
Getting backend PID
Backend PID: 40690
Excecuting 50000 PreparedStatements
Executing PreparedStatement 'ps_0'
Executing PreparedStatement 'ps_5000'
Executing PreparedStatement 'ps_10000'
Executing PreparedStatement 'ps_15000'
Executing PreparedStatement 'ps_20000'
Executing PreparedStatement 'ps_25000'
Executing PreparedStatement 'ps_30000'
Executing PreparedStatement 'ps_35000'
Executing PreparedStatement 'ps_40000'
Executing PreparedStatement 'ps_45000'
Executing PreparedStatement 'ps_50000'

Postgres log:
=============
2024-02-01 11:19:16.240 CET [40690] test@test LOG:  ejecutar ps_49999:
SELECT 1 WHERE 1 = $1
2024-02-01 11:19:16.240 CET [40690] test@test DETALLE:  parámetros: $1 = '1'
2024-02-01 11:19:16.243 CET [40690] test@test LOG:  ejecutar ps_50000:
SELECT 1 WHERE 1 = $1
2024-02-01 11:19:16.243 CET [40690] test@test DETALLE:  parámetros: $1 = '1'
2024-02-01 11:19:16.243 CET [40690] test@test LOG:  sentencia: DISCARD ALL

Ps aux | grep 40690:
====================
$ ps aux | grep 40690
postgres   40690  5.8  1.4 481204 226024 ?       Ss   11:18   0:04
postgres: 14/main: test test 127.0.0.1(39254) idle