Re: BUG #18624: Memory Leak Issue with PostgreSQL Connection During COPY Command Execution.

On Thu, Sep 19, 2024 at 4:28 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> 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);
> }
>

Can you please provide the table schema and my_table.csv file?

--
Regards
Junwang Zhao