From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
cc: Bruce Momjian <bruce@momjian.us>,
        Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>,
        "Ideriha,
    Takeshi" <ideriha.takeshi@jp.fujitsu.com>,
        "pgsql-hackers@lists.postgresql.org"
 <pgsql-hackers@lists.postgresql.org>,
        "Tsunakawa,
    Takayuki" <tsunakawa.takay@jp.fujitsu.com>,
        "alvherre@alvh.no-ip.org" <alvherre@alvh.no-ip.org>,
        "andres@anarazel.de" <andres@anarazel.de>,
        "michael.paquier@gmail.com" <michael.paquier@gmail.com>,
        "david@pgmasters.net" <david@pgmasters.net>,
        "craig@2ndquadrant.com" <craig@2ndquadrant.com>
Subject: Re: Protect syscache from bloating with negative cache entries
In-reply-to: 
 <CA+TgmoaQVtw=D8sDe78NwrOAPmJFjsR6XWtQ29C=fquoBvhCVw@mail.gmail.com>
References: <4E72940DA2BF16479384A86D54D0988A6F1BCB6F@G01JPEXMBKW04>
 <20181019.173457.68080786.horiguchi.kyotaro@lab.ntt.co.jp>
 <4E72940DA2BF16479384A86D54D0988A6F1F4165@G01JPEXMBKW04>
 <20181127.193622.252197705.horiguchi.kyotaro@lab.ntt.co.jp>
 <4E72940DA2BF16479384A86D54D0988A6F3D09C1@G01JPEXMBKW04>
 <4E72940DA2BF16479384A86D54D0988A6F3D0D5F@G01JPEXMBKW04>
 <7925.1547397565@sss.pgh.pa.us>
 <CA+TgmoYPWOHB0uPwSza350mH6n58KWeJKAyfuFbR11HXgGMvTw@mail.gmail.com>
 <20190117194851.GG10895@momjian.us>
 <CA+TgmoaQVtw=D8sDe78NwrOAPmJFjsR6XWtQ29C=fquoBvhCVw@mail.gmail.com>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
	message dated "Fri, 18 Jan 2019 15:50:21 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1390.1547845037.1@sss.pgh.pa.us>
Date: Fri, 18 Jan 2019 15:57:17 -0500
Message-ID: <1391.1547845037@sss.pgh.pa.us>
Precedence: bulk

Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Jan 17, 2019 at 2:48 PM Bruce Momjian <bruce@momjian.us> wrote:
>> Unfortunately, because we have not found something we are happy with, we
>> have done nothing.  I agree LRU can be expensive.  What if we do some
>> kind of clock sweep and expiration like we do for shared buffers?  I
>> think the trick is figuring how frequently to do the sweep.  What if we
>> mark entries as unused every 10 queries, mark them as used on first use,
>> and delete cache entries that have not be used in the past 10 queries.

> I still think wall-clock time is a perfectly reasonable heuristic.

The easy implementations of that involve putting gettimeofday() calls
into hot code paths, which would be a Bad Thing.  But maybe we could
do this only at transaction or statement start, and piggyback on the
gettimeofday() calls that already happen at those times.

			regards, tom lane