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

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: Postgresql MVCC, Read Committed Isolation Level and taking "snapshot"
Date: 2008-05-16 07:06:11
Message-ID: 20080516090611.6133be00@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vance Maverick 2008-05-16 07:09:05 Re: triggers: dynamic references to fields in NEW and OLD?
Previous Message Anton Melser 2008-05-16 07:03:17 recommended way of separating data from indexes