plan invalidation vs stored procedures

Hello

try version 8.3. There lot of dependencies are solved.

Regards
Pavel Stehule

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

Hi

Sadly PostgreSQL inability to invalidate plan cache when function is dropped
causes us downtime and costs money.
ERROR: cache lookup failed for function 24865)
This time our developers just rewrote function to use OUT parameters instead
of return type.
Currently i had to forbid dropping functions in our most critical databases
but that makes developers unhappy.

And as i understand it is not fixed in 8.3:
Comment from code
* Currently, we use only relcache invalidation events to invalidate plans.
* This means that changes such as modification of a function definition do
* not invalidate plans using the function. This is not 100% OK --- for
* example, changing a SQL function that's been inlined really ought to
* cause invalidation of the plan that it's been inlined into --- but the
* cost of tracking additional types of object seems much higher than the
* gain, so we're just ignoring them for now.

So we will have to get it fixed and better would be to do it so that
solution suits everybody.

Our current workaround include updating pg_proc after release or letting
pgBouncer to reconnect all connections but neither solution is good and
cause us to lose valuable minutes in error flood when we miss some crucial
drop function.

Asko

On Tue, Aug 5, 2008 at 1:40 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

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

Hi

Thanks for pointing to another thing to fix :)

postgres=# create type public.ret_status as ( status integer, status_text
text);
CREATE TYPE
postgres=# create or replace function pavel ( i_param text ) returns
public.ret_status 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 ) returns record as $$ select 200::int, 'ok'::text; $$
language sql;
ERROR: cannot change return type of existing function
HINT: Use DROP FUNCTION first.

Asko

On Tue, Aug 5, 2008 at 4:00 PM, Pavel Stehule <pavel.stehule@gmail.com>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

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.

On Tue, Aug 5, 2008 at 4:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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:

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

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

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