Thread: plan invalidation vs stored procedures

Howdy,

What is the status of plan invalidation vs stored procedures? From
the initial design discussion I understand that function change handling
was postponed to "some time in the future". Is anybody already working
on that or maybe some ideas of how to implement this?

The business case for the feature is that most of our db logic is inside
stored procedures and hence use cached plans. Every time a function is
dropped and recreated we get a storm of "cache lookup failed" errors.
If we are lucky, the DBA will detect it and apply appropriate workarounds.
If not ... things get messy.

We are considering of hacking up a proprietary solution to address our
specific problems (e.g. invalidate every plan on pg_proc changes). But I
think that this is something that would be useful to a wider audience and
deserves a more general solution. How about it?

regards,
Martin

Hello

try version 8.3. There lot of dependencies are solved.

Regards
Pavel Stehule

2008/8/5 Martin Pihlak <martin.pihlak@gmail.com>:
> Howdy,
>
> What is the status of plan invalidation vs stored procedures? From
> the initial design discussion I understand that function change handling
> was postponed to "some time in the future". Is anybody already working
> on that or maybe some ideas of how to implement this?
>
> The business case for the feature is that most of our db logic is inside
> stored procedures and hence use cached plans. Every time a function is
> dropped and recreated we get a storm of "cache lookup failed" errors.
> If we are lucky, the DBA will detect it and apply appropriate workarounds.
> If not ... things get messy.
>
> We are considering of hacking up a proprietary solution to address our
> specific problems (e.g. invalidate every plan on pg_proc changes). But I
> think that this is something that would be useful to a wider audience and
> deserves a more general solution. How about it?
>
> regards,
> Martin
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Pavel Stehule wrote:
> Hello
> 
> try version 8.3. There lot of dependencies are solved.
> 

Yes, 8.3 was the version I was testing with. Same results on the HEAD:

$ psql -e -f test.sql
select version();                                                        version


--------------------------------------------------------------------------------------------------------------------------PostgreSQL
8.4develon i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.3 20070929 (prerelease)
 
(Ubuntu 4.1.2-16ubuntu2)
(1 row)

create function foo() returns integer as $$ begin return 1; end; $$ language plpgsql;
CREATE FUNCTION
prepare c1 as select * from foo();
PREPARE
execute c1;foo
-----  1
(1 row)

drop function foo();
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

regards,
Martin

2008/8/5 Martin Pihlak <martin.pihlak@gmail.com>:
> Pavel Stehule wrote:
>> Hello
>>
>> try version 8.3. There lot of dependencies are solved.
>>
>
> Yes, 8.3 was the version I was testing with. Same results on the HEAD:
>
> $ psql -e -f test.sql
> select version();
>                                                         version
>
>
--------------------------------------------------------------------------------------------------------------------------
>  PostgreSQL 8.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.3 20070929 (prerelease)
> (Ubuntu 4.1.2-16ubuntu2)
> (1 row)
>
> create function foo() returns integer as $$ begin return 1; end; $$ language plpgsql;
> CREATE FUNCTION
> prepare c1 as select * from foo();
> PREPARE
> execute c1;
>  foo
> -----
>   1
> (1 row)
>
> drop function foo();
> 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
>
> regards,
> Martin
>

use CREATE OR REPLACE FUNCTION syntax without DROP FUNCTION, CREATE FUNCTION ..

Regards
Pavel Stehule

Martin Pihlak <martin.pihlak@gmail.com> writes:
> create function foo() returns integer as $$ begin return 1; end; $$ language plpgsql;
> CREATE FUNCTION
> prepare c1 as select * from foo();
> PREPARE
> execute c1;
>  foo
> -----
>    1
> (1 row)

> drop function foo();
> 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?
        regards, tom lane

>> 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.)

regards,
Martin

2008/8/5 Martin Pihlak <martin.pihlak@gmail.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.

regards
Pavel Stehule

>
> regards,
> Martin
>
>

2008/8/5 Asko Oja <ascoja@gmail.com>:
> postgres=# create or replace function pavel ( i_param text, status OUT int,
> status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$
> language sql;
> CREATE FUNCTION
> postgres=# create or replace function pavel ( i_param text, status OUT int,
> status_text OUT text, more_text OUT text ) returns record as $$ select
> 200::int, 'ok'::text, 'tom'::text; $$ language sql;
> ERROR:  cannot change return type of existing function
> DETAIL:  Row type defined by OUT parameters is different.
> HINT:  Use DROP FUNCTION first.
>
> On Tue, Aug 5, 2008 at 5:04 PM, Asko Oja <ascoja@gmail.com> wrote:
>>
>> > This is simply a bad, wrong, stupid way to do it.  Why do you not use
>> > CREATE OR REPLACE FUNCTION?
>> I totally agree we should get this fixed first :)
>>
>> postgres=# create or replace function pavel ( i_param text, status OUT
>> int, status_text OUT text ) returns record as $$ select 200::int,
>> 'ok'::text; $$ language sql;
>> ERROR:  cannot change return type of existing function
>> HINT:  Use DROP FUNCTION first.
>>

you cannot change header of function. It's same as change C header of
function without complete recompilation.

>> On Tue, Aug 5, 2008 at 4:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>
>>> Martin Pihlak <martin.pihlak@gmail.com> writes:
>>> > create function foo() returns integer as $$ begin return 1; end; $$
>>> > language plpgsql;
>>> > CREATE FUNCTION
>>> > prepare c1 as select * from foo();
>>> > PREPARE
>>> > execute c1;
>>> >  foo
>>> > -----
>>> >    1
>>> > (1 row)
>>>
>>> > drop function foo();
>>> > 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?
>>>
>>>                        regards, tom lane
>>>
>>> --
>>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>
>

On 8/5/08, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>  >> ERROR:  cannot change return type of existing function
>  >> HINT:  Use DROP FUNCTION first.
>
> you cannot change header of function. It's same as change C header of
> function without complete recompilation.

Thats why plan invalidation for DROP+CREATE is needed.

-- 
marko

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.

merlin

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

On Tue, 2008-08-05 at 16:16 +0200, Pavel Stehule wrote:
> 2008/8/5 Martin Pihlak <martin.pihlak@gmail.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

On Tue, 2008-08-05 at 16:17 +0200, Pavel Stehule wrote:
> 2008/8/5 Asko Oja <ascoja@gmail.com>:
> > postgres=# create or replace function pavel ( i_param text, status OUT int,
> > status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$
> > language sql;
> > CREATE FUNCTION
> > postgres=# create or replace function pavel ( i_param text, status OUT int,
> > status_text OUT text, more_text OUT text ) returns record as $$ select
> > 200::int, 'ok'::text, 'tom'::text; $$ language sql;
> > ERROR:  cannot change return type of existing function
> > DETAIL:  Row type defined by OUT parameters is different.
> > HINT:  Use DROP FUNCTION first.
> >
> > On Tue, Aug 5, 2008 at 5:04 PM, Asko Oja <ascoja@gmail.com> wrote:
> >>
> >> > This is simply a bad, wrong, stupid way to do it.  Why do you not use
> >> > CREATE OR REPLACE FUNCTION?
> >> I totally agree we should get this fixed first :)
> >>
> >> postgres=# create or replace function pavel ( i_param text, status OUT
> >> int, status_text OUT text ) returns record as $$ select 200::int,
> >> 'ok'::text; $$ language sql;
> >> ERROR:  cannot change return type of existing function
> >> HINT:  Use DROP FUNCTION first.
> >>
> 
> you cannot change header of function. It's same as change C header of
> function without complete recompilation.

SQL is not C. 

You don't have to recompile the whole SQL database when you add columns
to tables, so why should you need to do it, when you add a column to
table-returning function ?

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

2008/8/6 Hannu Krosing <hannu@krosing.net>:
> On Tue, 2008-08-05 at 16:17 +0200, Pavel Stehule wrote:
>> 2008/8/5 Asko Oja <ascoja@gmail.com>:
>> > postgres=# create or replace function pavel ( i_param text, status OUT int,
>> > status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$
>> > language sql;
>> > CREATE FUNCTION
>> > postgres=# create or replace function pavel ( i_param text, status OUT int,
>> > status_text OUT text, more_text OUT text ) returns record as $$ select
>> > 200::int, 'ok'::text, 'tom'::text; $$ language sql;
>> > ERROR:  cannot change return type of existing function
>> > DETAIL:  Row type defined by OUT parameters is different.
>> > HINT:  Use DROP FUNCTION first.
>> >
>> > On Tue, Aug 5, 2008 at 5:04 PM, Asko Oja <ascoja@gmail.com> wrote:
>> >>
>> >> > This is simply a bad, wrong, stupid way to do it.  Why do you not use
>> >> > CREATE OR REPLACE FUNCTION?
>> >> I totally agree we should get this fixed first :)
>> >>
>> >> postgres=# create or replace function pavel ( i_param text, status OUT
>> >> int, status_text OUT text ) returns record as $$ select 200::int,
>> >> 'ok'::text; $$ language sql;
>> >> ERROR:  cannot change return type of existing function
>> >> HINT:  Use DROP FUNCTION first.
>> >>
>>
>> you cannot change header of function. It's same as change C header of
>> function without complete recompilation.
>
> SQL is not C.
>
> You don't have to recompile the whole SQL database when you add columns
> to tables, so why should you need to do it, when you add a column to
> table-returning function ?
>

I thing, it's possible - but it's neccessary completly new dictionary
with dependencies (some dependencies are dynamic - polymorphic
functions) so it's dificult task.

Pavel

> --------------
> Hannu
>
>
>

On Wed, 2008-08-06 at 12:13 +0200, Pavel Stehule wrote:
> 2008/8/6 Hannu Krosing <hannu@krosing.net>:
> > On Tue, 2008-08-05 at 16:17 +0200, Pavel Stehule wrote:
..
> >> you cannot change header of function. It's same as change C header of
> >> function without complete recompilation.
> >
> > SQL is not C.
> >
> > You don't have to recompile the whole SQL database when you add columns
> > to tables, so why should you need to do it, when you add a column to
> > table-returning function ?
> >
> 
> I thing, it's possible - but it's neccessary completly new dictionary
> with dependencies (some dependencies are dynamic - polymorphic
> functions) so it's dificult task.

I think that you can safely err on the side of caution, that is, save
more dependendcies than actually affected.

Or you even add dependencies from inside the pl, either at compile/check
or run time (cached of course), so that you hit the exact right function
oid and can reuse the function lookup already done.

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

2008/8/6 Hannu Krosing <hannu@krosing.net>:
> On Wed, 2008-08-06 at 12:13 +0200, Pavel Stehule wrote:
>> 2008/8/6 Hannu Krosing <hannu@krosing.net>:
>> > On Tue, 2008-08-05 at 16:17 +0200, Pavel Stehule wrote:
> ..
>> >> you cannot change header of function. It's same as change C header of
>> >> function without complete recompilation.
>> >
>> > SQL is not C.
>> >
>> > You don't have to recompile the whole SQL database when you add columns
>> > to tables, so why should you need to do it, when you add a column to
>> > table-returning function ?
>> >
>>
>> I thing, it's possible - but it's neccessary completly new dictionary
>> with dependencies (some dependencies are dynamic - polymorphic
>> functions) so it's dificult task.
>
> I think that you can safely err on the side of caution, that is, save
> more dependendcies than actually affected.
>

> Or you even add dependencies from inside the pl, either at compile/check
> or run time (cached of course), so that you hit the exact right function
> oid and can reuse the function lookup already done.
>
actually functions doesn't see into SQL statements - but I though is
could be hook on new item in plan cache, so there can be some
registration that try to analyze all called functions from plan and
add some info to some buffer. There is lot of some. Some have to write
it :)

Pavel


> -----------------
> Hannu
>
>
>

On Wed, 2008-08-06 at 15:41 +0200, Pavel Stehule wrote:
> 2008/8/6 Hannu Krosing <hannu@krosing.net>:
> > On Wed, 2008-08-06 at 12:13 +0200, Pavel Stehule wrote:
> >> 2008/8/6 Hannu Krosing <hannu@krosing.net>:
> >> > On Tue, 2008-08-05 at 16:17 +0200, Pavel Stehule wrote:
> > ..
> >> >> you cannot change header of function. It's same as change C header of
> >> >> function without complete recompilation.
> >> >
> >> > SQL is not C.
> >> >
> >> > You don't have to recompile the whole SQL database when you add columns
> >> > to tables, so why should you need to do it, when you add a column to
> >> > table-returning function ?
> >> >
> >>
> >> I thing, it's possible - but it's neccessary completly new dictionary
> >> with dependencies (some dependencies are dynamic - polymorphic
> >> functions) so it's dificult task.
> >
> > I think that you can safely err on the side of caution, that is, save
> > more dependendcies than actually affected.
> >
> 
> > Or you even add dependencies from inside the pl, either at compile/check
> > or run time (cached of course), so that you hit the exact right function
> > oid and can reuse the function lookup already done.
> >
> actually functions doesn't see into SQL statements - but I though is
> could be hook on new item in plan cache, so there can be some
> registration that try to analyze all called functions from plan and
> add some info to some buffer. There is lot of some. Some have to write
> it :)

Actually I think we need a callback in either execute or verify methods
so that various pl-s can implement adding the dependency.

or maybe not even a callback, pl implementation could just add needed
entries to pg_catalog.pg_shdepend when doing direct function calls. And
it should inspect prepared plans for a list of called functions. the
latter probably needs some help from planner, so that it would be easy
to get a list of function oids used by a plan.

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

On Wed, Aug 6, 2008 at 2:20 AM, Marko Kreen <markokr@gmail.com> wrote:
> 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.

sure -- this a known issue --,  but the point is that there are not
that many reasons why you have to drop/create a function if you are
careful.  hiding function prototypes is actually pretty powerful
although you have to deal with creating the extra types.

merlin

On 8/6/08, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Wed, Aug 6, 2008 at 2:20 AM, Marko Kreen <markokr@gmail.com> wrote:
>  > 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.
>
> sure -- this a known issue --,  but the point is that there are not
>  that many reasons why you have to drop/create a function if you are
>  careful.  hiding function prototypes is actually pretty powerful
>  although you have to deal with creating the extra types.

Um.  If you are talking about about returning type defined by CREATE TYPE
then you are wrong as changing type requires DROP+CREATE for both type
and function.

Again - the main problem is if you are not careful or really need to do
the DROP+CREATE, it will result in permanent errors in all backends.
To fix it, admin needs to manually intervene.  And this is silly,
as we already have all the mechanisms needed to survive the situation
gracefully.

-- 
marko

On Wed, Aug 6, 2008 at 2:28 PM, Marko Kreen <markokr@gmail.com> wrote:
> On 8/6/08, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Wed, Aug 6, 2008 at 2:20 AM, Marko Kreen <markokr@gmail.com> wrote:
>>  > 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.
>>
>> sure -- this a known issue --,  but the point is that there are not
>>  that many reasons why you have to drop/create a function if you are
>>  careful.  hiding function prototypes is actually pretty powerful
>>  although you have to deal with creating the extra types.
>
> Um.  If you are talking about about returning type defined by CREATE TYPE
> then you are wrong as changing type requires DROP+CREATE for both type
> and function.

you missed the point...if your return type is a composite type that is
backed by the table (CREATE TABLE, not CREATE TYPE), then you can
'alter' the type by altering the table.  This can be done without full
drop recreate of the function.

merlin

On 8/6/08, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Wed, Aug 6, 2008 at 2:28 PM, Marko Kreen <markokr@gmail.com> wrote:
>  > On 8/6/08, Merlin Moncure <mmoncure@gmail.com> wrote:
>  >> On Wed, Aug 6, 2008 at 2:20 AM, Marko Kreen <markokr@gmail.com> wrote:
>  >>  > 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.
>  >>
>  >> sure -- this a known issue --,  but the point is that there are not
>  >>  that many reasons why you have to drop/create a function if you are
>  >>  careful.  hiding function prototypes is actually pretty powerful
>  >>  although you have to deal with creating the extra types.
>  >
>  > Um.  If you are talking about about returning type defined by CREATE TYPE
>  > then you are wrong as changing type requires DROP+CREATE for both type
>  > and function.
>
> you missed the point...if your return type is a composite type that is
>  backed by the table (CREATE TABLE, not CREATE TYPE), then you can
>  'alter' the type by altering the table.  This can be done without full
>  drop recreate of the function.

Yes, although I suspect it works by accident and can lead to crash
with creative use. (eg. plpgsql 'record' cache, any PL's rettype cache
are not invalidated when doing the ALTER TABLE)

But you missed my point: if you don't have functions backed by table,
the DROP+CREATE results in inappropriate behaviour that can be avoided.

-- 
marko

Merlin Moncure wrote:
> you missed the point...if your return type is a composite type that is
> backed by the table (CREATE TABLE, not CREATE TYPE), then you can
> 'alter' the type by altering the table.  This can be done without full
> drop recreate of the function.

Which - at least IMHO - clearly shows that we ought to support
ALTER TYPE for composite types ;-)

Is there anything fundamental standing in the way of that, or is it just 
that nobody yet cared enough about this?

regrads, Florian Pflug

On 8/6/08, Florian Pflug <fgp.phlo.org@gmail.com> wrote:
> Merlin Moncure wrote:
> > you missed the point...if your return type is a composite type that is
> > backed by the table (CREATE TABLE, not CREATE TYPE), then you can
> > 'alter' the type by altering the table.  This can be done without full
> > drop recreate of the function.
>
>  Which - at least IMHO - clearly shows that we ought to support
>  ALTER TYPE for composite types ;-)
>
>  Is there anything fundamental standing in the way of that, or is it just
> that nobody yet cared enough about this?

Yes, that would be really good idea.  Although as I mentioned in previous
email, even for tables it works by accident, thus we need some invalidation
mechanism for both tables and types in PLs.

And then maybe even rettype (OUT param) change with CREATE OR REPLACE?

-- 
marko

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

Le 6 août 08 à 20:42, Marko Kreen a écrit :
> But you missed my point: if you don't have functions backed by table,
> the DROP+CREATE results in inappropriate behaviour that can be
> avoided.


Just wanted to say I agree with Marko here: it seems we have here a
pretty nice (and required) core feature which does not work all of the
time. It might be a difficult case to get right, but we certainly are
NOT getting it right now.

Whatever the workarounds, it would be nice that plan invalidation
worked whenever it makes sense, and DROP FUNCTION certainly makes
sense. Allowing REPLACE function to change return type (adding an OUT
parameter, the TABLE column list, etc) would certainly be a good
feature to add, but having existing feature set behave correctly is
more important.
It seems to qualify the complaint as a bug.

Regards,
- --
Dimitri Fontaine
PostgreSQL DBA, Architecte



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (Darwin)

iEYEARECAAYFAkiaAPsACgkQlBXRlnbh1bl3CQCgvtP09HyFgzdqWVEpmGBA4bXy
3YUAoKLCrP+2AGqmctL9IBFmpUUmdssD
=b8HX
-----END PGP SIGNATURE-----

On Wed, Aug 6, 2008 at 3:29 PM, Florian Pflug <fgp.phlo.org@gmail.com> wrote:
> Merlin Moncure wrote:
>>
>> you missed the point...if your return type is a composite type that is
>> backed by the table (CREATE TABLE, not CREATE TYPE), then you can
>> 'alter' the type by altering the table.  This can be done without full
>> drop recreate of the function.
>
> Which - at least IMHO - clearly shows that we ought to support
> ALTER TYPE for composite types ;-)
>
> Is there anything fundamental standing in the way of that, or is it just
> that nobody yet cared enough about this?

I look at it from another perspective.  I see very little value in
'create type as'...it just creates a table that you can't insert to
and can't alter (but I agree).

merlin