postgresql consume more memory in db server for long running connection

Question

We have a c++ server application which is connecting to postgresql database using libpq library. Application creating 100s of connection to database and most of the connection's life time is application scope.

Initially application was running fine, but over a period of time postgres server consuming more memory for long running connections. By writing a below sample program I come to know creating prepared statements using PQsendPrepare and PQsendQueryPrepared is causing the memory consumption issue in database server.

How we can fix this server memory issue? is there any libpq function to free the memory in server?

#include <iostream>
#include <fstream>
#include <string>
#include <sstream>
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

int main(int argc, char *argv[]) {

    const int LEN = 10;
    const char *paramValues[1];

    int paramFormats[1];
    int rowId = 7369;
    Oid paramTypes[1];
    char str[LEN];
    snprintf(str, LEN, "%d", rowId);
    paramValues[0] = str;
    paramTypes[0]=20;
    paramFormats[0]=0;
    long int c=1;

    PGresult* result;
    //PGconn *conn = PQconnectdb("user=scott dbname=dame");
    PGconn *conn = PQsetdbLogin ("", "", NULL, NULL, "dame", "scott", "tiger") ;

    if (PQstatus(conn) == CONNECTION_BAD) {
        fprintf(stderr, "Connection to database failed: %s\n",
            PQerrorMessage(conn));
    do_exit(conn);
    }
    char *stm = "SELECT coalesce(ename,'test') from emp where empno=$1";
    for(;;)
    {
        std::stringstream strStream ; 
        strStream << c++ ;
        std::string strStatementName = "s_" + strStream.str() ;
        if(PQsendPrepare(conn,strStatementName.c_str(), stm,1,paramTypes) )
        {
            result = PQgetResult(conn); 
            if (PQresultStatus(result) != PGRES_COMMAND_OK)
            {
                PQclear(result) ;
                result = NULL ;
                do
                {
                    result = PQgetResult(conn);
                    if(result != NULL)
                    {
                        PQclear (result) ;
                    }
                } while (result != NULL) ;
                std::cout<<"error prepare"<<PQerrorMessage (conn)<<std::endl;
                break;
            }
            PQclear(result) ;
            result = NULL ;
            do
            {
                result = PQgetResult(conn);
                if(result != NULL)
                {
                    PQclear (result) ;
                }
            } while (result != NULL) ;
        }
        else
        {
            std::cout<<"error:"<<PQerrorMessage (conn)<<std::endl;
            break;
        }

        if(!PQsendQueryPrepared(conn,
                strStatementName.c_str(),1,(const char* const *)paramValues,paramFormats,paramFormats,0))
        {
            std::cout<<"error:prepared "<<PQerrorMessage (conn)<<std::endl;
        }
        if (!PQsetSingleRowMode(conn))
        {
            std::cout<<"error singrow mode "<<PQerrorMessage (conn)<<std::endl;
        }
    result = PQgetResult(conn);
        if (result != NULL)
        {
            if((PGRES_FATAL_ERROR == PQresultStatus(result)) || (PGRES_BAD_RESPONSE == PQresultStatus(result)))
            {
                PQclear(result);
                result = NULL ;
                do
                {
                    result = PQgetResult(conn);
                    if(result != NULL)
                    {
                        PQclear (result) ;
                    }
                } while (result != NULL) ;
                break;
            }

            if (PQresultStatus(result) == PGRES_SINGLE_TUPLE)
            {
                std::ofstream myfile;
            myfile.open ("native.txt",std::ofstream::out |     std::ofstream::app);
                myfile << PQgetvalue(result, 0, 0)<<"\n";
                myfile.close();
                PQclear(result);
                result = NULL ;
                do
                {
                    result = PQgetResult(conn) ;
                    if(result != NULL)
                    {
                        PQclear (result) ;
                    }
                }
                while(result != NULL) ;
                sleep(10);
            }
            else if(PQresultStatus(result) == PGRES_TUPLES_OK || PQresultStatus(result) ==  PGRES_COMMAND_OK)
            {
                PQclear(result);
                result = NULL ;
                do
                {
                    result = PQgetResult(conn) ;
                    if(result != NULL)
                    {
                        PQclear (result) ;
                    }
                }
                while(result != NULL) ;
            }
       }

    }

    PQfinish(conn);
    return 0;

}

Answer 1

Initially application was running fine, but over a period of time postgres server consuming more memory for long running connections. By writing a below sample program I come to know creating prepared statements using PQsendPrepare and PQsendQueryPrepared is causing the memory consumption issue in database server.

Well that seems unsurprising. You are generating a new prepared statement name at each iteration of your outer loop, and then creating and executing a prepared statement of that name. All the resulting, differently-named prepared statements will indeed remain in the server's memory as long as the connection is open. This is intentional.

How we can fix this server memory issue?

I'd characterize it as a program logic issue, not a server memory issue, at least as far as the test program goes. You obtain resources (prepared statements) and then allow them to hang around when you have no further use for them. The statements aren't leaked per se, as you could recreate the algorithmically-generated statement names, but the problem is similar to a resource leak. In your program, not in Postgres.

If you want to use one-off prepared statements then give them the empty string, "", as their name. Postgres calls these "unnamed" statements. Each unnamed statement you prepare will replace any previous one belonging to the same connection.

But even that's a hack. The most important feature of prepared statements in the first place is that they can be reused. Every statement prepared by your test program is identical, so not only are you wasting memory, you are also wasting CPU cycles. You should prepare it once only -- via PQsendPrepare(), or maybe simply PQprepare() -- and when it has successfully been prepared, execute it as many times as you want with PQsendQueryPrepared() or PQqueryPrepared(), passing the same statement name every time (but possibly different parameters).

is there any libpq function to free the memory in server?

The documentation for the synchronous versions of the query functions says:

Prepared statements for use with PQexecPrepared can also be created by executing SQL PREPARE statements. Also, although there is no libpq function for deleting a prepared statement, the SQL DEALLOCATE statement can be used for that purpose.

To the best of my understanding, there is only one flavor of prepared statement in Postgres, used by the synchronous and asynchronous functions alike. So no, libpq provides no function specifically for dropping prepared statements associated with a connection, but you can write a statement in SQL to do the job. Of course, it would be pointless to create a new, uniquely-named prepared statement to execute such a statement.

Most programs do not need anywhere near so many distinct prepared statements as to produce the kind of problem you report having.