Re: pg_temp_%d namespace creation can invalidate all the cached plan in other backends

On Tue, Feb 23, 2021 at 1:50 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> writes:
> > Planning is expensive and we use plancache to bypass its effect. I find
> the
> > $subject recently which is caused by we register NAMESPACEOID
> invalidation
> > message for pg_temp_%s as well as other normal namespaces. Is it a
> > must?
>
> Since we don't normally delete those namespaces once they exist,
> the number of such events is negligible over the life of a database
> (at least in production scenarios).

I do miss this part during my test. Thanks for sharing this.

> I'm having a very hard time
> getting excited about spending effort here.
>

While I admit this should happen rarely in production, I still think we
may need to fix it. This is kind of tech debt. For example, why my
application has a spike on time xx:yy:zz (Assume it happens even
it is rare). I think there is a very limited DBA who can find out this
reason easily. Even he can find out it, he is still hard to make others
to understand and be convinced. So why shouldn't we just avoid it
if the effort is not huge?

(I do find this in my production case, where the case starts
from this invalidation message, and crashes at ResetPlanCache.
I'm using a modified version, so the crash probably not the community
version's fault and we will fix it separately. )

Also, you can't just drop the inval event, because even if you
> believe it's irrelevant to other backends (a questionable
> assumption), it certainly is relevant locally.
>

Thanks for this hint! Can just finding a place to run
SysCacheInvalidate/CallSyscacheCallbacks locally fix this issue?

--
Best Regards
Andy Fan (https://www.aliyun.com/)