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

Hi Heikki!

I made some modifications as you requested:

I have modified the program, now DISCARD ALL is issued within the program,
at the end.
I have added all headers to ps aux output so anyone can see the memory
growth I am refering to.
I now connect directly to postgrres,
I run now 500000 prepared statements.

I hope it's clearer now.

Thank you very much for your tips.

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 500000
#define PRINT_TIMES 5000
#define SLEEP_AFTER_DISCARD_ALL 60

#define HOST "localhost"
#define PORT "5432"
#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);
}

// Execute discard all
printf("Executing DISCARD ALL;\n");
PGresult *discard_result = PQexec(conn, "DISCARD ALL;");

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

// Wait to check backend growth
printf("Waiting %d seconds, now its time to check backend growth!\n",
SLEEP_AFTER_DISCARD_ALL);
sleep(SLEEP_AFTER_DISCARD_ALL);

// Close Connection
PQfinish(conn);

return 0;
}

program output:
===============
Connecting to host=localhost port=5432 dbname=test user=test password=test
Getting backend PID
Backend PID: 6423
Excecuting 500000 PreparedStatements
Executing PreparedStatement 'ps_0'
Executing PreparedStatement 'ps_5000'

...

Executing PreparedStatement 'ps_495000'
Executing PreparedStatement 'ps_500000'
Executing DISCARD ALL;
Waiting 60 seconds, now its time to check backend growth!

Postgres log:
=============
2024-02-02 08:29:22.554 CET [6423] test(at)test LOG: ejecutar ps_499999:
SELECT 1 WHERE 1 = $1
2024-02-02 08:29:22.554 CET [6423] test(at)test DETALLE: parámetros: $1 = '1'
2024-02-02 08:29:22.554 CET [6423] test(at)test LOG: ejecutar ps_500000:
SELECT 1 WHERE 1 = $1
2024-02-02 08:29:22.554 CET [6423] test(at)test DETALLE: parámetros: $1 = '1'
2024-02-02 08:29:22.554 CET [6423] test(at)test LOG: sentencia: DISCARD ALL;

Ps aux output (memory growth):
==============================
$ date; ps aux | head -n 1; ps aux | grep 6423
vie 02 feb 2024 08:29:24 CET
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 6423 59.1 13.1 2358692 2105708 ? Ss 08:28 0:27
postgres: 14/main: test test 127.0.0.1(46084) idle

El jue, 1 feb 2024 a las 14:51, Heikki Linnakangas (<hlinnaka(at)iki(dot)fi>)
escribió:

> On 01/02/2024 15:40, Daniel Blanch Bataller wrote:
> > 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.
>
> There is no DISCARD ALL command in the test program you included. I can
> see the DISCARD ALL in the log output, however. Perhaps you included a
> wrong version of the test program?
>
> In any case, it's notoriously hard to measure memory usage of backend
> processes correctly. The resident size displayed by tools like 'ps' and
> 'top' includes shared memory, too, for example.
>
> I'd recommend that you run the test much longer, and observe the memory
> usage for a much longer period of time. I would expect it to eventually
> stabilize at some reasonable level.
>
> --
> Heikki Linnakangas
> Neon (https://neon.tech)
>
>