Thread: ALTER FUNCTION problem

I am working on an implementation of 'ALTER FUNCTION' and have run into a 
problem.

plpgsql. plperl and pltcl all cache the result of a compile of prosrc.
Which leads to things like:

mhh=# create function f() returns integer as 'begin return 42; end;' language 
'plpgsql';
CREATE
mhh=# select f();f  
----42
(1 row)

mhh=# alter function f() as 'begin return 44; end;';
ALTER
mhh=# select f();f  
----42
(1 row)

mhh=# select proname, prosrc from pg_proc where proname = 'f';proname |        prosrc         
---------+-----------------------f       | begin return 44; end;

Of course, leaving psql and re-entering fixes the problem. But the same 
problem is manifested between concurrent sessions as well.

I would like to propose that a new attribute be added to pg_proc 'proserial'. 
'CREATE FUNCTION' will set proserial to 0. 'ALTER FUNCTION' will increment it
each time. It would be up to the individual PL handlers to check to make sure 
that their cache is not out of date.

Is there a better way to solve this problem?


-- 
Mark Hollomon

Mark Hollomon <mhh@mindspring.com> writes:
> plpgsql. plperl and pltcl all cache the result of a compile of prosrc.

plpgsql does, but I didn't think the other two do.

> I would like to propose that a new attribute be added to pg_proc
> 'proserial'.  'CREATE FUNCTION' will set proserial to 0. 'ALTER
> FUNCTION' will increment it each time. It would be up to the
> individual PL handlers to check to make sure that their cache is not
> out of date.

This is completely inadequate for plpgsql, if not for the others,
because plpgsql also caches query plans --- which depend on more than
the text of the function.  I don't think it's worth our time to put
in a partial solution; we need to think about a generic cache
invalidation mechanism.

Jan Wieck has posted some comments about this, and I think there was
also some discussion in connection with Karel Zak's proposed cross-
backend query plan cache.  Check the archives...
        regards, tom lane