Re: Extension Enhancement: Buffer Invalidation in pg_buffercache

Hii,
Thanks for your feedback. We have decided to add a role for the
extension to solve that problem.
And concerning to pg_unwarm table I think we can create a new function
to do that but I think a general user would not require to clear a
table from buffercache.
We can use bufferid and where statements to do the same if a
superuser/(specific role) requests it.

Thanks.

On Sat, 29 Jul 2023 at 02:55, Cary Huang <cary(dot)huang(at)highgo(dot)ca> wrote:
>
> Hello
>
> I had a look at the patch and tested it on CI bot, it compiles and tests fine both autoconf and meson. I noticed that the v2 patch contains the v1 patch file as well. Not sure if intended but put there my accident.
>
> > 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?
>
> I think "invalidating" is the right terminology here, it is exactly what the feature is doing, it tries to invalidate a buffer ID by calling InvalidateBuffer() routine inside buffer manager and calls FlushBuffer() before invalidating if marked dirty.
>
> The problem here is that InvalidateBuffer() could be dangerous because it allows a user to invalidate buffer that may have data in other tables not owned by the current user,
>
> I think it all comes down to the purpose of this feature. Based on the description in this email thread, I feel like this feature should be categorized as a developer-only feature, to be used by PG developer to experiment and observe some development works by invalidating one more more specific buffers..... If this is the case, it may be helpful to add a "DEVELOPER_OPTIONS" in GUC, which allows or disallows the TryInvalidateBuffer() to run or to return error if user does not have this developer option enabled.
>
> If the purpose of this feature is for general users, then it would make sense to have something like pg_unwarm (exactly opposite of pg_prewarm) that takes table name (instead of buffer ID) and drop all buffers associated with that table name. There will be permission checks as well so a user cannot pg_unwarm a table owned by someone else. User in this case won't be able to invalidate a particular buffer, but he/she should not have to as a regular user anyway.
>
> thanks!
>
> Cary Huang
> -------------
> HighGo Software Inc. (Canada)
> cary(dot)huang(at)highgo(dot)ca
> www.highgo.ca
>