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

Reply via email to