Thread: Create or replace function doesn't work so well

Create or replace function doesn't work so well

From

Peter Eisentraut

Date:

18 October 2001, 17:00:43

peter=# drop function test();
DROP

peter=# create or replace function test() returns int as 'return 1;' language plperl;
CREATE
peter=# select test();test
------   1
(1 row)

peter=# create or replace function test() returns int as 'return 2;' language plperl;
CREATE
peter=# select test();test
------   1
(1 row)

The same can be observed with PL/Tcl and PL/Python, but not with PL/pgSQL
and plain SQL.  Obviously, there is some caching going on, and a session
restart fixes everything, but the failure with this plain and simple test
case makes me wonder about this new feature...

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: Create or replace function doesn't work so well

From

Tom Lane

Date:

18 October 2001, 21:15:16

Peter Eisentraut <peter_e@gmx.net> writes:
> The same can be observed with PL/Tcl and PL/Python, but not with PL/pgSQL
> and plain SQL.  Obviously, there is some caching going on, and a session
> restart fixes everything, but the failure with this plain and simple test
> case makes me wonder about this new feature...

Hmm.  I fixed plplgsql a few days ago, but I was unaware that the other
PLs cached anything.  Will look.
        regards, tom lane

Re: Create or replace function doesn't work so well

From

Gavin Sherry

Date:

18 October 2001, 21:53:32

Peter,

On Thu, 18 Oct 2001, Peter Eisentraut wrote:

> peter=# drop function test();
> DROP
> 

[snip]

> The same can be observed with PL/Tcl and PL/Python, but not with PL/pgSQL
> and plain SQL.  Obviously, there is some caching going on, and a session
> restart fixes everything, but the failure with this plain and simple test
> case makes me wonder about this new feature...
> 

I cannot recreate this on my devel system with plain SQL

template1=# drop function test();
DROP
template1=# create or replace function test() returns int as 'select 1;'
language 'sql';
CREATE
template1=# select test();test
------   1
(1 row)

template1=# create or replace function test() returns int as 'select 2;'
language 'sql';
CREATE
template1=# select test();test
------   2
(1 row)


However,

template1=# create or replace function test() returns int as 'begin
template1'# return ''1'';
template1'# end;
template1'# ' language 'plpgsql';
CREATE
template1=# select test();test
------   1
(1 row)


template1=# create or replace function test() returns int as 'begin
template1'# return ''2'';
template1'# end;
template1'# ' language 'plpgsql';
CREATE
template1=# select test();test
------   1
(1 row)


Yet,

template1=# create or replace function test() returns int as 'select 3'
language 'sql';
CREATE
template1=# select test();test
------   3
(1 row)

So, it must be caching at of procedural (C??) functions. Apologies for not
testing this on all languages -- I presumed what was good for SQL would be
good for PLpgSQL ;).

I'll look into further but.

Gavin

Re: Create or replace function doesn't work so well

From

Bruce Momjian

Date:

06 November 2001, 20:47:45

Has this been resolved?


---------------------------------------------------------------------------

> Peter,
> 
> On Thu, 18 Oct 2001, Peter Eisentraut wrote:
> 
> > peter=# drop function test();
> > DROP
> > 
> 
> [snip]
> 
> > The same can be observed with PL/Tcl and PL/Python, but not with PL/pgSQL
> > and plain SQL.  Obviously, there is some caching going on, and a session
> > restart fixes everything, but the failure with this plain and simple test
> > case makes me wonder about this new feature...
> > 
> 
> I cannot recreate this on my devel system with plain SQL
> 
> template1=# drop function test();
> DROP
> template1=# create or replace function test() returns int as 'select 1;'
> language 'sql';
> CREATE
> template1=# select test();
>  test
> ------
>     1
> (1 row)
> 
> template1=# create or replace function test() returns int as 'select 2;'
> language 'sql';
> CREATE
> template1=# select test();
>  test
> ------
>     2
> (1 row)
> 
> 
> However,
> 
> template1=# create or replace function test() returns int as 'begin
> template1'# return ''1'';
> template1'# end;
> template1'# ' language 'plpgsql';
> CREATE
> template1=# select test();
>  test
> ------
>     1
> (1 row)
> 
> 
> template1=# create or replace function test() returns int as 'begin
> template1'# return ''2'';
> template1'# end;
> template1'# ' language 'plpgsql';
> CREATE
> template1=# select test();
>  test
> ------
>     1
> (1 row)
> 
> 
> Yet,
> 
> template1=# create or replace function test() returns int as 'select 3'
> language 'sql';
> CREATE
> template1=# select test();
>  test
> ------
>     3
> (1 row)
> 
> So, it must be caching at of procedural (C??) functions. Apologies for not
> testing this on all languages -- I presumed what was good for SQL would be
> good for PLpgSQL ;).
> 
> I'll look into further but.
> 
> Gavin
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026

Re: Create or replace function doesn't work so well

From

Tom Lane

Date:

06 November 2001, 21:07:46

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Has this been resolved?

Yes.
        regards, tom lane