Re: cache invalidation for PL/pgsql functions

On Fri, May 1, 2015 at 12:29 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, May 1, 2015 at 9:09 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
>>> We recently hit a similar case in our production environment. What was
>>> annoying about it is that there didn't seem to be a way for the application
>>> to fix the issue by itself, short of reconnecting; even DISCARD ALL doesn't
>>> help. If we can't fix the underlying issue, can we at least provide a way
>>> for apps to invalidate these caches themselves, for example in the form of a
>>> DISCARD option?
>
>> It's been discussed before and I am in favor of it.
>
> I'm not. We should fix the problem not expect applications to band-aid
> around it. This would be particularly ill-advised because there are so
> many applications that just blindly do DISCARD ALL when changing contexts.

The most common real world manifestation of this problem (having to
DISCARD to invalidate plans) is when using schema partitioned data
with pl/pgsql functions. Attempting to hide everything under DISCARD
is trading one problem for another: it's going to be hard for users
of tools like pgbouncer (the main users of DISCARD) to correctly judge
the performance trade-offs and this statement's workings is already
pretty arcane.

merlin