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

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.

> 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.

Thanks for your concise explanation. Do you think it is difficult to fix
that bug? That sounds so to me... though I don't know the design of
catcaches yet.

Could you tell me the conditions where this bug occurs and how to avoid it?
I thought of the following:

[Condition]
1. Create and drop the same table repeatedly on the same session. Whether
the table is a temporary table is irrelevant.
2. Do SELECT against the table. INSERT/DELETE/UPDATE won't cause the
catcache leak.
3. Whether the processing happens in a PL/pgSQL function is irrelevant. The
leak occurs even when you do not use PL/pgSQL.

[Wordaround]
Use CREATE TABLE IF NOT EXISTS and TRUNCATE (or ON COMMIT DROP in case of
temporary tables) to avoid repeated creation/deletion of the same table.

Regards
MauMau