postgres.git / summary / log / commit / refs

commit    edbb2813746de72e0a7ae20bd7ebe5b37fb78b23
Author:   David Rowley <drowley@postgresql.org>
Date:     Wed Nov 04 02:56:46 2026 +0000
First released in: 17.12, 18.7, 19.0

    Fix incorrect Assert in add_foreign_ordered_paths
    
    12933dc60 (originally 66c0185a3) added setop_pathkeys for providing
    presorted inputs for set operation from child queries when that query
    doesn't have any other pathkey requirements.
    
    add_foreign_ordered_paths() assumed that if the query had an ORDER BY
    that the query_pathkeys would be equal to sort_pathkeys.  That's not
    true when query_pathkeys is set to setop_pathkeys, which can happen
    for set-op child queries with an ORDER BY when all the sort_pathkeys are
    redundant pathkeys. (See standard_qp_callback()).
    
    Here we relax the Assert to allow for sort_pathkeys to be empty.  In
    this case it is technically ok to mark pushdown_safe as true so that
    postgresGetForeignUpperPaths() allows add_foreign_final_paths() to
    generate paths with the LIMIT clause pushed down.  We don't adjust that
    here as that could cause plan changes in the backbranches.  Let's just
    fix things enough so the Assert doesn't fail and do a proper fix in
    master.
    
    Backpatch to 17 where the setop_pathkeys field was added.
    
    Reported-by: Robins Tharakan <tharakan@gmail.com>
    Author: David Rowley <dgrowleyml@gmail.com>
    Reviewed-by: Robins Tharakan <tharakan@gmail.com>
    Discussion: https://postgr.es/m/CAEP4nAwnSefmi4w+89XbHQ0f-0OixCYUJo9kVcbXSYL8L4rEPg@mail.gmail.com
    Backpatch-through: 17


contrib/postgres_fdw/expected/postgres_fdw.out | 26 ++++++++++++++++++++++++++ contrib/postgres_fdw/postgres_fdw.c | 16 ++++++++-------- contrib/postgres_fdw/sql/postgres_fdw.sql | 9 +++++++++ 3 files changed, 43 insertions(+), 8 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 86dbf74d64f..7c120647c53 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -1279,6 +1279,32 @@ SELECT * FROM ( Remote SQL: SELECT "C 1" FROM "S 1"."T 1" (8 rows) +-- Ensure the code works correctly when sort_pathkeys is empty due to +-- a redundant pathkey. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM ( + (SELECT c1, c2 FROM ft1 WHERE c1 = 0 ORDER BY c1 LIMIT 1) + UNION + (SELECT c1, c2 FROM ft1 WHERE c1 = 0 ORDER BY c1) +) a; + QUERY PLAN +--------------------------------------------------------------------------------------------- + Unique + Output: ft1.c1, ft1.c2 + -> Sort + Output: ft1.c1, ft1.c2 + Sort Key: ft1.c1, ft1.c2 + -> Append + -> Limit + Output: ft1.c1, ft1.c2 + -> Foreign Scan on public.ft1 + Output: ft1.c1, ft1.c2 + Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE (("C 1" = 0)) + -> Foreign Scan on public.ft1 ft1_1 + Output: ft1_1.c1, ft1_1.c2 + Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE (("C 1" = 0)) +(14 rows) + -- =================================================================== -- JOIN queries -- =================================================================== diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index c1c28df8be7..2ad734c8a8a 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -6904,20 +6904,20 @@ add_foreign_ordered_paths(PlannerInfo *root, RelOptInfo *input_rel, merge_fdw_options(fpinfo, ifpinfo, NULL); /* - * If the input_rel is a base or join relation, we would already have - * considered pushing down the final sort to the remote server when - * creating pre-sorted foreign paths for that relation, because the - * query_pathkeys is set to the root->sort_pathkeys in that case (see - * standard_qp_callback()). + * If the input_rel is a base or join relation, + * add_paths_with_pathkeys_for_rel() already considered the creation of + * paths to suit query_pathkeys. sort_pathkeys can be NIL here despite + * the query having an ORDER BY clause, and query_pathkeys can be set to + * setop_pathkeys (see standard_qp_callback()). */ if (input_rel->reloptkind == RELOPT_BASEREL || input_rel->reloptkind == RELOPT_JOINREL) { - Assert(root->query_pathkeys == root->sort_pathkeys); + Assert(root->query_pathkeys == root->sort_pathkeys || + root->sort_pathkeys == NIL); - /* Safe to push down if the query_pathkeys is safe to push down */ + /* Safe to push down if the query_pathkeys are safe to push down */ fpinfo->pushdown_safe = ifpinfo->qp_is_pushdown_safe; - return; } diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index f25975a8ba8..d45f0641fd8 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -495,6 +495,15 @@ SELECT * FROM ( SELECT 2 AS type,c1 FROM ft2 ) a ORDER BY type; +-- Ensure the code works correctly when sort_pathkeys is empty due to +-- a redundant pathkey. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM ( + (SELECT c1, c2 FROM ft1 WHERE c1 = 0 ORDER BY c1 LIMIT 1) + UNION + (SELECT c1, c2 FROM ft1 WHERE c1 = 0 ORDER BY c1) +) a; + -- =================================================================== -- JOIN queries -- =================================================================== [parent: 145a7175ee0a]