From: Tom Lane <tgl@sss.pgh.pa.us>
To: mike@mikebrancato.com
Cc: Christophe Pettus <xof@thebuild.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: RLS creates inaccurate limit and offset results
Date: Wed, 12 Nov 2025 13:27:01 -0500
Message-ID: <2381086.1762972021@sss.pgh.pa.us> (raw)
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>
mike@mikebrancato.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, mike@mikebrancato.com, xof@thebuild.com, pgsql-bugs@lists.postgresql.org
Subject: Re: RLS creates inaccurate limit and offset results
In-Reply-To: <2381086.1762972021@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox