Обсуждение: Read Committed Transaction Isolation and SELECT ... UNION ... SELECT
I think I've been studying the documentation too long and have thought myself into a circle. http://www.postgresql.org/docs/8.4/static/transaction-iso.html says: "Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes during execution of the first SELECT." I get that what that means in normal cases, but what about a single query comprised of one or more unions: SELECT ... FROM foo WHERE ... UNION SELECT ... FROM foo WHERE ... Since the above is one query issued by the client, are the two SELECT statements still operating within the same snapshot? Is the above considered to be one command? I think the answer to those questions is "Yes", but I'd appreciate some clarification. Thanks in advance! eric
Eric Ridge <eebbrr@gmail.com> writes: > http://www.postgresql.org/docs/8.4/static/transaction-iso.html says: > "Also note that two successive SELECT commands can see different data, > even though they are within a single transaction, if other > transactions commit changes during execution of the first SELECT." > I get that what that means in normal cases, but what about a single > query comprised of one or more unions: > SELECT ... FROM foo WHERE ... > UNION > SELECT ... FROM foo WHERE ... That's just one SELECT command. Sub-SELECTs inside a query don't count as separate commands for this purpose; the use of SELECT in that way is just an artifact of the SQL grammar. regards, tom lane
On Tue, Aug 10, 2010 at 6:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > That's just one SELECT command. Sub-SELECTs inside a query don't > count as separate commands for this purpose; the use of SELECT in > that way is just an artifact of the SQL grammar. Thanks. That's what I figured, but wanted to make sure. eric