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

On 01/25/2014 11:36 PM, Bruce Momjian wrote:
> On Tue, Jun 18, 2013 at 09:07:59PM +0300, 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.
>>
>> But there is a crucial difference in this case; the system created a
>> negative cache entry for the pg_statistic row of the table, but once
>> the relation is dropped, the cache entry keyed on the relation's
>> OID, is totally useless. It should be removed.
>>
>> We have this problem with a few other catcaches too, which have what
>> is effectively a foreign key relationship with another catalog. For
>> example, the RELNAMENSP catcache is keyed on pg_class.relname,
>> pg_class.relnamespace, yet any negative entries are not cleaned up
>> when the schema is dropped. If you execute this repeatedly in a
>> session:
>>
>> CREATE SCHEMA foo;
>> SELECT * from foo.invalid; -- throws an error
>> DROP SCHEMA foo;
>>
>> it will leak similarly to the original test case, but this time the
>> leak is into the RELNAMENSP catcache.
>>
>> To fix that, I think we'll need to teach the catalog cache about the
>> relationships between the caches.
>
> Is this a TODO?

Yes, I think so. Added.

- Heikki