2016-04-19 5:41 GMT+02:00 Jinhua Luo <luajit.io@xxxxxxxxx>:
> 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)
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
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)
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.
--
