pbelbin@gmail.com writes:
> ... populated with rows where many of the 'code' rows contain the same value,
> and then queried with something like:
> select * from proxy_homing order by code limit 10 offset 40
> is returning the exact same result in the last row, regardless of the
> offset.
Sorry, this is not a bug. If you have an underspecified ORDER BY
ordering, the sorting code is entitled to return equal-keyed rows
in any order whatsoever, and there is no guarantee that changing
the limit/offset parameters won't affect that.
> changing the sorting so that it includes the match column appears to avoid
> the issue, but, this is a bug. each row should only appear once if the
> limit/offset values are looking at different portions of the result that
> would be produced without the limit/offset options.
We do not make any guarantee that that is how limit/offset works. You
will only get consistent results across queries if the underlying result
ordering is guaranteed consistent across queries, but that's not the case
in this example. Postgres would be within its rights to return different
rowsets on different executions even without any change in limit/offset,
and indeed it can do so in some cases.
(The technical reason why this happens in this particular case is that
when using a bounded heap to do a top-N sort, which of the equal-keyed
tuples survive to the end of the sort depends heavily on the exact heap
size, ie the LIMIT+OFFSET sum; and the order in which the survivors end
up getting output is also dependent on the exact heap size. But there
are other mechanisms that could cause the results to be unstable.)
regards, tom lane
