I have done the following test pn v9.2.4 with two concurrent sessions:
-- session no.1
tmp1=# create table t1 ( t text );
CREATE TABLE
Tempo: 37,351 ms
tmp1=# create table t2 ( t text );
CREATE TABLE
Tempo: 33,363 ms
tmp1=# create or replace function f1( out tx text )
tmp1-# language plpgsql
tmp1-# stable as $l0$
tmp1$# begin
tmp1$# select t into tx from t1 limit 1;
tmp1$# end;
tmp1$# $l0$;
CREATE FUNCTION
Tempo: 14,148 ms
tmp1=# create or replace function f2( out tx text )
tmp1-# language plpgsql
tmp1-# volatile as $l0$
tmp1$# begin
tmp1$# select t into tx from t1 limit 1;
tmp1$# end;
tmp1$# $l0$;
CREATE FUNCTION
Tempo: 12,712 ms
tmp1=# insert into t1 values ( 'ciao' );
INSERT 0 1
Tempo: 14,777 ms
tmp1=# insert into t2 values ( 'hello' );
INSERT 0 1
Tempo: 9,032 ms
tmp1=# select * from f1();
tx
------
ciao
(1 riga)
Tempo: 0,600 ms
tmp1=# select * from f2();
tx
------
ciao
(1 riga)
Tempo: 0,549 ms
-- session no.2
tmp1=# begin;
BEGIN
Tempo: 0,287 ms
tmp1=# alter table t1 rename to t3;
ALTER TABLE
Tempo: 1,023 ms
tmp1=# alter table t2 rename to t1;
ALTER TABLE
Tempo: 0,533 ms
tmp1=# alter table t3 rename to t2;
ALTER TABLE
Tempo: 0,449 ms
-- back to session no.1
tmp1=# select * from f1();
-- not ending, possibly due to table lock
-- back to session no.2
tmp1=# commit;
COMMIT
Tempo: 10,986 ms
-- back to session no.1
tx
-------
hello
(1 riga)
Tempo: 39946,137 ms
The result changes slightly if I query the function f1() just after ALTERing t1.
In this case from f1() I get NULL as result after COMMIT on session no.2.
A subsequent query returns 'hello'.
While from f2() I get always the right result.
This makes me think that the volatility specification in the function
declaration obviously changes something in the caching of the catalog
queries.
The NULL remains a mystere for me.
Any hint? Any way to avoid such a behaviour?
