Re: Couple of question on functions

On Tue, 13 May 2008 22:51:00 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Vyacheslav Kalinin" <vka(at)mgcp(dot)com> 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