Re: TRUNCATE memory leak with temporary tables?

Hi Ravi,

I am not sure about that
"It creates a new empty table , followed by rename of the existing table to
the new empty table and finally dropping of the old table."

You mean table is re-created with new oid?

thanks
Ahmet

On Fri, 28 May 2021 at 15:10, Ravi Krishna <ravikrishna3(at)icloud(dot)com> wrote:

> Truncate is not delete + vaccum.
> It creates a new empty table , followed by rename of the existing table to
> the new empty table and finally dropping of the old table.
>
> On May 28, 2021 at 7:05 AM, Vijaykumar Jain <
> vijaykumarjain(dot)github(at)gmail(dot)com> wrote:
>
> Yes,
> I too see growth when text type is used, but not when int or even fixed
> size char(10) is used.
>
> I always thought truncate was similar to delete + vacuum full,
> but checking for your scenarios, I did not see an update on
> pg_stat_user_table on truncate for vacuums.
>
> then i checked
> PostgreSQL Internals: TRUNCATE (pykello.github.io)
> <https://pykello.github.io/2019/03/postgresql-internals-truncate.html>
> to help understand truncation better.
>
> but then i still do not understand how a col type *text* which is dynamic
> results in mem growth (coz there are no rows inserted, i understand for
> long strings db does work to compress, move them to toast tables etc) but
> these are empty rows.
>
> Maybe someone else will be able to explain what is going on.
>
>
>
>
> On Fri, 28 May 2021 at 06:52, Nick Muerdter <stuff(at)nickm(dot)org> wrote:
>
>> 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
>>
>>
>>
>
> --
> Thanks,
> Vijay
> Mumbai, India
>
>