Re: Memory leak in PL/pgSQL function which CREATE/SELECT/DROP a temporary table - Mailing list pgsql-hackers

From Jeff Janes
Subject Re: Memory leak in PL/pgSQL function which CREATE/SELECT/DROP a temporary table
Date
Msg-id CAMkU=1wRqcs2xLs4=iDU=nXtJKr5kiiyv-Ev-Uvwd=u=gYCa_g@mail.gmail.com
Whole thread Raw
In response to Re: Memory leak in PL/pgSQL function which CREATE/SELECT/DROP a temporary table  ("MauMau" <maumau307@gmail.com>)
Responses Re: Memory leak in PL/pgSQL function which CREATE/SELECT/DROP a temporary table  ("MauMau" <maumau307@gmail.com>)
List pgsql-hackers
On Tue, Jun 18, 2013 at 3:40 PM, MauMau <maumau307@gmail.com> wrote:
From: "Heikki Linnakangas" <hlinnakangas@vmware.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:

 
Cheers,

Jeff

pgsql-hackers by date:

Previous
From: "MauMau"
Date:
Subject: Re: Memory leak in PL/pgSQL function which CREATE/SELECT/DROP a temporary table
Next
From: Vik Fearing
Date:
Subject: Re: LEFT JOIN LATERAL can remove rows from LHS