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

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(at)test LOG: ejecutar ps_49999:
SELECT 1 WHERE 1 = $1
2024-02-01 11:19:16.240 CET [40690] test(at)test DETALLE: parámetros: $1 = '1'
2024-02-01 11:19:16.243 CET [40690] test(at)test LOG: ejecutar ps_50000:
SELECT 1 WHERE 1 = $1
2024-02-01 11:19:16.243 CET [40690] test(at)test DETALLE: parámetros: $1 = '1'
2024-02-01 11:19:16.243 CET [40690] test(at)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