Re: [HACKERS] plan invalidation vs stored procedures

On 8/5/08, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Aug 5, 2008 at 10:12 AM, Martin Pihlak <martin.pihlak@gmail.com> wrote:
> >>> 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.)
>
>
> one workaround is to use a table based custom composite type:
>
> create table foo_output(a int, b text);
>
> create function foo() returns foo_output as ...
>
> alter table foo_output add column c int;
>
> create or replace foo() if necessary. This also works for 'in' variables.
>
> voila! :-) note you can't use standard composite type because there
> is no way to 'alter' it.

Yes. Or require always new name for function.

But the main problem is that if the DROP/CREATE happens, the failure
mode is very nasty - you get permanent error on existing backends.
(Main case I'm talking about is functions calling other functions.)

Some sorta recovery mode would be nice to have, it does not even
need function perfectly. Giving error once and then recover would
be better than requiring manual action from admin.

--
marko

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers