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
