Re: When Update balloons memory

Thanks for the insight!

I have recreated the problem on a different machine and installation where I was more free to experiment to isolate what causes this.
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.

If I remove the ind1 index on "deltatime::date", and just add another one on a random column, the problem disappears. So it seems like the index on the partition key is relevant.
Additional info, alphatable is a 200M evenly distributed row across the partitions, and I haven't tried to see if the ::date casting is relevant for the problem. No there are no triggers here; I can't vouch for what the system creates behind my back though.

Is this a feature or a bug?

--
Klaudie

Sent with ProtonMail Secure Email.

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

On Tuesday, December 7th, 2021 at 15:57, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Klaudie Willis Klaudie(dot)Willis(at)protonmail(dot)com writes:
>
> > The following statement below, when not divided up into chunks, but run across all 800M rows, did trigger an OOM-kill from the OS.
>
> An UPDATE should only result in memory bloat if it's queuing trigger
>
> events to be processed at end-of-statement. You claim there are
>
> no triggers, but are you sure? (what about foreign keys?)
>
> Otherwise, it seems possible that you've identified a memory leak,
>
> but there's not enough detail here to investigate. Can you create
>
> a reproducible test case?
>
> regards, tom lane