Search Postgresql Archives

Are identical subqueries in unioned statements nonrepeatable?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux