Re: Synchronized scans

"Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:

> LIMIT without ORDER BY is worse because it not only returns tuples in different
> order, but it can return different tuples altogether when you run it multiple
> times.

I don't think printing a notice is feasible. For regular DML a notice or info
message is basically equivalent to an error. It makes it entirely impractical
to use such a query in a production system where it will spam your logs with
thousands of messages.

Now it might be worth considering making this an error. We already make it an
error to have DISTINCT ON without an matching ORDER BY or a full outer join
without a merge-joinable operator. Both of those are really implementation
limitations but they're also arguably conceptual limitations in that it's hard
(though not impossible) to imagine it working any differently.

However... there are circumstances where having non-deterministic queries is
perfectly reasonable. Perhaps you're implementing a work queue and want to
process a batch of records but don't care which. Or perhaps you want to cap
the number of records a search result will display to a reasonable value that
won't cause problems but you expect under normal conditions not to reach that
limit.

There are also cases where people use OFFSET and LIMIT with degenerate values
(either OFFSET 0 or LIMIT <bignum>) to induce the planner to plan queries
differently knowing that it won't actually change the results.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com