Re: RLS creates inaccurate limit and offset results

mike(at)mikebrancato(dot)com writes:
> If I understand the documentation (https://www.postgresql.org/docs/current/queries-limit.html) inconsistent results for different LIMIT / OFFSET values is only a known issue when selecting different subsets of data.

You are willfully ignoring the lede on that page:

When using LIMIT, it is important to use an ORDER BY clause that
constrains the result rows into a unique order. Otherwise you will
get an unpredictable subset of the query's rows.

This has nothing particularly to do with RLS; RLS is merely one way in
which a query might gain complications sufficient to cause the planner
to change plans. Without ORDER BY, the planner is free to switch to
a plan that delivers rows in some other order. Even just modifying
OFFSET can cause that to happen (since it's effectively a change in
the number of rows required to be fetched).

I suspect if you check with EXPLAIN, you'll find that you get
different plans with the different OFFSET values. But whatever the
details, your code was making unjustified assumptions.

regards, tom lane