Re: "union all" query consumes all memory

Pavel Stehule <pavel.stehule@gmail.com> writes:
> one my customer reported a "out of memory" issue. After investigation he
> found a main problem in large query that uses a lot of union all queries.
> He wrote a self test:

> do $$

> declare i integer;  str text='';

> begin

> for i in 1..1000 loop

> str := str || 'union all select i,i,i from generate_series(1,50000) g(i) ';

> end loop;

> execute 'select 1,2,3 ' || str;

> end;
> $$

> is it expected behave?

Well, that query is entitled to use 1000 * work_mem for the
generate_series output tuplestores.  What's your work_mem setting?
Perhaps more to the point, what was your customer doing?
        regards, tom lane