Let me clarify my question a bit more (I don't know why nobody raises
such question):
For trigger, e.g. written in pl/pgsql, each sql command within the
function may see more new data beyond the (entry) snapshot of outer
command.
So if the "command" term in the read committed isolation level only
refers to outer command (the outer command is the top level command
send by session client), then it's wrong obviously, so it should
clarify that the trigger is an rule exception, in other words, the
commands in trigger should be considered as virtual "outer" commands,
just like you inline the trigger body below the outer command.
But the document doesn't cover this important topic.
Here is a simple example:
create table foo (a int);
create or replace function foo_trigger() returns trigger as $$
declare
v_rec record;
begin
raise NOTICE 'before bar update:';
for v_rec in select a from bar loop
raise NOTICE '%', v_rec.a;
end loop;
perform pg_sleep(30);
raise NOTICE 'after bar update:';
for v_rec in select a from bar loop
raise NOTICE '%', v_rec.a;
end loop;
return NEW;
end;
$$ language plpgsql;
create trigger foo_trigger_01 before insert on foo for each row
execute procedure foo_trigger();
create table bar (a int);
insert into bar values(100);
=> insert into foo values(1);
NOTICE: before bar update:
NOTICE: 100
<----- when this trigger sleeps,
<----- switch to another login session, run insert into bar values(999);
<----- after 30 seconds, check the first session output:
NOTICE: after bar update:
NOTICE: 100
NOTICE: 999
INSERT 0 1
In the source codes of postgresql, the plpgsql uses SPI to run
statement, you could also see that each statement get new snapshot, so
it would see new data from committed transaction up to that instant:
_SPI_execute_plan() -->
/*
* In the default non-read-only case, get a new snapshot, replacing
* any that we pushed in a previous cycle.
*/
if (snapshot == InvalidSnapshot && !read_only)
{
if (pushed_active_snap)
PopActiveSnapshot();
PushActiveSnapshot(GetTransactionSnapshot());
pushed_active_snap = true;
}
And, I am not sure CTE and other forms of sub-query cases. But
theoretically, they're part of the outer command, so I think they
should see the same data of outer command.
Anybody know the correct answer?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
