Re: ERROR: out of memory DETAIL: Failed on request of size ???

Hello

I reported similar problem week ago - Postgres releases work_mem (assigned
for every SELECT in union) after query finishing. So large SELECT UNION ALL
SELECT UNION ALL .. queries require lot of memory. My customer reported
significant problems for 100 unions. He had to migrate to 64bit pg with
large swap or (first, but probably not good solution) significantly
decreases work_mem. Probably better solution is using temporary table in
this moment.

Regards

Pavel Stehule

2013/11/19 bricklen <bricklen(at)gmail(dot)com>

>
> On Mon, Nov 18, 2013 at 8:30 PM, Brian Wong <bwong64(at)hotmail(dot)com> wrote:
>
>> I've tried any work_mem value from 1gb all the way up to 40gb, with no
>> effect on the error. I'd like to think of this problem as a server process
>> memory (not the server's buffers) or client process memory issue, primarily
>> because when we tested the error there was no other load whatsoever.
>> Unfortunately, the error doesn't say what kinda memory ran out.
>>
>
> I wasn't asking because I thought you should make it higher, I think you
> should make it lower. Set it to 200MB and reload your conf files ("select
> pg_reload_conf()") and try your queries again. work_mem is a per step
> setting, used by aggregates and sort steps, potentially multiple times in a
> single query, also multiplied by any other concurrent queries. In this
> case, it might not be the cause, but certainly try a lower setting to rule
> it out.
>