Let's assume we use autocommit mode, i.e.
By default (without BEGIN), PostgreSQL executes transactions in "autocommit" mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).
Then with read committed isolation level (by default in postgresql too), the statement is the actual isolation unit. But here the statement only means outer statement? What about the statements embedded in the same outer statement?
- CTE (With queries)
I test some cases, it seems that the main query and all WITH queries share the same transaction snapshot, although some query may happen after the other due to dependency.
with a as
(
statements...
),
b as (
statements depends on a, e.g. from a,
)
main query
if during the whole query running, new transaction committed, should any (sub-)query here see new data?
- statements in trigger function
It's easy to test and know that each statement within the trigger function satisfy the read committed isolation level rules, i.e. each statement would see data from all committed transactions just before it start execution.
outer dml statement ----trigger---> foobar()
statement1
statement2
....
if some other transaction committed after statement1, the statement2 would see the new data.
- subquery
Consider the subquery in WHERE part, it's hard to test if each execution of subquery would see new data from other committed transactions, because the subquery would be converted into join by optimizer mostly.
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 haven't read the source codes of postgresql according to this topic, does anybody know the answer?
