Обсуждение: what's the exact command definition in read committed isolation level?

Hi All,

The document said, "Read Committed mode starts each command with a new
snapshot that includes all transactions committed up to that instant".

But what about the embedded commands within the outer command itself?
Do they share the same snapshot with the outer command?

a) trigger

This is the case I am sure it would break/extend the read committed
isolation level. When the outer command triggers a trigger function,
each command within the function would own new snapshot that includes
all transactions committed up to that instant, that means it would see
new data beyond the transaction of the outer command.

b) CTE

It seems that all WITH sub-queries share the same snapshot of the main
query, but I am not sure.

c) other forms of sub-query

e.g. sub-query in WHERE part,

select * from foo where col1 > any(select col1 from bar where ....);

If some other transaction committed after the second run of subquery,
which modified table bar, does the third run of subquery see the new
data?

I could not find any answer from the official documents, so could
anybody help to answer them?

Thanks!

Regards,
Jinhua Luo

Jinhua Luo wrote:
> The document said, "Read Committed mode starts each command with a new
> snapshot that includes all transactions committed up to that instant".
> 
> But what about the embedded commands within the outer command itself?
> Do they share the same snapshot with the outer command?
> 
> a) trigger
> b) CTE
> c) other forms of sub-query

All these share the snapshot of the command to which they belong.

As you wrote, things would break if it were different.

Yours,
Laurenz Albe

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?

Jinhua Luo schrieb am 18.04.2016 um 16:47:
> 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.

No it will not see "more data")

It runs in the same _transaction_ as the "firing" command and thus sees
**exactly** the same data as the triggering statement

> 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.

The visibility of data with regards to isolation levels is all about _transactions_ - **not** statements.

Just because you run in auto-commit mode doesn't mean this changes.

With autocommit enabled, the first (or outer) statement starts a _transaction_ and that transaction only ends when
**that**statement is finished.  

Any statement that is execute because e.g. a trigger is fired or the statement calls a function that contains several
statementsis still part of that _transaction_.  

Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:
> Jinhua Luo schrieb am 18.04.2016 um 16:47:
>> 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.

> No it will not see "more data")

> It runs in the same _transaction_ as the "firing" command and thus sees
> **exactly** the same data as the triggering statement

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.

            regards, tom lane

Tom Lane schrieb am 18.04.2016 um 17:16:
>>> 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.
>
>> No it will not see "more data")
>
>> It runs in the same _transaction_ as the "firing" command and thus sees
>> **exactly** the same data as the triggering statement
>
> 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.

Does that mean a VOLATILE function runs in a different transaction?

And does that mean it will see committed data that the calling statement would not see?

> Does that mean a VOLATILE function runs in a different transaction?

No, all statements triggered by the outer statement is within the same
transaction. If the trigger fails (without trapping the error), all
affects including changes by outer statement would be rollback.

> And does that mean it will see committed data that the calling statement
> would not see?

Yes, that's what I said. The trigger is special, each statement within
it get new snapshot so it would see data from all committed
transactions up to its execution instant. But that doesn't mean the
trigger runs in different transaction.

Please check my example above, and try it yourself.

>
> First session:
> postgres=# create table t(id integer);
> CREATE TABLE
> postgres=# insert into t values (1);
> INSERT 0 1
> postgres=# begin;
> BEGIN
> postgres=# select count(*) from t;
>  count
> -------
>      1
> (1 row)
>
> 1 row in the table. In another session, insert another row in t:
>
> postgres=# insert into t values (2);
> INSERT 0 1
>
> And, then, back to the first session, still inside the previous transaction:
>
> postgres=# select count(*) from t;
>  count
> -------
>      2
> (1 row)
>
> That's how Read Committed behaved. You see changes commited by other
> transactions, even transactions started after yours. That has nothing to do
> with triggers.

Yes, this is exactly how document tells us. The outer statement
("top-level" statements from client session) is of course one of the
cases which satisfies the read committed isolation level rules. It's
easy to understand and well-documented.

But remind that I am talking about the embedded statements triggered
by the outer statement (which you cannot see them in explicit way, you
need to check whether some trigger be set, and what statements
contained in the trigger). That's the document doesn't clarify.

Theoretically and literally, the trigger should be considered as part
of the outer command, just like CTE and other forms of sub-query, but
in fact, it is not! Instead, the commands in trigger should be
considered as virtual "outer" commands, just like you inline the
trigger body after the outer command. So that's why I said trigger is
an exception, which break/extend the rule.

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)