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
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
