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

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);
}

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

Hi Abhishek,

On Thu, Sep 19, 2024 at 7:04 PM Abhishek Mittal <abhmittal@gmail.com> wrote:
>
> Hi Junwang,
>
> Please find the table schema below along with the attached sample CSV file.
> test=> \d my_table
>                        Table "public.my_table"
>      Column     |          Type           | Collation | Nullable | Default
> ----------------+-------------------------+-----------+----------+---------
>  id             | integer                 |           |          |
>  uindex         | integer                 |           |          |
>  pindex         | integer                 |           |          |
>  hid            | integer                 |           |          |
>  hcode          | bigint                  |           |          |
>  len            | integer                 |           |          |
>  uname          | character varying(4096) |           |          |
>  plen           | integer                 |           |          |
>  pname          | character varying(8192) |           |          |
>
> Note: There are no indexes on any of the columns.
>
> Regards
> Abhishek Mittal
>
> On Thu, Sep 19, 2024 at 2:57 PM Junwang Zhao <zhjwpku@gmail.com> wrote:
>>
>> 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
>
>
>
> --
> amittal

I tried to reproduce the bug you reported against postgresql master
but no luck, I built from source with asan, I didn't observe any leak.

I did not test against 12.16 yet because I got some compiling issues.

I use the following command btw, not sure if this is enough to
reproduce the bug.

select 'COPY my_table from ''/tmp/my_table.csv'' CSV' FROM
generate_series(0,10000) \gexec

--
Regards
Junwang Zhao

Abhishek Mittal <abhmittal@gmail.com> writes:
> Please find the table schema below along with the attached sample CSV file.

I too tried to reproduce this, using both Junwang's \gexec method
and the C program.  I don't see any leak.  However, I tested v12
branch tip, so it's possible that you are hitting a problem that's
been fixed since 12.16.  I notice a plausibly-related item in the
12.18 release notes:

    Fix memory leak when performing JIT inlining (Andres Freund, Daniel Gustafsson)

If that's it, then setting "jit = off" should do as a temporary
workaround.

If updating to 12.20 does not resolve the problem, then we are
going to need more information.  Is this straight community
Postgres, or some variant?  If you built it yourself, what
options did you use?  Do you have any extensions installed?

            regards, tom lane

Hi,

On 2024-09-19 07:30:54 +0000, PG Bug reporting form 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:        

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

I suspect what you may be seeing is that RES is increased the first time a
backend references a shared memory page, unless huge page are used. Because
you're copying more and more data in, more and more of shared buffers are
going to be used.

Do you see RES increasing to something substantially larger than
shared_buffers?

You can query the different aspects making up RES by doing
grep Rss /proc/$PID/status

which will return something like:

RssAnon:        3364 kB
RssFile:       11196 kB
RssShmem:     2204444 kB

Where only RssAnon really is memory usage.

FWIW, this was for a pid that was doing lots of COPYs and where top showed a
memory usage of 2GB.

Greetings,

Andres Freund

On 10/3/24 11:49, Abhishek Mittal wrote:
> Hi,
> 
> I'm currently facing a blocker in my project. Could we please check this
> issue.
> 
> Thank you for your assistance.
> 

Please, don't top post. Reply in-line.

You have not reported if you tried upgrading to 12.20. Chances are this
issue was already fixed since 12.16, people are unlikely to spend time
investigating this, only to find it was a waste of time.

Also, Tom asked you a couple important questions you ignored:

> If updating to 12.20 does not resolve the problem, then we are
> going to need more information.  Is this straight community
> Postgres, or some variant?  If you built it yourself, what
> options did you use?  Do you have any extensions installed?

Please provide this info. It was suggested to try with "jit=off", so
maybe try that too - I personally don't see why would that matter for
the COPY command, but maybe it does ...

Finally, I wonder if you're simply misinterpreting what RES means. It's
not "private memory used by the process". So if you're simply seeing RES
to grow to 10GB and conclude "memory leak", it may not be the case. It
could be shared memory, for example - you need to inspect "smaps" for
the process to see what exactly the RES figure includes.


regards

-- 
Tomas Vondra