Re: plan invalidation vs stored procedures

On Tue, 2008-08-05 at 16:16 +0200, Pavel Stehule wrote:
> 2008/8/5 Martin Pihlak <martin(dot)pihlak(at)gmail(dot)com>:
> >>> DROP FUNCTION
> >>> create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql;
> >>> CREATE FUNCTION
> >>> execute c1;
> >>> psql:test.sql:11: ERROR: cache lookup failed for function 36555
> >>
> >> This is simply a bad, wrong, stupid way to do it. Why do you not use
> >> CREATE OR REPLACE FUNCTION?
> >>
> >
> > Well, the test case was an illustration. The actual reason for DROP and CREATE is
> > the inability to change function return type. In our case there are plpgsql OUT
> > parameters involved, and there is no other way to add additional OUT parameters
> > without dropping the function first. I'd be glad if this was fixed, but I still
> > think that proper plan invalidation for function changes is needed (inlined
> > functions, ALTER FUNCTION stuff etc.)
>
> It isn't possible. Probably some wrong is in your database design.

Yup. It is called evolving a design.

Them stupid people did not design all their possible future uses of
functions properly at first try.

I'm sure that it is possible to work around postgreSQL's inability to
properly invalidate plans by treating SQL as C++, where every change
needs a complete recompile & restart, but that enforces an unneccessary
cost in downtime.

-------------
Hannu