Re: When Update balloons memory

Hi,

Turns out the base case is simpler than I thought. Not involving partitions at all

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;

Hope you can replicate it.

best regards
Klaudie

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

On Tuesday, December 14th, 2021 at 12:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Klaudie Willis Klaudie.Willis@protonmail.com writes:
>
> > So, it seems like the index is central cog here:
> >
> > > create index ind1 on alpha ((deltatime::date));
> > >
> > > where "alpha" is a partition tableset partitioned by (deltatime::date)
> > >
> > > The general and simple updates like:
> > >
> > > update alphatable set gamma=gamma || "#postfix#"
> > >
> > > makes the process memory balloon to the point of OOM.
>
> That seems like a bug, but please supply a self-contained test case
>
> rather than expecting other people to reverse-engineer one.
>
> regards, tom lane