From: | "Nick Muerdter" <stuff(at)nickm(dot)org> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | TRUNCATE memory leak with temporary tables? |
Date: | 2021-05-28 01:21:49 |
Message-ID: | 88986113-6b01-452b-89d0-9492b6a79e33@www.fastmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I've been seeing what looks like unbounded memory growth (until the OOM killer kicks in and kills the postgres process) when running a pl/pgsql function that performs TRUNCATE statements against various temporary tables in a loop. I think I've been able to come up with some fairly simple reproductions of the issue in isolation, but I'm trying to figure out if this is a memory leak or of I'm perhaps doing something wrong with tuning or other settings.
What I've observed:
- The memory growth occurs if the temp table has indexes or a primary key set on it.
- Alternatively, the memory growth also occurs if the temp table has certain column types on it (eg, "text" types).
- If the table doesn't have indexes and only has integer columns present, then the memory growth does *not* occur.
- I originally saw this against a PostgreSQL 12 server, but I've tested this against PostgreSQL 9.6.22, 12.7, and 13.3 Docker containers and reproduced it against all versions in the containers.
Here are 2 separate examples that seem to show the memory growth on the server (the first being a table with a "text" column, the second example having no text column but a primary key index):
DO $$
DECLARE
i bigint;
BEGIN
CREATE TEMPORARY TABLE pg_temp.foo (id integer, bar text);
FOR i IN 1..200000000 LOOP
TRUNCATE pg_temp.foo;
END LOOP;
END
$$
DO $$
DECLARE
i bigint;
BEGIN
CREATE TEMPORARY TABLE pg_temp.foo (id integer);
ALTER TABLE pg_temp.foo ADD PRIMARY KEY (id);
FOR i IN 1..200000000 LOOP
TRUNCATE pg_temp.foo;
END LOOP;
END
$$
Compare that to this example (which doesn't have an index or any other column types that trigger this), which does *not* show any memory growth:
DO $$
DECLARE
i bigint;
BEGIN
CREATE TEMPORARY TABLE pg_temp.foo (id integer);
FOR i IN 1..200000000 LOOP
TRUNCATE pg_temp.foo;
END LOOP;
END
$$
Any help in determining what's going on here (or if there are other ways to go about this) would be greatly appreciated!
Thank you!
Nick
From | Date | Subject | |
---|---|---|---|
Next Message | Dean Gibson (DB Administrator) | 2021-05-28 03:41:14 | AWS forcing PG upgrade from v9.6 a disaster |
Previous Message | Gmail | 2021-05-28 00:38:48 | Re: How different is AWS-RDS postgres? |