Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xOXmx-00000000CWI-34ty for pgsql-bugs@arkaria.postgresql.org; Wed, 04 Nov 2026 10:00:17 +0000 Received: from localhost ([127.0.0.1]:40294 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xOXmy-00000005YVx-0d8D for pgsql-bugs@arkaria.postgresql.org; Wed, 04 Nov 2026 10:00:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:49972) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xNuCf-00000004PGG-0FuH for pgsql-bugs@lists.postgresql.org; Mon, 02 Nov 2026 15:44:09 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xNuCc-00000002Fml-1eMm for pgsql-bugs@lists.postgresql.org; Mon, 02 Nov 2026 15:44:08 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=ojQCkca5lliCpNGLtLbH0jbKorvOu6oXGo2zlMNxZo0=; b=s925kK80QF+S+x8BqsDy9hYZKT mRyoW/uiV/79gYHtQ1uueBjukOMKhgs1UCJeRZ2MDyevU5OUDKfXZWVmlGpfocj5R/MYWsZNcaYlQ +kwAFw4z7kSsFHUsSL28IfHyYx6u+1oGZ/z+rVwj2h7tazYbvcEUAeI33xAxI0Y6a7xGNIuRLzBDv g+ExBK5QwJWAWxmlyNkDh7PGgW857OfLQb5RggKXH+jW9WvHitd+Brg1CYtWYLHYLuD5swb0d3+2i LBePqCwM8Ixxlg0q7kHpaigQLGShEy/U1v65Ik1o5WVwYoCQDALTrVXldYQxvV+ofhiuyeQpSs9DW lAof6OuA==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xNuCb-00000006RJN-0hxM for pgsql-bugs@lists.postgresql.org; Mon, 02 Nov 2026 15:44:05 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xNuCZ-0000000C3EH-0zNK for pgsql-bugs@lists.postgresql.org; Mon, 02 Nov 2026 15:44:04 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19827: postgres_fdw: Assert in add_foreign_ordered_paths() with redundant ORDER BY in set-op input To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: tharakan@gmail.com Reply-To: tharakan@gmail.com, pgsql-bugs@lists.postgresql.org Date: Mon, 02 Nov 2026 15:43:48 +0000 Message-ID: <19827-6e0bc0f9a5226e71@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 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 =3D=3D 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 =3D=3D=3D 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 =3D 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 =3D=3D=3D=3D=3D=3D 6cda9dfc4f97 (master) =E2=80=94 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 =3D=3D=3D=3D=3D=3D=3D=3D=3D #4 add_foreign_ordered_paths (...) at postgres_fdw.c:8280 #5 postgresGetForeignUpperPaths (root=3D..., stage=3DUPPERREL_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=3D"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=3D...) at prepunion.c:164 (EXCEPT case; UNION goes through generate_union_paths) Patch =3D=3D=3D=3D=3D 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 !=3D root->sort_pathkeys) + { + Assert(root->sort_pathkeys =3D=3D NIL && + root->query_pathkeys =3D=3D root->setop_pathkeys); + fpinfo->pushdown_safe =3D 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 =3D=3D=3D=3D=3D=3D=3D 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 =3D 1 ORDER BY a LIMIT 1) UNION (SELECT 2, 'x'); the patched build sends SELECT a, b FROM public.t WHERE ((a =3D 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