Re: [HACKERS] Solution for LIMIT cost estimation

At 02:27 PM 2/14/00 -0500, Tom Lane wrote:

>CAUTION: if you repeat a query several times with different OFFSET or
>LIMIT values to fetch different portions of the whole result, you will
>find that you get inconsistent results unless you specify an ORDER BY
>condition that is strong enough to ensure that all selected tuples must
>appear in a unique order. Without ORDER BY, the system is free to
>return the tuples in any order it finds convenient

Personally, I would generalize this and leave out the reference to
LIMIT and OFFSET, except perhaps to point out that this is one
particular construct that confuses people.

As PG matures, so will the optimizer and query engine, and people
who've written code that depends on tuples being returned in a
single consistent order might find themselves in for a rude shock.

A well-deserved one (IMO), but still a shock.

The documentation won't stop most people who want to do this
from doing so, they'll test and try to "trick" the system by
taking advantage of behavior that might not be consistent in
future releases.

Still...if it stops even ONE person from doing this, the doc will
do some good.

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.