TRUNCATE memory leak with temporary tables?
От | Nick Muerdter |
---|---|
Тема | TRUNCATE memory leak with temporary tables? |
Дата | |
Msg-id | 88986113-6b01-452b-89d0-9492b6a79e33@www.fastmail.com обсуждение исходный текст |
Ответы |
Re: TRUNCATE memory leak with temporary tables?
Re: TRUNCATE memory leak with temporary tables? |
Список | pgsql-general |
I've been seeing what looks like unbounded memory growth (until the OOM killer kicks in and kills the postgres process) whenrunning a pl/pgsql function that performs TRUNCATE statements against various temporary tables in a loop. I think I'vebeen able to come up with some fairly simple reproductions of the issue in isolation, but I'm trying to figure out ifthis 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 Dockercontainers 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
В списке pgsql-general по дате отправления: