From: Tom Lane <tgl@sss.pgh.pa.us>
To: mike@mikebrancato.com
cc: Christophe Pettus <xof@thebuild.com>, pgsql-bugs@lists.postgresql.org
Subject: Re: RLS creates inaccurate limit and offset results
In-reply-to: <E13131DB-28E5-4F05-A9D8-D54F886230C2@mikebrancato.com>
References: <9C0FBBAE-F65F-407D-B69A-B26CBC1AE35B@mikebrancato.com>
 <E02822A7-6755-476C-AE78-5DDBE69304C4@thebuild.com>
 <E13131DB-28E5-4F05-A9D8-D54F886230C2@mikebrancato.com>
Comments: In-reply-to mike@mikebrancato.com
	message dated "Tue, 11 Nov 2025 17:46:28 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2381085.1762972021.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 12 Nov 2025 13:27:01 -0500
Message-ID: <2381086.1762972021@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2381086.1762972021%40sss.pgh.pa.us>
Precedence: bulk

mike@mikebrancato.com writes:
> If I understand the documentation (https://www.postgresql.org/docs/curre=
nt/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