On Tue, 13 May 2008 22:51:00 -0400 Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > "Vyacheslav Kalinin" <vka@xxxxxxxx> writes: > > Reading the manual recently I came across this: ( > > http://www.postgresql.org/docs/8.3/interactive/xfunc-volatility.html ) > >> Because of the snapshotting behavior of MVCC (see Chapter > >> 13<http://www.postgresql.org/docs/8.3/interactive/mvcc.html>) > > a function containing only SELECT commands can safely be marked > >> STABLE, even if it selects from tables that might be undergoing > > modifications by concurrent queries. PostgreSQL will execute a > > STABLE > >> function using the snapshot established for the calling query, > >> and so it > > will see a fixed view of the database throughout that query. > > > It stroke me that it might be not all that safe to mark SELECTing > > only function STABLE vs VOLATILE (or vice versa). > > What it says is that you *can* mark such a function stable, without > violating the rules for a stable function. It doesn't say that this > choice doesn't affect the results. Feel free to propose better > wording... I'm confused... Actually f1 seems to be stable. the insert is executed "outside" the function. My understanding is that immutable, stable and volatile are hints for the optimizer. Results from an immutable function could be cached across the whole life of the DB if input parameters are the same. insert into t (a,b) values(5,fi(3)); insert (a,b) values(7,fi(3)); fi *could* be executed just one time. Results from a stable function could be cached across a statement. insert into t (a,b) values(fs(3),fs(3)); fs *could* be executed just one time. Inside *any* function selects will see the snapshot and the modification made inside the function since function are executed inside an implicit transaction. Now I read: http://searchwarp.com/swa9860.htm Read Committed Isolation Level Read Committed is the default isolation level in PostgreSQL. When a transaction runs on this isolation level, a SELECT query sees only data committed before the query began it never sees either uncommitted data or changes committed during query execution by concurrent. create table t1(a int); insert into t1 values(1); create or replace function ft(out a1 int, out a2 int) as $$ begin select into a1 a from t1 limit 1; for i in 1..700000000 loop end loop; select into a2 a from t1 limit 1; return; end; $$ language plpgsql; select * from ft(); update t1 set a=5; So I'd expect ft() return always (1,1) or (5,5). Since select * from ft(); is one statement... it should see only data that were committed when select started. But actually I can obtain (1,5) ??? -- Ivan Sergio Borgonovo http://www.webthatworks.it
