Re: [HACKERS] Another nasty cache problem

"Oliver Elphick" <olly@lfix.co.uk> writes:
> Tom Lane wrote:
>> There's a separate question about *why* such a simple query is chewing
>> up so much memory.  What query plan does EXPLAIN show for your test
>> query? 
> I can show a similar problem.

> bray=# explain select * from pg_operator as a, pg_operator as b;
> NOTICE:  QUERY PLAN:

> Nested Loop  (cost=12604.88 rows=258064 width=162)
>  -> Seq Scan on pg_operator b  (cost=24.76 rows=508 width=81)
>  -> Seq Scan on pg_operator a  (cost=24.76 rows=508 width=81)

OK, I sussed this one --- there's a (longstanding) memory leak in
catcache.c.  When entering a system-table tuple into the cache,
it forgot to free the copy of the tuple that had been created in
transaction-local memory context.  Cause enough cache entries to
be created within one transaction, and you'd start to notice the
leak.  The above query exhibits the problem because it produces
about 250K tuples each with six regproc columns, and each regprocout
call does a cache lookup to convert regproc OID to procedure name.
Since you're cycling through 500-plus different procedure names,
and the cache only keeps ~ 300 entries, there's going to be a
fresh cache entry made every time :-(

With the fix I just committed, current sources execute the above query
in constant backend memory space.  psql's space usage still goes to the
moon, of course, since it's trying to buffer the whole query result :-(
... but there's no way around that short of a major redesign of libpq's
API.  When and if we switch over to CORBA, we really need to rethink
the client access API so that buffering the query result in the client-
side library is an option not a requirement.

I do not think this is the same problem that Patrick Welche is
complaining of, unfortunately.
        regards, tom lane