Re: Out of memory on SELECT (from sort?) in 8.3

"Matt Magoffin" <postgresql(dot)org(at)msqr(dot)us> writes:
> I see, I was looking at the log entries wrong, sorry. This is Postgres
> 8.3.3 + an xml.c patch (from Kris -
> http://archives.postgresql.org/pgsql-general/2008-07/msg00110.php) to fix
> a memory leak there. The EXPLAIN for this query was this:

Is this particular query using xml features, and if so which? Actually,
could you just show us the whole query and the schemas of the tables
it's using?

> I'm also seeing this TopMemoryContext output in the logs from some other
> situations (sorry I'm not familiar with what/how this gets logged), like
> this autovacuum task that appears to dump this into the log:

The memory context dump happens when we're about to report an "out of
memory" error, so actually the relevant ERROR line would come after the
context dump not before. I'm supposing it's another instance of the
same problem, since the main bloat seems to be here:

> ExecutorState: 1019641424 total in 92998 blocks; 6834024 free (66
> chunks); 1012807400 used
> TIDBitmap: 4186112 total in 9 blocks; 1774784 free (26 chunks);
> 2411328 used
> TupleSort: 31449136 total in 13 blocks; 3451184 free (12 chunks);
> 27997952 used

Thinking about it, it's actually a bit hard to see how it could be an
xml.c problem --- AFAIR that code should only be running in per-tuple
contexts. A leak at the ExecutorState level might perhaps have
something to do with misbehaving aggregates, or some such. Anyway we
really need an example query to test in order to find it.

regards, tom lane