BUG #19827: postgres_fdw: Assert in add_foreign_ordered_paths() with redundant ORDER BY in set-op input

Поиск
Список
Период
Сортировка
Искать

BUG #19827: postgres_fdw: Assert in add_foreign_ordered_paths() with redundant ORDER BY in set-op input

От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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
https://robins.in




FAQ