My target is to take a snapshot of a slice of some tables and put the result in a log, regardless of writes made after I started to take the snapshot. something like: create or replace function snapshot(out stats int) as $$ begin -- prepare stuff from t1, t2, t3 -- save it in other tables return; end; $$ language plpgsql; suppose I do something like: update t1 set col1=7 where col2=5; select stats from snapshot(); update t2 set col4=2 where col1=3; from different connections but starting in that temporal order and that snapshot is still running when the second update is fired. I'd like snapshot() seeing what the first update did but NOT see what the second update is doing. I'd expect this to be "auto-magic" according to 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. but: 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; I'd expect this function to return (1,1) or (5,5) and never (1,5). Then I read: http://www.postgresql.org/docs/8.1/static/transaction-iso.html#XACT-READ-COMMITTED Notice that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes during execution of the first SELECT. Is BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE what I'm looking for? -- Ivan Sergio Borgonovo http://www.webthatworks.it
