Re: cache invalidation for PL/pgsql functions

On 4/28/15 12:58 PM, Pavel Stehule wrote:
> I hate to use the term "bug" for what somebody's probably going to
> tell me is acceptable behavior, but that seems like a bug. 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?
>
>
> It is inconsistent - and I know so one bigger Czech companies, that use
> Postgres, had small outage, because they found this issue, when deployed
> new version of procedure.
>
> The question is what is a cost of fixing

We don't actually consider the argument types at all (pl_comp.c:169):

/* We have a compiled function, but is it still valid? */
if (function->fn_xmin == HeapTupleHeaderGetRawXmin(procTup->t_data) &&
ItemPointerEquals(&function->fn_tid, &procTup->t_self))

Perhaps pg_depend protects from a base type changing.

This problem also exists for internal variables:

create table moo(m int);
create function t() returns text language plpgsql as $$declare m moo;
begin m := null; return m.t; end$$;
select t(); -- Expected error
alter table moo add t text;
select t(); -- Unexpected error

So it seems the correct fix would be to remember the list of every xmin
for every type we saw... unless there's some way to tie the proc into
type sinval messages?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com