Re: Memory leak in PL/pgSQL function which CREATE/SELECT/DROP a temporary table

On Tue, Jun 18, 2013 at 3:40 PM, MauMau <maumau307(at)gmail(dot)com> wrote:

> From: "Heikki Linnakangas" <hlinnakangas(at)vmware(dot)com>
>
>> On 18.06.2013 15:48, Heikki Linnakangas wrote:
>>
>>> Hmm. I could repeat this, and it seems that the catcache for
>>> pg_statistic accumulates negative cache entries. Those slowly take up
>>> the memory.
>>>
>>
>> Digging a bit deeper, this is a rather common problem with negative
>> catcache entries. In general, nothing stops you from polluting the cache
>> with as many negative cache entries as you like. Just do "select * from
>> table_that_doesnt_exist" for as many non-existent table names as you want,
>> for example. Those entries are useful at least in theory; they speed up
>> throwing the error the next time you try to query the same non-existent
>> table.
>>
>
> Really? Would the catcache be polluted with entries for nonexistent
> tables? I'm surprised at this. I don't think it is necessary to speed up
> the query that fails with nonexistent tables, because such queries should
> be eliminated during application development.

I was thinking the same thing, optimizing for failure is nice if there are
no tradeoffs, but not so nice if it leaks memory. But apparently the
negative cache was added for real reasons, not just theory. See discussion
from when it was added:

http://www.postgresql.org/message-id/19585.1012350724@sss.pgh.pa.us

Cheers,

Jeff