2016-04-18 23:16 GMT+08:00 Tom Lane <tgl@xxxxxxxxxxxxx>: > > 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) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
