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 <ravikrish...@icloud.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.git...@gmail.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 <st...@nickm.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 > >
