Search Postgresql Archives

Postgresql MVCC, Read Committed Isolation Level and taking "snapshot"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux