Re: 9.0 Out of memory

Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz> writes:
> Ok I removed the geometry column from the cursor query within the function and the session still runs out of memory. I'm still seeing the same error message as well:

> PortalHeapMemory: 16384 total in 4 blocks; 5944 free (0 chunks); 10440 used
> ExecutorState: 122880 total in 4 blocks; 63984 free (8 chunks); 58896 used
> ExprContext: 2496819768 total in 9 blocks; 21080 free (15 chunks); 2496798688 used

> So I guess it's not likely to be the PostGIS geometry to text cast that is leaking the memory.

OK, so that was a wrong guess.

> One thing that has got me interested now is query that executes directly before (see SQL below). If I remove the geometry column that is generated using ST_Collect aggregate function, the subsequent function involving the cursor query completes and the transaction also runs to completion.

Hrm. We were pretty much guessing as to which query was running in that
portal, I think. It seems entirely plausible that this other query is
the one at fault instead. It might be premature to blame ST_Collect per
se though --- in particular I'm wondering about the ORDER BY on the
ST_Collect's input. But if this line of thought is correct, you ought
to be able to exhibit a memory leak using just that sub-part of that
query, without the surrounding function or any other baggage. Maybe the
leak wouldn't drive the backend to complete failure without that
additional overhead; but a leak of a couple gig ought to be pretty
obvious when watching the process with "top" or similar tool.

regards, tom lane