Re: cache invalidation for PL/pgsql functions

On Fri, May 1, 2015 at 9:09 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> On 2015-04-28 19:43, Robert Haas wrote:
>> I guess
>> the root of the problem is that PL/plgsql's cache invalidation logic
>> only considers the pg_proc row's TID and xmin when deciding whether to
>> recompile. For base types that's probably OK, but for composite
>> types, not so much.
>>
>> Thoughts?
>
> 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. However the
implementation is a bit challenging. The DISCARD command doesn't know
what PLs may have decided to cache, nonwithstanding the fact that they
all cache basically the same stuff using basically the same method. I
think the PL interface will need to be extended in some way to support
a new callback.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company