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

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(at)gmail(dot)com> wrote:

> On Thu, Sep 19, 2024 at 4:28 PM PG Bug reporting form
> <noreply(at)postgresql(dot)org> wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference: 18624
> > Logged by: Abhishek Mittal
> > Email address: abhmittal(at)gmail(dot)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