BUG #18624: Memory Leak Issue with PostgreSQL Connection During COPY Command Execution. - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18624
Logged by:          Abhishek Mittal
Email address:      abhmittal@gmail.com
PostgreSQL version: 12.16
Operating system:   ubuntu0.20.04.1
Description:

We are currently experiencing a memory leak issue with the PostgreSQL
connection process while executing the COPY command multiple times on the
same connection.

Issue Details:
1. We establish a connection using the following command:
psql <DB_NAME> <USER_NAME>

2. We retrieve the process ID associated with this connection from the
pg_stat_activity table using the query:
SELECT pid, query FROM pg_stat_activity;

3. We then monitor this process ID using the top command:
top -p <PID>

4. We observe that the RES (resident) memory reported by the top command
continuously increases. This memory increase only occurs when we run queries
on this connection. If no queries are executed, the RES memory remains
stable.

5. We attempted to clear the cache by executing the DISCARD ALL command
after each query, but the issue persists.

Additionally, we tested the process using a C program with libpg, and the
issue remains the same.

Additional Information:
We are executing only the COPY command to copy a CSV file into a database
table.
In our scenario, the RES memory usage has reached approximately 10 GB.

Could you please provide guidance or recommend a solution to address this
memory leak issue?


Please find the sample C program below.
int db_copy_into_db(PGconn *conn, char *buf, int read_bytes)
{
  PGresult *res;
  char copy_cmd[1024];
  int ret;

  /*If read_bytes are 0 then no need for further processing */
  if(!read_bytes)
    return 0;

  sprintf(copy_cmd, "COPY %s FROM STDIN WITH DELIMITER ',' NULL AS '' CSV",
"my_table");
  res = PQexec(conn, copy_cmd);
  if (PQresultStatus(res) != PGRES_COPY_IN)
  {
    fprintf(stderr,"COPY command for %s table failed: %s\n",
"urltable_1114", PQerrorMessage(conn));
    conn = NULL;
    return -1;
  }
  //fprintf(stdout ,"COPY command is started\n");

  ret = PQputCopyData(conn, buf, read_bytes);
  //fprintf(stdout, "ret = %d\n", ret);
  if(ret == -1)
  {
    fprintf(stderr, "PQputCopyData command failed: %s\n",
PQerrorMessage(conn));
    conn = NULL;
    return -1;
  }

  /* Ends the COPY_IN operation successfully if errormsg is NULL.
   * If errormsg is not NULL then the COPY is forced to fail,
   * with the string pointed to by errormsg used as the error message. */
  if(PQputCopyEnd(conn, NULL) != 1)
  {
    fprintf(stderr,"PQputCopyEnd() command for %s table failed: %s\n",
"urltable_1114", PQerrorMessage(conn));
    conn = NULL;
  }
  PQclear(res);
  res = PQexec(conn, "DISCARD ALL");

  if (PQresultStatus(res) != PGRES_COMMAND_OK)
  {
    fprintf(stderr, "DISCARD ALL command failed: %s",
PQerrorMessage(conn));
  }
  PQclear(res);
  return 0;
}

void read_csv_and_copy_inio_db( char *csv_file, PGconn *conn)
{
 char* read_buf = malloc(sizeof(char) * 1024 * 1024 * 20);
 int read_bytes;

 /* 1. open file
  * read bufer
  */
  int fd;
  int status;
  fd = open(csv_file, O_RDONLY);
  while (1)
  {
    //seek to byke 0
    status = lseek(fd, 0, SEEK_SET);
    if(status == -1)
      fprintf(stderr, "Failed to seek offset file\n");
    else
      printf("Seeking to 0\n");
    read_bytes = read(fd, read_buf, 1024 * 1024 * 20);
    db_copy_into_db(conn, read_buf, read_bytes);

    getc(stdin);
  }
}

int main()
{
  char csv_file[1024 * 10];
  char *conn_info = "dbname=my_db user=abhi";
  PGconn *conn = PQconnectdb(conn_info);
  if(PQstatus(conn) != CONNECTION_OK)
  {
    fprintf(stderr, "Connection to database failed: %s\n",
PQerrorMessage(conn));
    if(conn)
    {
      PQfinish(conn);
      conn = NULL;
    }
    return -1;
  }
  sprintf(csv_file,"/tmp/my_table.csv");
  read_csv_and_copy_inio_db(csv_file, conn);
}