Re: Re: what's the exact command definition in read committed isolation level?

2016-04-18 23:16 GMT+08:00 Tom Lane <tgl@sss.pgh.pa.us>:
>
> No, that's not true: a trigger is a function and what it can see is
> determined by the rules of the PL it's written in.  Typically a
> function that's marked STABLE or IMMUTABLE will see the same snapshot
> as the calling query, but a function that's VOLATILE will take a new
> snapshot for each query it contains.

Thank you, Tom. I think I find the answer now.

Yes, for all the cases (trigger, CTE, other sub-query), as long as
they use function written in PL, marked as VOLATILE (by default), then
each query contained in the function may see new data.

http://www.postgresql.org/docs/current/static/xfunc-volatility.html

"STABLE and IMMUTABLE functions use a snapshot established as of the
start of the calling query, whereas VOLATILE functions obtain a fresh
snapshot at the start of each query they execute."

I think this page:
http://www.postgresql.org/docs/current/static/transaction-iso.html
should refer to above page to clarify the function affect.




Here is an example for CTE or other sub-query:

create or replace function test_fn() returns setof int as $$
begin
raise INFO 'sleep 30 secs, modify bar in another session...';
perform pg_sleep(30);
return query select * from bar;
end;
$$ language plpgsql;

=> create table foo(a int);
CREATE TABLE
=> insert into foo values(1);
INSERT 0 1
=> create table bar(a int);
CREATE TABLE
=> insert into foo values(100);
INSERT 0 1
=> with t as (select a from test_fn() as tt(a)) select * from foo, t
where foo.a = t.a;
INFO:  sleep 30 secs, modify bar in another session...
 a | a
---+---
(0 rows)

=> with t as (select a from test_fn() as tt(a)) select * from foo, t
where foo.a = t.a;
INFO:  sleep 30 secs, modify bar in another session...
<-------------------- run below command in another session:
<-------------------- insert into bar values(1);
  a  |  a
-----+-----
   1 |   1
 100 | 100
(2 rows)

=> select * from foo where exists (select a from test_fn() as tt(a)
where tt.a = foo.a);
INFO:  sleep 30 secs, modify bar in another session...
  a
-----
   1
 100
(2 rows)

=> select * from foo where exists (select a from test_fn() as tt(a)
where tt.a = foo.a);
INFO:  sleep 30 secs, modify bar in another session...
<-------------------- run below command in another session:
<-------------------- delete from bar where a = 1;
  a
-----
 100
(1 row)