Re: Extension Enhancement: Buffer Invalidation in pg_buffercache

On Wed, Jul 19, 2023 at 12:45 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> I don't think "invalidating" is the right terminology. Note that we already
> have InvalidateBuffer() - but it's something we can't allow users to do, as it
> throws away dirty buffer contents (it's used for things like dropping a
> table).
>
> How about using "discarding" for this functionality?

+1

> Using the buffer ID as the identifier doesn't seem great, because what that
> buffer is used for, could have changed since the buffer ID has been acquired
> (via the pg_buffercache view presumably)?
>
> My suspicion is that the usual usecase for this would be to drop all buffers
> that can be dropped?

Well the idea was to be able to drop less than everything. Instead of
having to bike-shed what the user interface should look like to
specify what subset of everything you want to drop, you can just write
SQL queries (mostly likely involving the pg_buffercache view, indeed).
It's true that buffer IDs can change underneath your feet between
SELECT and discard, but the whole concept is inherently racy like
that. Suppose we instead had pg_unwarm('my_table') or whatever
instead. Immediately after it runs and before it even returns, some
blocks of my_table can finish up coming back into the pool. It's also
interesting to be able to kick individual pages out when testing code
that caches buffers IDs for ReadRecentBuffer(), and other buffer-pool
work. Hence desire to not try to be clever at all here, and just come
up with the absolute bare minimum thing that can kick buffers out by
ID and leave the rest up to hackers/experts who are willing and able
to write queries to supply them. You can still drop everything that
can be dropped -- generate_series. Or whatever you want.