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

2016-04-19 5:41 GMT+02:00 Jinhua Luo <luajit(dot)io(at)gmail(dot)com>:

> > 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.
>
>
Well, that's not specific to the trigger. Try this:

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.

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com