Re: When Update balloons memory

So sorry about that;
I'll repost it here, corrected, for others to use who wants to exhaust their memory:

--PG-14.1

CREATE TABLE public.part_main (
txid bigint,
actiondate timestamp without time zone NOT NULL
);

insert into part_main
select x, '2019-06-01'::timestamp + x%365 * interval '1 day'
from generate_series(1, 30 * 1E6) as x;

CREATE INDEX partindx ON public.part_main USING btree ((actiondate::date)); -- mem bug?
-- CREATE INDEX partindx ON public.part_main USING btree (actiondate); -- no bug
-- mem runaway follows
update part_main set txid = txid + 1;

Klaudie

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐

On Tuesday, December 14th, 2021 at 16:58, Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr> wrote:

> On Tue, 14 Dec 2021 08:16:08 +0000
>
> Klaudie Willis Klaudie(dot)Willis(at)protonmail(dot)com wrote:
>
> > CREATE INDEX partindx ON public.part_main USING btree ((actiondate)::date); -- mem bug?
>
> Nope, syntax error
>
> ERROR: syntax error at or near "::"
>
> LINE 1: ...indx_1 ON public.part_main USING btree ((actiondate)::date);
>
> ^
>
> > -- CREATE INDEX partindx ON public.part_main USING btree (actiondate); -- no bug
> >
> > -- mem runaway follows
> >
> > update part_main set txid = txid + 1;
> >
> > Hope you can replicate it.
>
> Can't replicate on my Intel(R) Core(TM) i5 CPU M 520 @ 2.40GHz with 2Go of RAM
>
> time psql -c 'update part_main set txid = txid + 1' vv
>
> UPDATE 31000000
>
> real 24m39.594s
>
> user 0m0.121s
>
> sys 0m0.036s
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Bien à vous, Vincent Veyron
>
>
> https://marica.fr
>
> Gestion des contentieux juridiques, des contrats et des sinistres d'assurance