Re: BUG #8687: HashAggregate runs out of memory

Michael,

Attached is the script for reproducing the problem that I was seeing.
The actual update was being run from a single plsql session, so I don't think multiple work_mem areas would have been allocated.
Note that the join produces more records than the larger table, which is likely a contributor to the issue.

--Peter

-----Original Message-----
From: Michael Paquier [mailto:michael(dot)paquier(at)gmail(dot)com]
Sent: Monday, December 23, 2013 9:40 AM
To: Sylvester, Peter A.
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #8687: HashAggregate runs out of memory

On Fri, Dec 20, 2013 at 12:09 PM, <peters(at)mitre(dot)org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 8687
> Logged by: Peter Sylvester
> Email address: peters(at)mitre(dot)org
> PostgreSQL version: 9.3.2
> Operating system: CENTOS6
> Description:
>
> I have a query which causes out of memory conditions with a HashAggregate
> plan.
>
>
> The query involves a join between a 10M row table and a 1K row table,
> work_mem=300MB, process space goes over 6GB then machine runs out of memory
> and swap and the OS kills the back end process.
>
> I have a test script which I can upload to recreate the issue.
And this would be welcome. In this case the most interesting part is
the test case you could provide, perhaps this could show up some
memory not free'd in the code path you use for your query. It is hard
to tell what might be going wrong in your case, so first are you sure
that you weren't trapped by the fact that work_mem allocates its
amount of memory for each sort/limit/hash operation?

Regards,
--
Michael