Thread: Are identical subqueries in unioned statements nonrepeatable?
Hi all. I had no luck finding a previous message or documentation related to the effective transaction isolation of subqueries, specifically identical subqueries in union statements. Consider the following statement executed without a transaction.
select true as from_one, table_one.*
from table_one
where table_one.id not in (select id from reference_table)
union all
select false as from_one, table_two.*
from table_two
where table_two.id not in (select id from reference_table)
Is it possible for the contents of reference_table to differ from the first select to the select on the right hand side of the union? (e.g. because some other transaction committed additional rows). Or even from row comparison to row comparison in the same select (I highly doubt that).
If it is not possible, why? Is it because a single query always executes with serializable (effective) isolation? Is it because postgresql recognizes that the query is repeated and uses a single result set in both sides of the union?
Is this behavior that is part of postgresql intentionally, or a side effect that I should not rely on?
Assumption: I'm assuming that it's faster to union all after filtering by the where clause than to union all then filter by a single where clause. The subquery for exclude_ids is very fast and the results of each of the selects is a small fraction of the entire tables. Doing a union first would be expensive in comparison to doing a union of the
Thanks,
Derrick
select true as from_one, table_one.*
from table_one
where table_one.id not in (select id from reference_table)
union all
select false as from_one, table_two.*
from table_two
where table_two.id not in (select id from reference_table)
Is it possible for the contents of reference_table to differ from the first select to the select on the right hand side of the union? (e.g. because some other transaction committed additional rows). Or even from row comparison to row comparison in the same select (I highly doubt that).
If it is not possible, why? Is it because a single query always executes with serializable (effective) isolation? Is it because postgresql recognizes that the query is repeated and uses a single result set in both sides of the union?
Is this behavior that is part of postgresql intentionally, or a side effect that I should not rely on?
Assumption: I'm assuming that it's faster to union all after filtering by the where clause than to union all then filter by a single where clause. The subquery for exclude_ids is very fast and the results of each of the selects is a small fraction of the entire tables. Doing a union first would be expensive in comparison to doing a union of the
Thanks,
Derrick
Excerpts from Derrick Rice's message of jue jul 22 12:27:31 -0400 2010: > Is it possible for the contents of reference_table to differ from the first > select to the select on the right hand side of the union? (e.g. because > some other transaction committed additional rows). No. > If it is not possible, why? Is it because a single query always executes > with serializable (effective) isolation? Yes. (Actually: it's because a query is always executed with a single snapshot). > Is it because postgresql > recognizes that the query is repeated and uses a single result set in both > sides of the union? No. > Is this behavior that is part of postgresql intentionally, or a side effect > that I should not rely on? It is intentional and will not be changed.