Re: Reasons to reorder results *within* a transaction?

Evan Jones <ej(at)evanjones(dot)ca> wrote:

> I *know* that without an ORDER BY clause, the database is free to reorder
> results in any way it likes. However, I recently ran into a case where the
> *SAME* query was returning results in a different order *within* a single
> transaction, which surprised me (we fixed it by adding the missing ORDER BY). I
> would assume that once a transaction obtains a snapshot, all its read operations
> would return the same results.

That is not a valid assumption.  For one thing, the default
transaction isolation level is read committed, and at that
isolation level you are not guaranteed to even get the same *rows*
running the same query twice within the same transaction, much less
in the same order.  At any isolation level statistics could change,
resulting in a different plan on two successive executions.  Even
running the same plan using the same snapshot you could get a
different order if you have not specified one with ORDER BY.  As
one example, a sequential scan of a table won't necessarily start
at the beginning of the heap -- if there is already a sequential
scan in progress for another process, the new one will start at the
point the other one is at, and "wrap around".  This can save a lot
of physical disk access, resulting in better performance.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company