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

From: Jinhua Luo <luajit(dot)io(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: what's the exact command definition in read committed isolation level?
Date: 2016-04-17 07:55:41
Message-ID: CAAc9rOz1TMme7NTb3NkvHiPjX0ckmC5UmFhadPdmXkmxagco7w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jony Cohen 2016-04-17 08:23:33 Re: understanding postgres backend process memory usage
Previous Message Adrian Klaver 2016-04-16 23:09:34 Re: pg_basebackup: return value 1: reason?