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? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
