Are identical subqueries in unioned statements nonrepeatable?

From: Derrick Rice <derrick(dot)rice(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Are identical subqueries in unioned statements nonrepeatable?
Date: 2010-07-22 16:27:31
Message-ID: AANLkTinsmgu20TBIwU-A8WmuGWvpimNYN_L2txO-Vq9t@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Joseph Krogh 2010-07-22 17:32:42 Re: Clarification of the "simple" dictionary
Previous Message John Gage 2010-07-22 16:27:06 Re: Clarification of the "simple" dictionary