Re: how to limit statement memory allocation

Radoslav Nedyalkov <rnedyalkov(at)gmail(dot)com> writes:
> On Tue, Mar 9, 2021 at 6:03 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Perhaps the accumulation is happening on the client side? libpq doesn't
>> have any provision for spilling a result set to disk.

> Ah, I named it result set wrongly perhaps.
> These are queries , part of a larger ETL function or statement which at the
> end just write to a table.
> The huge join is an intermediate step.

Hm. What's the query plan look like?

The only thing I'm aware of that could consume unbounded memory
on the server side is hash aggregation. (v13 has improved that
situation, but I'm guessing you are running some older version.)
The planner won't choose hash aggregation if it estimates that
the hash table would need to exceed work_mem, but its estimate
could be wrong. If that's the scenario, you could back off
work_mem until hash aggregation isn't used, or you could try to
improve the planner's estimates. If your ETL process involves
intermediate tables, you might need to explicitly ANALYZE those
after you fill them so that the planner has a better idea how
to plan the later steps.

regards, tom lane