Re: BUG #19827: postgres_fdw: Assert in add_foreign_ordered_paths() with redundant ORDER BY in set-op input
Re: BUG #19827: postgres_fdw: Assert in add_foreign_ordered_paths() with redundant ORDER BY in set-op input
Hi, > It fails on master and REL_17_STABLE to REL_19_STABLE; 16 has no > setop_pathkeys. On 17 only UNION inputs trip it; from 18 EXCEPT does > too (and INTERSECT, tried on master). A patch follows in a reply. > > The patch (in a reply to this report), is against master 6cda9dfc4f97, > and applies through to REL_17_STABLE. In add_foreign_ordered_paths(), > the base/join branch now handles the set-operation case first: Here's the patch that fixes the issue. It applies from HEAD through REL_17_STABLE. make check passes too. - robins | robins.in
Re: BUG #19827: postgres_fdw: Assert in add_foreign_ordered_paths() with redundant ORDER BY in set-op input
On Tue, 3 Nov 2026 at 05:09, Robins Tharakan wrote: > > It fails on master and REL_17_STABLE to REL_19_STABLE; 16 has no > > setop_pathkeys. On 17 only UNION inputs trip it; from 18 EXCEPT does > > too (and INTERSECT, tried on master). A patch follows in a reply. > > > > The patch (in a reply to this report), is against master 6cda9dfc4f97, > > and applies through to REL_17_STABLE. In add_foreign_ordered_paths(), > > the base/join branch now handles the set-operation case first: > > > Here's the patch that fixes the issue. > It applies from HEAD through REL_17_STABLE. make check passes too. (I don't seem to have any emails to see what you're replying to here, and [1] doesn't seem to have any matches apart from your email.) I think we should fix this in the backbranches by modifying the Assert to allow sort_pathkeys to be NIL. The human-done v2-0001 patch does this, plus fixes the incorrect comment just above that was claiming query_pathkeys must be the same as sort_pathkeys in this scenario. That should have been updated when I added setop_pathkeys in v17. Fixing it this way won't result in any plan changes for existing queries, which is imporant for backpatching. For master, I propose v2-0002 (also human-done). This effectively does the same as your v1. What you had was quite clanky which seems to have resulted in weird logic. I think it's fairly simple; if sort_pathkeys is NIL, just set fpinfo->pushdown_safe to true, otherwise we can expect sort_pathkeys to be equal to query_pathkeys. David [1] https://www.postgresql.org/search/?m=1&q=19827&l=8&d=-1&s=d
Re: BUG #19827: postgres_fdw: Assert in add_foreign_ordered_paths() with redundant ORDER BY in set-op input
От:
Robins Tharakan <tharakan@gmail.com>
Дата:
Hi David,
Thanks for taking a look.
On Tue, 3 Nov 2026 at 11:44, David Rowley wrote:
> On Tue, 3 Nov 2026 at 05:09, Robins Tharakan wrote:
> >
> > Here's the patch that fixes the issue.
> > It applies from HEAD through REL_17_STABLE. make check passes too.
>
> (I don't seem to have any emails to see what you're replying to here,
> and [1] doesn't seem to have any matches apart from your email.)
It is odd that despite receiving an email via the web-form submission process,
it is missing from the pgsql-bugs web-portal today. At this point, it seems like
a bug with the web-form / mailing system.
Anyway, below is the verbatim copy of what I received from the
pgsql-bugs web-form submission that has my original triaging and
testing details.
"
PG Bug reporting form
02:14 (11 hours ago)
to pgsql-bugs, me
The following bug has been logged on the website:
Bug reference: 19827
Logged by: Robins Tharakan
Email address: tharakan@gmail.com
PostgreSQL version: 19rc1
Operating system: Debian 13 (x86_64)
Description:
A set-operation input that scans a postgres_fdw foreign table and has
an ORDER BY whose keys are all redundant (a constant, or a column the
WHERE clause equates to a constant) fails this assertion:
TRAP: failed Assert("root->query_pathkeys == root->sort_pathkeys"),
File: "postgres_fdw.c", Line: 8280
Release builds return correct rows (checked against the local table).
add_foreign_ordered_paths() asserts that query_pathkeys is
sort_pathkeys for a base or join rel. Since 66c0185a3,
standard_qp_callback() falls back to setop_pathkeys when sort_pathkeys
is NIL, for an input of a set operation. A redundant ORDER BY leaves
sort_pathkeys NIL while the query still has a sortClause, so
create_ordered_paths() still calls the FDW for UPPERREL_ORDERED.
It fails on master and REL_17_STABLE to REL_19_STABLE; 16 has no
setop_pathkeys. On 17 only UNION inputs trip it; from 18 EXCEPT does
too (and INTERSECT, tried on master). A patch follows in a reply.
SQL
===
CREATE EXTENSION postgres_fdw;
DO $$
BEGIN
EXECUTE format('CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname %L, port %L)',
current_database(), current_setting('port'));
END
$$;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
CREATE TABLE t (a int, b bool);
INSERT INTO t VALUES (1, true), (2, false);
CREATE FOREIGN TABLE ft (a int, b bool)
SERVER loopback OPTIONS (table_name 't');
(SELECT a, b FROM ft WHERE a = 1 ORDER BY a) UNION (SELECT 2, true);
-- from 18, also:
(SELECT 1 AS x, b FROM ft ORDER BY 1) EXCEPT (SELECT 1, true);
Commit
======
6cda9dfc4f97 (master) — fails here
Reproduced starting from 12933dc6048, a re-commit of 66c0185a3.
Built with --enable-cassert and -O2 (gcc 14.2, Debian 13, x86_64).
Backtrace
=========
#4 add_foreign_ordered_paths (...) at postgres_fdw.c:8280
#5 postgresGetForeignUpperPaths (root=..., stage=UPPERREL_ORDERED, ...)
at postgres_fdw.c:8119
#6 create_ordered_paths (...) at planner.c:5784
#7 grouping_planner (...) at planner.c:2160
#8 subquery_planner (..., plan_name="setop_1", ...) at planner.c:1367
#9 recurse_set_operations (...) at prepunion.c:252
#10 generate_nonunion_paths (...) at prepunion.c:1091
#11 recurse_set_operations (...) at prepunion.c:287
#12 plan_set_operations (root=...) at prepunion.c:164
(EXCEPT case; UNION goes through generate_union_paths)
Patch
=====
The patch (in a reply to this report), is against master 6cda9dfc4f97,
and applies through to REL_17_STABLE. In add_foreign_ordered_paths(),
the base/join branch now handles the set-operation case first:
+ if (root->query_pathkeys != root->sort_pathkeys)
+ {
+ Assert(root->sort_pathkeys == NIL &&
+ root->query_pathkeys ==
root->setop_pathkeys);
+ fpinfo->pushdown_safe = true;
+ return;
+ }
Why that patch looks correct:
- For a base or join rel, query_pathkeys can differ from sort_pathkeys
only through the setop_pathkeys fallback: GROUP BY, window functions
or DISTINCT would make the ordered stage's input an upper rel. The
new Assert checks that.
- With sort_pathkeys NIL there's no final sort, so the ordered rel is
safe to push down, as the grouping-rel path below already decides
when sort_pathkeys is NIL.
- Release builds took pushdown_safe from qp_is_pushdown_safe, computed
for setop_pathkeys, so a LIMIT in the input was pushed down only if
the set operation's order could be. Results were right either way.
The one plan change: such an input with a LIMIT whose set-operation
order can't be shipped now pushes the LIMIT down without ORDER BY,
which is fine since every row ties on the redundant keys.
- A top-level query with an all-redundant ORDER BY is unchanged:
query_pathkeys is NIL there too.
The test adds five queries to postgres_fdw.sql: UNION inputs over a
base rel, over a join and with a LIMIT, an EXCEPT input and an
INTERSECT input. They check rows, not plans, so one expected output
fits every branch.
To keep back-branch plans exactly as they are, set pushdown_safe from
qp_is_pushdown_safe in the new block instead; it's a one-line change.
Testing
=======
On the tips of master (6cda9dfc4f97), REL_19_STABLE (4942dc745cf1),
REL_18_STABLE (19a2002b5db9) and REL_17_STABLE (9c44922d125c), with
assertions: the patch applies and compiles with no warning in
postgres_fdw.c, the core regression tests and postgres_fdw's pass, and
with the postgres_fdw.c change reverted, the new test hits this
assertion.
By hand, plans match an unpatched release build's except in that one
case. For
(SELECT a, b::text COLLATE "C" AS c FROM ft
WHERE a = 1 ORDER BY a LIMIT 1) UNION (SELECT 2, 'x');
the patched build sends
SELECT a, b FROM public.t WHERE ((a = 1)) LIMIT 1::bigint
where the unpatched build applies the LIMIT locally. The rows are the
same.
Found using SQLsmith and Claude.
-
Robins Tharakan
"
-
Robins
Databricks
Re: BUG #19827: postgres_fdw: Assert in add_foreign_ordered_paths() with redundant ORDER BY in set-op input
От:
Robins Tharakan <tharakan@gmail.com>
Дата:
Hi David,
On Tue, 3 Nov 2026 at 11:44, David Rowley <dgrowleyml@gmail.com> wrote:
> I think we should fix this in the backbranches by modifying the Assert
> to allow sort_pathkeys to be NIL.
On Tue, 3 Nov 2026 at 11:44, David Rowley <dgrowleyml@gmail.com> wrote:
> I think we should fix this in the backbranches by modifying the Assert
> to allow sort_pathkeys to be NIL.
> Fixing it this way won't result in any plan changes for existing
> queries, which is imporant for backpatching.
+1. Agree with the need to ensure back branches' plans don't change here.
On a quick check, both v2 patches tested fine for me:
- v2-0001 was tested on REL_17_STABLE .. REL_19_STABLE. It applies,
compiles with no warnings, and make check / postgres_fdw's
tests pass.
- Similarly v2-0001 + v2-0002 on master passes both tests too.
-
Robins
> queries, which is imporant for backpatching.
+1. Agree with the need to ensure back branches' plans don't change here.
On a quick check, both v2 patches tested fine for me:
- v2-0001 was tested on REL_17_STABLE .. REL_19_STABLE. It applies,
compiles with no warnings, and make check / postgres_fdw's
tests pass.
- Similarly v2-0001 + v2-0002 on master passes both tests too.
-
Robins
Re: BUG #19827: postgres_fdw: Assert in add_foreign_ordered_paths() with redundant ORDER BY in set-op input
Hi,
On Tue, 3 Nov 2026 at 11:44, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Tue, 3 Nov 2026 at 05:09, Robins Tharakan <tharakan@gmail.com> wrote:
> >
> > Here's the patch that fixes the issue.
> > It applies from HEAD through REL_17_STABLE. make check passes too.
>
> (I don't seem to have any emails to see what you're replying to here,
> and [1] doesn't seem to have any matches apart from your email.)
On Tue, 3 Nov 2026 at 11:44, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Tue, 3 Nov 2026 at 05:09, Robins Tharakan <tharakan@gmail.com> wrote:
> >
> > Here's the patch that fixes the issue.
> > It applies from HEAD through REL_17_STABLE. make check passes too.
>
> (I don't seem to have any emails to see what you're replying to here,
> and [1] doesn't seem to have any matches apart from your email.)
This conversation has moved to a separate thread, I’m sending this just for
the record.
I've sent a copy of my original email to David / Etsuro directly, but I'm making
a short-note here that the mailing-list filters are blocking the original
contents of this email for some reason (or slowing delivery). I've tried sending
the original email twice and both times, they haven't (yet) shown up on the
mailing-list portal [1] although mails before / after already do.
Attached is a screenshot of the email from the web-form submission email
with the Bug ID 19827.
-
Robins
Ref:
Re: BUG #19827: postgres_fdw: Assert in add_foreign_ordered_paths() with redundant ORDER BY in set-op input
От:
David Rowley <dgrowleyml@gmail.com>
Дата:
On Wed, 4 Nov 2026 at 00:23, Robins Tharakan wrote: > On Tue, 3 Nov 2026 at 11:44, David Rowley wrote: > > I think we should fix this in the backbranches by modifying the Assert > > to allow sort_pathkeys to be NIL. > > > Fixing it this way won't result in any plan changes for existing > > queries, which is imporant for backpatching. > > +1. Agree with the need to ensure back branches' plans don't change here. > On a quick check, both v2 patches tested fine for me: > > - v2-0001 was tested on REL_17_STABLE .. REL_19_STABLE. It applies, > compiles with no warnings, and make check / postgres_fdw's > tests pass. > - Similarly v2-0001 + v2-0002 on master passes both tests too. Thanks for having a look. I've now backpatched 0001 and applied 0002 to master. David
Re: BUG #19827: postgres_fdw: Assert in add_foreign_ordered_paths() with redundant ORDER BY in set-op input
От:
Álvaro Herrera <alvherre@kurilemu.de>
Дата:
On 2026-Nov-03, Robins Tharakan wrote: > On Tue, 3 Nov 2026 at 11:44, David Rowley wrote: > > (I don't seem to have any emails to see what you're replying to here, > > and [1] doesn't seem to have any matches apart from your email.) > > It is odd that despite receiving an email via the web-form submission process, > it is missing from the pgsql-bugs web-portal today. At this point, it seems like > a bug with the web-form / mailing system. I'm sure there are bugs, but this is not one of them. It's just that all pgsql-bugs submissions via the web interface go through human moderation; but you (as submitter) also got the copy that we CC to you directly. The pgsql-bugs one was not approved until a few hours later, but David had already received the direct CC reply that you sent quoting the direct CC you had received, before any pgsql-bugs moderators had a chance to approve the original email. These emails have now been approved and the entirety of the thread can be seen in the pgsql-bugs archives. https://www.postgresql.org/message-id/flat/19827-6e0bc0f9a5226e71%40postgresql.org Please don't cry wolf so eagerly, but thanks for reporting Postgres bugs. Regards -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "Postgres is bloatware by design: it was built to house PhD theses." (Joey Hellerstein, SIGMOD annual conference 2002)
Re: BUG #19827: postgres_fdw: Assert in add_foreign_ordered_paths() with redundant ORDER BY in set-op input
От:
Robins Tharakan <tharakan@gmail.com>
Дата:
Hi Alvaro,
On Wed, 4 Nov 2026 at 20:45, Álvaro Herrera <alvherre@kurilemu.de> wrote:
On 2026-Nov-03, Robins Tharakan wrote:
> On Tue, 3 Nov 2026 at 11:44, David Rowley <dgrowleyml@gmail.com> wrote:
> > (I don't seem to have any emails to see what you're replying to here,
> > and [1] doesn't seem to have any matches apart from your email.)
>
> It is odd that despite receiving an email via the web-form submission process,
> it is missing from the pgsql-bugs web-portal today. At this point, it seems like
> a bug with the web-form / mailing system.
I'm sure there are bugs, but this is not one of them.
It's just that all pgsql-bugs submissions via the web interface go
through human moderation; but you (as submitter) also got the copy that
we CC to you directly. The pgsql-bugs one was not approved until a few
hours later, but David had already received the direct CC reply that you
sent quoting the direct CC you had received, before any pgsql-bugs
moderators had a chance to approve the original email.
These emails have now been approved and the entirety of the thread can
be seen in the pgsql-bugs archives.
https://www.postgresql.org/message-id/flat/19827-6e0bc0f9a5226e71%40postgresql.org
Thanks, this explanation makes perfect sense. My apologies for
the confusion. I'll await the mailing-list web portal to show up the
original bug-submission (so that I can be sure everyone's got a
copy) before posting follow-ups.
-
Robins