Hi hackers, I'd like to propose a patch that makes the ReScanForeignScan callback optional for Foreign Data Wrappers, allowing the planner to automatically handle non-rescannable FDWs.
# Background Currently, FDWs are expected to implement ReScanForeignScan to support rescanning in scenarios like nested loop joins. However, some FDWs just cannot implement rescanning: - Streaming data sources (Kafka, message queues) - One-time token-based APIs with expensive re-authentication - Data sources where re-fetching is prohibitively expensive - ... Right now, these FDWs either implement a stub ReScanForeignScan that fails at runtime, or they buffer all data in BeginForeignScan to support rescan, which wastes memory when rescanning isn't needed. # Proposed Solution This patch introduces a 'rescannable' field in the Path structure that tracks whether each path supports rescanning. The planner uses this information to: 1. Auto-detect FDW rescan capability by checking if ReScanForeignScan is provided 2. Automatically insert Material nodes when non-rescannable paths are used as inner paths in nested loops 3. Reject parameterized foreign scan paths if the FDW doesn't support rescan (preventing planning failures) 4. Raise a clear error for correlated subqueries that cannot be handled (these are planned independently and can't use Material nodes) # Not only FDWs Beyond enabling non-rescannable FDWs, this mechanism could also be used for performance optimization on other nodes. Some operations can technically support rescan but at significant cost (MergeAppend redo all the sorts, Aggregation redo all the calculations...). We may could mark such paths as non-rescannable in some cases to encourage the planner to materialize results instead. The patch is attached. If reviewers feel this is a good idea and needs more discussion or the complexity warrants it, I'm happy to register this for the next CommitFest. For now, I'm sharing it here to gather initial feedback on the approach. -- Adam
>From 2a0839f1e581294512a7424d260a8c853935edee Mon Sep 17 00:00:00 2001 From: Adam Lee <[email protected]> Date: Wed, 3 Dec 2025 16:25:31 +0800 Subject: [PATCH] Make ReScanForeignScan callback optional for FDWs This patch introduces a mechanism to handle Foreign Data Wrappers (FDWs) that do not or could not implement the ReScanForeignScan callback. The planner now tracks whether paths support rescanning and automatically inserts Material nodes when necessary. Key changes: 1. Added 'rescannable' field to Path struct Each path type now explicitly tracks whether it can be rescanned. Most scan types (SeqScan, IndexScan, etc.) are rescannable. Join paths inherit rescannability from their children. 2. Auto-detect FDW rescan capability Foreign scan paths are marked rescannable only if the FDW provides a ReScanForeignScan callback. This is determined by checking rel->fdwroutine->ReScanForeignScan != NULL during path creation. 3. Reject parameterized paths for non-rescannable FDWs If an FDW doesn't support rescan and a parameterized path is required, create_foreignscan_path() rejects the path by returning NULL. This prevents plan generation failures for regular joins. 4. Automatic Material node insertion In create_nestloop_plan(), if the inner path is not rescannable and doesn't already have a Material node, one is automatically inserted. This handles non-parameterized rescanning scenarios like nested loop joins and LATERAL joins. 5. Runtime error check for correlated subqueries In ExecReScanForeignScan(), if ReScanForeignScan is NULL, an error is raised. This catches cases that couldn't be prevented at planning time, primarily correlated subqueries (SubPlans). SubPlans are planned independently without knowledge that rescanning will be needed. --- src/backend/executor/nodeForeignscan.c | 11 + src/backend/optimizer/plan/createplan.c | 19 ++ src/backend/optimizer/util/pathnode.c | 113 +++++++ src/include/nodes/pathnodes.h | 7 + src/test/fdw/.gitignore | 1 + src/test/fdw/Makefile | 18 ++ src/test/fdw/expected/no_rescan_test.out | 293 +++++++++++++++++ .../fdw/no_rescan_test_extension/Makefile | 16 + .../no_rescan_test_fdw--1.0.sql | 9 + .../no_rescan_test_fdw.c | 303 ++++++++++++++++++ .../no_rescan_test_fdw.control | 4 + src/test/fdw/sql/no_rescan_test.sql | 177 ++++++++++ 12 files changed, 971 insertions(+) create mode 100644 src/test/fdw/.gitignore create mode 100644 src/test/fdw/Makefile create mode 100644 src/test/fdw/expected/no_rescan_test.out create mode 100644 src/test/fdw/no_rescan_test_extension/Makefile create mode 100644 src/test/fdw/no_rescan_test_extension/no_rescan_test_fdw--1.0.sql create mode 100644 src/test/fdw/no_rescan_test_extension/no_rescan_test_fdw.c create mode 100644 src/test/fdw/no_rescan_test_extension/no_rescan_test_fdw.control create mode 100644 src/test/fdw/sql/no_rescan_test.sql diff --git a/src/backend/executor/nodeForeignscan.c b/src/backend/executor/nodeForeignscan.c index 9c56c2f3acf..790691b6701 100644 --- a/src/backend/executor/nodeForeignscan.c +++ b/src/backend/executor/nodeForeignscan.c @@ -333,6 +333,17 @@ ExecReScanForeignScan(ForeignScanState *node) if (estate->es_epq_active != NULL && plan->operation != CMD_SELECT) return; + /* + * If the FDW doesn't provide a ReScan callback, we cannot rescan. + * + * This check catches cases that couldn't be prevented at planning time, + * primarily correlated subqueries (SubPlans). In SubPlans, the foreign + * table scan is planned independently without knowledge that it will need + * to be rescanned for each outer row. + */ + if (node->fdwroutine->ReScanForeignScan == NULL) + elog(ERROR, "foreign-data wrapper does not support ReScan"); + node->fdwroutine->ReScanForeignScan(node); /* diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 8af091ba647..a419112c541 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -20,6 +20,7 @@ #include "access/sysattr.h" #include "catalog/pg_class.h" +#include "executor/executor.h" #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/extensible.h" @@ -4228,6 +4229,24 @@ create_nestloop_plan(PlannerInfo *root, bms_free(root->curOuterRels); root->curOuterRels = saveOuterRels; + /* + * If the inner path doesn't support rescanning and we don't already have a + * Material node, add one to allow the inner plan to be rescanned. + */ + if (!best_path->jpath.innerjoinpath->rescannable && !IsA(inner_plan, Material)) + { + Plan *matplan = (Plan *) make_material(inner_plan); + + /* + * We charge cpu_operator_cost per tuple for materialization, similar + * to what's done for merge joins. + */ + copy_plan_costsize(matplan, inner_plan); + matplan->total_cost += cpu_operator_cost * inner_plan->plan_rows; + + inner_plan = matplan; + } + /* Sort join qual clauses into best execution order */ joinrestrictclauses = order_qual_clauses(root, joinrestrictclauses); diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index b6be4ddbd01..3163d550326 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -994,6 +994,7 @@ create_seqscan_path(PlannerInfo *root, RelOptInfo *rel, pathnode->parallel_safe = rel->consider_parallel; pathnode->parallel_workers = parallel_workers; pathnode->pathkeys = NIL; /* seqscan has unordered result */ + pathnode->rescannable = true; /* seqscan can restart */ cost_seqscan(pathnode, root, rel, pathnode->param_info); @@ -1018,6 +1019,7 @@ create_samplescan_path(PlannerInfo *root, RelOptInfo *rel, Relids required_outer pathnode->parallel_safe = rel->consider_parallel; pathnode->parallel_workers = 0; pathnode->pathkeys = NIL; /* samplescan has unordered result */ + pathnode->rescannable = true; /* samplescan can restart */ cost_samplescan(pathnode, root, rel, pathnode->param_info); @@ -1070,6 +1072,7 @@ create_index_path(PlannerInfo *root, pathnode->path.parallel_safe = rel->consider_parallel; pathnode->path.parallel_workers = 0; pathnode->path.pathkeys = pathkeys; + pathnode->path.rescannable = true; /* index scans support rescan */ pathnode->indexinfo = index; pathnode->indexclauses = indexclauses; @@ -1113,6 +1116,7 @@ create_bitmap_heap_path(PlannerInfo *root, pathnode->path.parallel_safe = rel->consider_parallel; pathnode->path.parallel_workers = parallel_degree; pathnode->path.pathkeys = NIL; /* always unordered */ + pathnode->path.rescannable = true; /* bitmap scan supports rescan */ pathnode->bitmapqual = bitmapqual; @@ -1246,6 +1250,7 @@ create_tidscan_path(PlannerInfo *root, RelOptInfo *rel, List *tidquals, pathnode->path.parallel_safe = rel->consider_parallel; pathnode->path.parallel_workers = 0; pathnode->path.pathkeys = NIL; /* always unordered */ + pathnode->path.rescannable = true; /* TID scans can restart */ pathnode->tidquals = tidquals; @@ -1276,6 +1281,7 @@ create_tidrangescan_path(PlannerInfo *root, RelOptInfo *rel, pathnode->path.parallel_safe = rel->consider_parallel; pathnode->path.parallel_workers = parallel_workers; pathnode->path.pathkeys = NIL; /* always unordered */ + pathnode->path.rescannable = true; /* TID range scans can restart */ pathnode->tidrangequals = tidrangequals; @@ -1337,6 +1343,8 @@ create_append_path(PlannerInfo *root, pathnode->path.parallel_safe = rel->consider_parallel; pathnode->path.parallel_workers = parallel_workers; pathnode->path.pathkeys = pathkeys; + /* Append is rescannable if all its children are, will check each later */ + pathnode->path.rescannable = true; /* * For parallel append, non-partial paths are sorted by descending total @@ -1378,6 +1386,9 @@ create_append_path(PlannerInfo *root, pathnode->path.parallel_safe = pathnode->path.parallel_safe && subpath->parallel_safe; + /* Append is rescannable only if all children are rescannable */ + pathnode->path.rescannable = subpath->rescannable; + /* All child paths must have same parameterization */ Assert(bms_equal(PATH_REQ_OUTER(subpath), required_outer)); } @@ -1495,6 +1506,8 @@ create_merge_append_path(PlannerInfo *root, pathnode->path.parallel_safe = rel->consider_parallel; pathnode->path.parallel_workers = 0; pathnode->path.pathkeys = pathkeys; + /* MergeAppend is rescannable if all its children are, will check each later */ + pathnode->path.rescannable = true; pathnode->subpaths = subpaths; /* @@ -1526,6 +1539,8 @@ create_merge_append_path(PlannerInfo *root, pathnode->path.rows += subpath->rows; pathnode->path.parallel_safe = pathnode->path.parallel_safe && subpath->parallel_safe; + /* MergeAppend is rescannable only if all children are */ + pathnode->path.rescannable = subpath->rescannable; if (!pathkeys_count_contained_in(pathkeys, subpath->pathkeys, &presorted_keys)) @@ -1670,6 +1685,10 @@ create_material_path(RelOptInfo *rel, Path *subpath) subpath->parallel_safe; pathnode->path.parallel_workers = subpath->parallel_workers; pathnode->path.pathkeys = subpath->pathkeys; + pathnode->path.rescannable = true; /* Material always supports rescan + unless the path is parameterized, + which will be rejected by the + planner if the scan is mandatory */ pathnode->subpath = subpath; @@ -1705,6 +1724,7 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, subpath->parallel_safe; pathnode->path.parallel_workers = subpath->parallel_workers; pathnode->path.pathkeys = subpath->pathkeys; + pathnode->path.rescannable = true; /* Memoize always supports rescan */ pathnode->subpath = subpath; pathnode->hash_operators = hash_operators; @@ -1816,6 +1836,7 @@ create_gather_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, pathnode->path.parallel_safe = false; pathnode->path.parallel_workers = 0; pathnode->path.pathkeys = NIL; /* Gather has unordered result */ + pathnode->path.rescannable = true; /* Gather supports rescan however maybe not efficient */ pathnode->subpath = subpath; pathnode->num_workers = subpath->parallel_workers; @@ -1860,6 +1881,7 @@ create_subqueryscan_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, subpath->parallel_safe; pathnode->path.parallel_workers = subpath->parallel_workers; pathnode->path.pathkeys = pathkeys; + pathnode->path.rescannable = true; /* subquery supports rescan if not parameterized */ pathnode->subpath = subpath; cost_subqueryscan(pathnode, root, rel, pathnode->path.param_info, @@ -1888,6 +1910,7 @@ create_functionscan_path(PlannerInfo *root, RelOptInfo *rel, pathnode->parallel_safe = rel->consider_parallel; pathnode->parallel_workers = 0; pathnode->pathkeys = pathkeys; + pathnode->rescannable = true; /* function scans materialize their output */ cost_functionscan(pathnode, root, rel, pathnode->param_info); @@ -1914,6 +1937,7 @@ create_tablefuncscan_path(PlannerInfo *root, RelOptInfo *rel, pathnode->parallel_safe = rel->consider_parallel; pathnode->parallel_workers = 0; pathnode->pathkeys = NIL; /* result is always unordered */ + pathnode->rescannable = true; /* table function scans materialize output */ cost_tablefuncscan(pathnode, root, rel, pathnode->param_info); @@ -1940,6 +1964,7 @@ create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel, pathnode->parallel_safe = rel->consider_parallel; pathnode->parallel_workers = 0; pathnode->pathkeys = NIL; /* result is always unordered */ + pathnode->rescannable = true; /* values scans can restart */ cost_valuesscan(pathnode, root, rel, pathnode->param_info); @@ -1966,6 +1991,7 @@ create_ctescan_path(PlannerInfo *root, RelOptInfo *rel, pathnode->parallel_safe = rel->consider_parallel; pathnode->parallel_workers = 0; pathnode->pathkeys = pathkeys; + pathnode->rescannable = true; /* CTE scans materialize output */ cost_ctescan(pathnode, root, rel, pathnode->param_info); @@ -1992,6 +2018,7 @@ create_namedtuplestorescan_path(PlannerInfo *root, RelOptInfo *rel, pathnode->parallel_safe = rel->consider_parallel; pathnode->parallel_workers = 0; pathnode->pathkeys = NIL; /* result is always unordered */ + pathnode->rescannable = true; /* tuplestore scans materialize output */ cost_namedtuplestorescan(pathnode, root, rel, pathnode->param_info); @@ -2018,6 +2045,7 @@ create_resultscan_path(PlannerInfo *root, RelOptInfo *rel, pathnode->parallel_safe = rel->consider_parallel; pathnode->parallel_workers = 0; pathnode->pathkeys = NIL; /* result is always unordered */ + pathnode->rescannable = true; /* result nodes can be rescanned */ cost_resultscan(pathnode, root, rel, pathnode->param_info); @@ -2044,6 +2072,7 @@ create_worktablescan_path(PlannerInfo *root, RelOptInfo *rel, pathnode->parallel_safe = rel->consider_parallel; pathnode->parallel_workers = 0; pathnode->pathkeys = NIL; /* result is always unordered */ + pathnode->rescannable = true; /* work table scans materialize output */ /* Cost is the same as for a regular CTE scan */ cost_ctescan(pathnode, root, rel, pathnode->param_info); @@ -2092,6 +2121,41 @@ create_foreignscan_path(PlannerInfo *root, RelOptInfo *rel, pathnode->path.total_cost = total_cost; pathnode->path.pathkeys = pathkeys; + /* + * A foreign scan is considered rescannable only if the FDW provides + * a ReScanForeignScan callback. If not provided, the planner will + * automatically insert a Material node when rescanning is needed + * (e.g., for nested loop joins). + * + * However, if the path is parameterized (required_outer is not empty), + * and the FDW doesn't support rescan, we cannot create this path. + * Parameterized paths require rescanning with different parameter values, + * and Material nodes don't help in this case (they would need to be + * rescanned too). This is similar to how Motion paths work. + */ + if (rel->fdwroutine != NULL && rel->fdwroutine->ReScanForeignScan != NULL) + { + pathnode->path.rescannable = true; + } + else + { + pathnode->path.rescannable = false; + + /* + * Reject parameterized paths if FDW doesn't support rescan + * + * We only need to check path.required_outer here. For a base relation, + * any dependency from rel->lateral_relids is already reflected in the + * required_outer set passed to this function. Therefore, checking + * required_outer is sufficient to detect all parameterization. + */ + if (!bms_is_empty(required_outer)) + { + pfree(pathnode); + return NULL; + } + } + pathnode->fdw_outerpath = fdw_outerpath; pathnode->fdw_restrictinfo = fdw_restrictinfo; pathnode->fdw_private = fdw_private; @@ -2150,6 +2214,24 @@ create_foreign_join_path(PlannerInfo *root, RelOptInfo *rel, pathnode->fdw_restrictinfo = fdw_restrictinfo; pathnode->fdw_private = fdw_private; + /* + * A foreign join is considered rescannable only if the FDW provides + * a ReScanForeignScan callback. If not provided, the planner will + * automatically insert a Material node when rescanning is needed. + */ + if (rel->fdwroutine != NULL && rel->fdwroutine->ReScanForeignScan != NULL) + { + pathnode->path.rescannable = true; + } + else + { + /* + * Parameterized paths are rejected at the beginning of this function + * already. + */ + pathnode->path.rescannable = false; + } + return pathnode; } @@ -2199,6 +2281,20 @@ create_foreign_upper_path(PlannerInfo *root, RelOptInfo *rel, pathnode->fdw_restrictinfo = fdw_restrictinfo; pathnode->fdw_private = fdw_private; + /* + * A foreign upper relation is considered rescannable only if the FDW + * provides a ReScanForeignScan callback. If not provided, the planner + * will automatically insert a Material node when rescanning is needed. + * + * Note: Upper relations are never parameterized (param_info is always + * NULL), so we don't need to check for the parameterization + no-rescan + * combination here. + */ + if (rel->fdwroutine != NULL && rel->fdwroutine->ReScanForeignScan != NULL) + pathnode->path.rescannable = true; + else + pathnode->path.rescannable = false; + return pathnode; } @@ -2356,6 +2452,8 @@ create_nestloop_path(PlannerInfo *root, /* This is a foolish way to estimate parallel_workers, but for now... */ pathnode->jpath.path.parallel_workers = outer_path->parallel_workers; pathnode->jpath.path.pathkeys = pathkeys; + /* NestLoop can be rescanned if both outer and inner can be */ + pathnode->jpath.path.rescannable = outer_path->rescannable && inner_path->rescannable; pathnode->jpath.jointype = jointype; pathnode->jpath.inner_unique = extra->inner_unique; pathnode->jpath.outerjoinpath = outer_path; @@ -2422,6 +2520,8 @@ create_mergejoin_path(PlannerInfo *root, /* This is a foolish way to estimate parallel_workers, but for now... */ pathnode->jpath.path.parallel_workers = outer_path->parallel_workers; pathnode->jpath.path.pathkeys = pathkeys; + /* Mergejoin can be rescanned if both outer and inner can be */ + pathnode->jpath.path.rescannable = outer_path->rescannable && inner_path->rescannable; pathnode->jpath.jointype = jointype; pathnode->jpath.inner_unique = extra->inner_unique; pathnode->jpath.outerjoinpath = outer_path; @@ -2500,6 +2600,8 @@ create_hashjoin_path(PlannerInfo *root, * outer rel than it does now.) */ pathnode->jpath.path.pathkeys = NIL; + /* Hashjoin can be rescanned if both outer and inner can be */ + pathnode->jpath.path.rescannable = outer_path->rescannable && inner_path->rescannable; pathnode->jpath.jointype = jointype; pathnode->jpath.inner_unique = extra->inner_unique; pathnode->jpath.outerjoinpath = outer_path; @@ -2557,6 +2659,8 @@ create_projection_path(PlannerInfo *root, pathnode->path.parallel_workers = subpath->parallel_workers; /* Projection does not change the sort order */ pathnode->path.pathkeys = subpath->pathkeys; + /* Projection inherits rescannability from its subpath */ + pathnode->path.rescannable = subpath->rescannable; pathnode->subpath = subpath; @@ -2810,6 +2914,8 @@ create_incremental_sort_path(PlannerInfo *root, subpath->parallel_safe; pathnode->path.parallel_workers = subpath->parallel_workers; pathnode->path.pathkeys = pathkeys; + /* Sort materializes its output, so it's rescannable */ + pathnode->path.rescannable = true; pathnode->subpath = subpath; @@ -2857,6 +2963,8 @@ create_sort_path(PlannerInfo *root, subpath->parallel_safe; pathnode->path.parallel_workers = subpath->parallel_workers; pathnode->path.pathkeys = pathkeys; + /* Sort materializes its output, so it's rescannable */ + pathnode->path.rescannable = true; pathnode->subpath = subpath; @@ -2904,6 +3012,8 @@ create_group_path(PlannerInfo *root, pathnode->path.parallel_workers = subpath->parallel_workers; /* Group doesn't change sort ordering */ pathnode->path.pathkeys = subpath->pathkeys; + /* Group doesn't materialize, so inherit from subpath */ + pathnode->path.rescannable = subpath->rescannable; pathnode->subpath = subpath; @@ -2959,6 +3069,8 @@ create_unique_path(PlannerInfo *root, pathnode->path.parallel_workers = subpath->parallel_workers; /* Unique doesn't change the input ordering */ pathnode->path.pathkeys = subpath->pathkeys; + /* Unique doesn't materialize, so inherit from subpath */ + pathnode->path.rescannable = subpath->rescannable; pathnode->subpath = subpath; pathnode->numkeys = numCols; @@ -3032,6 +3144,7 @@ create_agg_path(PlannerInfo *root, } else pathnode->path.pathkeys = NIL; /* output is unordered */ + pathnode->path.rescannable = true; /* Aggregations support rescan, however maybe not efficient */ pathnode->subpath = subpath; diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 46a8655621d..124c33e1709 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -1911,6 +1911,13 @@ typedef struct Path /* sort ordering of path's output; a List of PathKey nodes; see above */ List *pathkeys; + + /* + * Does this path support rescanning? + * If false and rescanning is needed (e.g., as NestLoop inner path), + * a Material node will be added automatically. + */ + bool rescannable; } Path; /* Macro for extracting a path's parameterization relids; beware double eval */ diff --git a/src/test/fdw/.gitignore b/src/test/fdw/.gitignore new file mode 100644 index 00000000000..fbca2253799 --- /dev/null +++ b/src/test/fdw/.gitignore @@ -0,0 +1 @@ +results/ diff --git a/src/test/fdw/Makefile b/src/test/fdw/Makefile new file mode 100644 index 00000000000..f8d11f514c1 --- /dev/null +++ b/src/test/fdw/Makefile @@ -0,0 +1,18 @@ +SUBDIRS = no_rescan_test_extension + +REGRESS = no_rescan_test + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = src/test/fdw +top_builddir = ../../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif + +installcheck: install + +$(call recurse,all install clean) diff --git a/src/test/fdw/expected/no_rescan_test.out b/src/test/fdw/expected/no_rescan_test.out new file mode 100644 index 00000000000..211798f6cf8 --- /dev/null +++ b/src/test/fdw/expected/no_rescan_test.out @@ -0,0 +1,293 @@ +-- Test script for no_rescan_test_fdw +-- This demonstrates automatic materialization when FDW doesn't support rescan +-- Configure planner to use Nested Loop Join so we can see Material nodes +SET enable_hashjoin = off; -- Disable Hash Join +SET enable_mergejoin = off; -- Disable Merge Join +SET enable_material = on; -- Ensure Material nodes are allowed +SET enable_nestloop = on; -- Ensure Nested Loop is allowed +-- Create the extension +CREATE EXTENSION no_rescan_test_fdw; +-- Create server +CREATE SERVER no_rescan_server FOREIGN DATA WRAPPER no_rescan_test_fdw; +-- Create foreign table +-- The FDW will generate 10 rows with (id, data) columns +CREATE FOREIGN TABLE test_no_rescan_ft ( + id int, + data text +) SERVER no_rescan_server; +-- Test 1: Simple scan (no rescan needed) +-- This should work fine without any Material node +SELECT * FROM test_no_rescan_ft ORDER BY id; +NOTICE: no_rescan_test_fdw: BeginForeignScan - will generate 10 rows +NOTICE: no_rescan_test_fdw: EndForeignScan - scanned 10 of 10 rows + id | data +----+-------- + 1 | row_1 + 2 | row_2 + 3 | row_3 + 4 | row_4 + 5 | row_5 + 6 | row_6 + 7 | row_7 + 8 | row_8 + 9 | row_9 + 10 | row_10 +(10 rows) + +-- Test 2: Verify the foreign scan works +EXPLAIN (COSTS OFF) SELECT * FROM test_no_rescan_ft; +NOTICE: no_rescan_test_fdw: BeginForeignScan - will generate 10 rows +NOTICE: no_rescan_test_fdw: EndForeignScan - scanned 0 of 10 rows + QUERY PLAN +----------------------------------- + Foreign Scan on test_no_rescan_ft +(1 row) + +-- Note: EXPLAIN triggers BeginForeignScan/EndForeignScan for cost estimation +-- This is normal behavior and shows "scanned 0 of 10 rows" because EXPLAIN +-- doesn't actually fetch tuples, only initializes the scan +-- Create a small local table for join testing +CREATE TABLE test_local_small ( + id int, + name text +); +INSERT INTO test_local_small VALUES + (1, 'one'), + (2, 'two'), + (3, 'three'), + (4, 'four'), + (5, 'five'); +-- Test 3: Nested Loop Join - Material node should be automatically inserted +-- Because we disabled hash/merge joins, planner will use Nested Loop +-- and because no_rescan_test_fdw doesn't provide ReScanForeignScan, +-- a Material node will be automatically inserted +EXPLAIN (COSTS OFF) +SELECT l.id, l.name, f.data +FROM test_local_small l +INNER JOIN test_no_rescan_ft f ON l.id = f.id +ORDER BY l.id; +NOTICE: no_rescan_test_fdw: BeginForeignScan - will generate 10 rows +NOTICE: no_rescan_test_fdw: EndForeignScan - scanned 0 of 10 rows + QUERY PLAN +------------------------------------------------------- + Sort + Sort Key: l.id + -> Nested Loop + Join Filter: (l.id = f.id) + -> Seq Scan on test_local_small l + -> Materialize + -> Foreign Scan on test_no_rescan_ft f +(7 rows) + +-- Expected plan +-- Gather Motion +-- -> Sort +-- -> Nested Loop +-- -> Seq Scan on test_local_small l +-- -> Material <-- Automatically inserted! +-- -> Foreign Scan on test_no_rescan_ft f +-- Test 4: Execute the join to verify it works correctly +-- This should return 5 rows (only IDs 1-5 match) +SELECT l.id, l.name, f.data +FROM test_local_small l +INNER JOIN test_no_rescan_ft f ON l.id = f.id +ORDER BY l.id; +NOTICE: no_rescan_test_fdw: BeginForeignScan - will generate 10 rows +NOTICE: no_rescan_test_fdw: EndForeignScan - scanned 10 of 10 rows + id | name | data +----+-------+------- + 1 | one | row_1 + 2 | two | row_2 + 3 | three | row_3 + 4 | four | row_4 + 5 | five | row_5 +(5 rows) + +-- The foreign scan will be executed once and materialized +-- Even though test_local_small has 5 rows, the Material node buffers the +-- foreign scan results, so we don't need to rescan +-- Test 5: Alternative - using LATERAL join which naturally requires rescan +-- LATERAL joins require the inner side to be rescanned for each outer row +-- The Material node allows this even though the FDW doesn't support rescan +EXPLAIN (COSTS OFF) +SELECT l.id, l.name, f.data +FROM test_local_small l, + LATERAL (SELECT * FROM test_no_rescan_ft f WHERE f.id = l.id) f +ORDER BY l.id; +NOTICE: no_rescan_test_fdw: BeginForeignScan - will generate 10 rows +NOTICE: no_rescan_test_fdw: EndForeignScan - scanned 0 of 10 rows + QUERY PLAN +------------------------------------------------------- + Sort + Sort Key: l.id + -> Nested Loop + Join Filter: (l.id = f.id) + -> Seq Scan on test_local_small l + -> Materialize + -> Foreign Scan on test_no_rescan_ft f +(7 rows) + +-- Execute the LATERAL join - should return 5 rows +SELECT l.id, l.name, f.data +FROM test_local_small l, + LATERAL (SELECT * FROM test_no_rescan_ft f WHERE f.id = l.id) f +ORDER BY l.id; +NOTICE: no_rescan_test_fdw: BeginForeignScan - will generate 10 rows +NOTICE: no_rescan_test_fdw: EndForeignScan - scanned 10 of 10 rows + id | name | data +----+-------+------- + 1 | one | row_1 + 2 | two | row_2 + 3 | three | row_3 + 4 | four | row_4 + 5 | five | row_5 +(5 rows) + +-- Test 6: More complex join scenario +CREATE TABLE test_local_medium ( + id int, + category text +); +INSERT INTO test_local_medium +SELECT i, 'category_' || (i % 3) +FROM generate_series(1, 8) i; +-- This should also show Material node with Nested Loop +EXPLAIN (COSTS OFF) +SELECT m.id, m.category, f.data +FROM test_local_medium m +INNER JOIN test_no_rescan_ft f ON m.id = f.id +WHERE m.id <= 7 +ORDER BY m.id; +NOTICE: no_rescan_test_fdw: BeginForeignScan - will generate 10 rows +NOTICE: no_rescan_test_fdw: EndForeignScan - scanned 0 of 10 rows + QUERY PLAN +------------------------------------------------------- + Sort + Sort Key: m.id + -> Nested Loop + Join Filter: (m.id = f.id) + -> Seq Scan on test_local_medium m + Filter: (id <= 7) + -> Materialize + -> Foreign Scan on test_no_rescan_ft f +(8 rows) + +-- Execute the query - should return 7 rows (IDs 1-7) +SELECT m.id, m.category, f.data +FROM test_local_medium m +INNER JOIN test_no_rescan_ft f ON m.id = f.id +WHERE m.id <= 7 +ORDER BY m.id; +NOTICE: no_rescan_test_fdw: BeginForeignScan - will generate 10 rows +NOTICE: no_rescan_test_fdw: EndForeignScan - scanned 10 of 10 rows + id | category | data +----+------------+------- + 1 | category_1 | row_1 + 2 | category_2 | row_2 + 3 | category_0 | row_3 + 4 | category_1 | row_4 + 5 | category_2 | row_5 + 6 | category_0 | row_6 + 7 | category_1 | row_7 +(7 rows) + +-- Test 7: Verify that without rescan the query still works +-- (Material node buffers the data) +-- Should return count = 5 +SELECT count(*) +FROM test_local_small l1 +INNER JOIN test_local_small l2 ON l1.id = l2.id +INNER JOIN test_no_rescan_ft f ON l1.id = f.id; +NOTICE: no_rescan_test_fdw: BeginForeignScan - will generate 10 rows +NOTICE: no_rescan_test_fdw: EndForeignScan - scanned 10 of 10 rows + count +------- + 5 +(1 row) + +-- Test 8: Correlated subquery limitation - execution time error +-- A correlated subquery in the SELECT list creates a SubPlan that must rescan +-- the foreign table for each outer row. SubPlans are planned independently, +-- so the foreign table scan doesn't know it will need rescanning until execution. +-- This is a known limitation: FDWs without ReScanForeignScan cannot be used +-- in correlated subqueries. +EXPLAIN (COSTS OFF) +SELECT l.id, l.name, + (SELECT f.data FROM test_no_rescan_ft f WHERE f.id = l.id LIMIT 1) as fdata +FROM test_local_small l +ORDER BY l.id; +NOTICE: no_rescan_test_fdw: BeginForeignScan - will generate 10 rows +NOTICE: no_rescan_test_fdw: EndForeignScan - scanned 0 of 10 rows + QUERY PLAN +--------------------------------------------------------- + Sort + Sort Key: l.id + -> Seq Scan on test_local_small l + SubPlan expr_1 + -> Limit + -> Foreign Scan on test_no_rescan_ft f + Filter: (id = l.id) +(7 rows) + +-- Execution fails because each SubPlan execution requires rescanning the +-- foreign table with different parameter values, which is impossible without +-- the ReScanForeignScan callback. +SELECT l.id, l.name, + (SELECT f.data FROM test_no_rescan_ft f WHERE f.id = l.id LIMIT 1) as fdata +FROM test_local_small l +ORDER BY l.id; +NOTICE: no_rescan_test_fdw: BeginForeignScan - will generate 10 rows +ERROR: foreign-data wrapper does not support ReScan +-- Test 9: LATERAL query - planner avoids parameterization +-- Even though this uses LATERAL syntax, the planner can convert it to a +-- regular nested loop join with a filter condition (l.id = f.id), avoiding +-- the need for a parameterized foreign scan path. A Material node is inserted +-- to buffer the foreign scan results for rescanning. +-- This works because the foreign scan itself doesn't need parameters - the +-- filtering happens after the scan. +EXPLAIN (COSTS OFF) +SELECT l.id, l.name, f.data +FROM test_local_small l, + LATERAL (SELECT * FROM test_no_rescan_ft f WHERE f.id = l.id) f +WHERE l.id < 3 +ORDER BY l.id; +NOTICE: no_rescan_test_fdw: BeginForeignScan - will generate 10 rows +NOTICE: no_rescan_test_fdw: EndForeignScan - scanned 0 of 10 rows + QUERY PLAN +------------------------------------------------------- + Sort + Sort Key: l.id + -> Nested Loop + Join Filter: (l.id = f.id) + -> Seq Scan on test_local_small l + Filter: (id < 3) + -> Materialize + -> Foreign Scan on test_no_rescan_ft f +(8 rows) + +-- Executes successfully: Material buffers all foreign scan results, then +-- rescans from the buffer for each outer row while applying the join filter. +SELECT l.id, l.name, f.data +FROM test_local_small l, + LATERAL (SELECT * FROM test_no_rescan_ft f WHERE f.id = l.id) f +WHERE l.id < 3 +ORDER BY l.id; +NOTICE: no_rescan_test_fdw: BeginForeignScan - will generate 10 rows +NOTICE: no_rescan_test_fdw: EndForeignScan - scanned 10 of 10 rows + id | name | data +----+------+------- + 1 | one | row_1 + 2 | two | row_2 +(2 rows) + +-- Reset planner settings +RESET enable_hashjoin; +RESET enable_mergejoin; +RESET enable_material; +RESET enable_nestloop; +-- Cleanup +DROP TABLE test_local_small; +DROP TABLE test_local_medium; +DROP FOREIGN TABLE test_no_rescan_ft; +DROP SERVER no_rescan_server; +DROP EXTENSION no_rescan_test_fdw; diff --git a/src/test/fdw/no_rescan_test_extension/Makefile b/src/test/fdw/no_rescan_test_extension/Makefile new file mode 100644 index 00000000000..30fe4b94505 --- /dev/null +++ b/src/test/fdw/no_rescan_test_extension/Makefile @@ -0,0 +1,16 @@ +MODULES = no_rescan_test_fdw + +EXTENSION = no_rescan_test_fdw +DATA = no_rescan_test_fdw--1.0.sql +PGFILEDESC = "no_rescan_test_fdw - a dummy extension to test FDW handling not rescannable" + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = src/test/fdw/no_rescan_test_extension +top_builddir = ../../../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/src/test/fdw/no_rescan_test_extension/no_rescan_test_fdw--1.0.sql b/src/test/fdw/no_rescan_test_extension/no_rescan_test_fdw--1.0.sql new file mode 100644 index 00000000000..a1a9e5c8700 --- /dev/null +++ b/src/test/fdw/no_rescan_test_extension/no_rescan_test_fdw--1.0.sql @@ -0,0 +1,9 @@ +\echo Use "CREATE EXTENSION" to load this file. \quit + +CREATE FUNCTION no_rescan_test_fdw_handler() +RETURNS fdw_handler +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT; + +CREATE FOREIGN DATA WRAPPER no_rescan_test_fdw + HANDLER no_rescan_test_fdw_handler; diff --git a/src/test/fdw/no_rescan_test_extension/no_rescan_test_fdw.c b/src/test/fdw/no_rescan_test_extension/no_rescan_test_fdw.c new file mode 100644 index 00000000000..ff4ecad6dad --- /dev/null +++ b/src/test/fdw/no_rescan_test_extension/no_rescan_test_fdw.c @@ -0,0 +1,303 @@ +/* + * no_rescan_test_fdw.c + * + * Test FDW that intentionally does NOT implement ReScanForeignScan + * to demonstrate automatic materialization by the planner. + * + * This FDW generates simple test data (id, data) and can be used + * in join queries to verify that the planner automatically inserts + * a Material node when rescanning is required. + */ + +#include "postgres.h" + +#include "access/reloptions.h" +#include "catalog/pg_type.h" +#include "foreign/fdwapi.h" +#include "funcapi.h" +#include "nodes/pg_list.h" +#include "optimizer/pathnode.h" +#include "optimizer/planmain.h" +#include "optimizer/restrictinfo.h" +#include "utils/builtins.h" +#include "utils/rel.h" + +PG_MODULE_MAGIC; + +/* + * FDW-specific information for a foreign table. + */ +typedef struct NoRescanFdwPlanState +{ + int num_rows; /* Number of rows to generate */ +} NoRescanFdwPlanState; + +/* + * Execution state for a foreign scan. + */ +typedef struct NoRescanFdwExecState +{ + int current_row; /* Current row number (0-based) */ + int max_rows; /* Maximum number of rows to generate */ + bool scan_started; /* Has scan been started? */ +} NoRescanFdwExecState; + +/* FDW callback functions */ +PG_FUNCTION_INFO_V1(no_rescan_test_fdw_handler); + +static void noRescanGetForeignRelSize(PlannerInfo *root, + RelOptInfo *baserel, + Oid foreigntableid); +static void noRescanGetForeignPaths(PlannerInfo *root, + RelOptInfo *baserel, + Oid foreigntableid); +static ForeignScan *noRescanGetForeignPlan(PlannerInfo *root, + RelOptInfo *baserel, + Oid foreigntableid, + ForeignPath *best_path, + List *tlist, + List *scan_clauses, + Plan *outer_plan); +static void noRescanBeginForeignScan(ForeignScanState *node, + int eflags); +static TupleTableSlot *noRescanIterateForeignScan(ForeignScanState *node); +static void noRescanEndForeignScan(ForeignScanState *node); + +/* Note: We intentionally DO NOT implement ReScanForeignScan */ + +/* + * Foreign-data wrapper handler function + */ +Datum +no_rescan_test_fdw_handler(PG_FUNCTION_ARGS) +{ + FdwRoutine *routine = makeNode(FdwRoutine); + + /* Mandatory planning functions */ + routine->GetForeignRelSize = noRescanGetForeignRelSize; + routine->GetForeignPaths = noRescanGetForeignPaths; + routine->GetForeignPlan = noRescanGetForeignPlan; + + /* Mandatory execution functions */ + routine->BeginForeignScan = noRescanBeginForeignScan; + routine->IterateForeignScan = noRescanIterateForeignScan; + routine->EndForeignScan = noRescanEndForeignScan; + + /* + * CRITICAL: We intentionally leave ReScanForeignScan as NULL. + * This demonstrates that the planner will automatically insert + * a Material node when this FDW is used in scenarios requiring + * rescanning (e.g., nested loop joins). + */ + routine->ReScanForeignScan = NULL; + + PG_RETURN_POINTER(routine); +} + +/* + * Estimate relation size and cost. + */ +static void +noRescanGetForeignRelSize(PlannerInfo *root, + RelOptInfo *baserel, + Oid foreigntableid) +{ + NoRescanFdwPlanState *fdw_private; + + /* + * For simplicity, we'll generate 10 rows. + * In a real FDW, you might read this from table options. + */ + fdw_private = (NoRescanFdwPlanState *) palloc0(sizeof(NoRescanFdwPlanState)); + fdw_private->num_rows = 10; + + baserel->rows = fdw_private->num_rows; + baserel->fdw_private = (void *) fdw_private; + + elog(DEBUG1, "no_rescan_test_fdw: GetForeignRelSize estimated %d rows", + fdw_private->num_rows); +} + +/* + * Create possible access paths. + */ +static void +noRescanGetForeignPaths(PlannerInfo *root, + RelOptInfo *baserel, + Oid foreigntableid) +{ + Cost startup_cost = 10; + Cost total_cost = startup_cost + (baserel->rows * 0.01); + ForeignPath *path; + + /* + * Create a simple non-parameterized ForeignPath. + * + * The key point: because we don't implement ReScanForeignScan, + * create_foreignscan_path will set path.rescannable = false. + * This allows the planner to automatically insert Material nodes + * for non-parameterized rescans (e.g., inner side of nested loops). + * + * For parameterized paths (required_outer != NULL), create_foreignscan_path + * will reject the path if we don't support rescan. This prevents generating + * plans that would fail at execution time in regular joins. + * + * However, correlated subqueries (SubPlans) are a special case: they are + * planned independently and the foreign table doesn't know it will be used + * in a SubPlan that requires rescanning. These will fail at execution time + * with "ERROR: foreign-data wrapper does not support ReScan". + */ + path = create_foreignscan_path(root, baserel, + NULL, /* default pathtarget */ + baserel->rows, + 0, /* disabled_nodes */ + startup_cost, + total_cost, + NIL, /* no pathkeys */ + NULL, /* no required_outer */ + NULL, /* no fdw_outerpath */ + NIL, /* no fdw_restrictinfo */ + NIL); /* no fdw_private */ + + if (path != NULL) + { + add_path(baserel, (Path *) path); + + elog(DEBUG1, "no_rescan_test_fdw: Added foreign path (rescannable=%d)", + path->path.rescannable); + } +} + +/* + * Create a ForeignScan plan node. + */ +static ForeignScan * +noRescanGetForeignPlan(PlannerInfo *root, + RelOptInfo *baserel, + Oid foreigntableid, + ForeignPath *best_path, + List *tlist, + List *scan_clauses, + Plan *outer_plan) +{ + NoRescanFdwPlanState *fdw_private = (NoRescanFdwPlanState *) baserel->fdw_private; + List *fdw_private_list; + + /* Extract non-FDW clauses */ + scan_clauses = extract_actual_clauses(scan_clauses, false); + + /* Pass the number of rows to execution state via fdw_private */ + fdw_private_list = list_make1_int(fdw_private->num_rows); + + /* Create the ForeignScan node */ + return make_foreignscan(tlist, + scan_clauses, + baserel->relid, + NIL, /* no fdw_exprs */ + fdw_private_list, + NIL, /* no fdw_scan_tlist */ + NIL, /* no fdw_recheck_quals */ + outer_plan); +} + +/* + * Begin executing a foreign scan. + */ +static void +noRescanBeginForeignScan(ForeignScanState *node, + int eflags) +{ + ForeignScan *plan = (ForeignScan *) node->ss.ps.plan; + NoRescanFdwExecState *exec_state; + int num_rows; + + /* Extract the number of rows from fdw_private */ + if (plan->fdw_private != NIL) + num_rows = linitial_int(plan->fdw_private); + else + num_rows = 10; /* default */ + + /* Initialize execution state */ + exec_state = (NoRescanFdwExecState *) palloc0(sizeof(NoRescanFdwExecState)); + exec_state->current_row = 0; + exec_state->max_rows = num_rows; + exec_state->scan_started = true; + + node->fdw_state = (void *) exec_state; + + elog(NOTICE, "no_rescan_test_fdw: BeginForeignScan - will generate %d rows", + num_rows); +} + +/* + * Iterate and return the next tuple. + */ +static TupleTableSlot * +noRescanIterateForeignScan(ForeignScanState *node) +{ + TupleTableSlot *slot = node->ss.ss_ScanTupleSlot; + NoRescanFdwExecState *exec_state = (NoRescanFdwExecState *) node->fdw_state; + + /* Clear the slot */ + ExecClearTuple(slot); + + /* Generate rows until we reach max_rows */ + if (exec_state->current_row < exec_state->max_rows) + { + int row_id = exec_state->current_row + 1; + + /* + * Generate simple test data: + * - Column 1: integer id (1, 2, 3, ...) + * - Column 2: text data ("row_1", "row_2", ...) + * + * Fill values/isnull arrays directly in the slot + */ + slot->tts_values[0] = Int32GetDatum(row_id); + slot->tts_isnull[0] = false; + + slot->tts_values[1] = CStringGetTextDatum(psprintf("row_%d", row_id)); + slot->tts_isnull[1] = false; + + exec_state->current_row++; + + /* Store the virtual tuple in the slot */ + ExecStoreVirtualTuple(slot); + } + + return slot; +} + +/* + * End a foreign scan. + */ +static void +noRescanEndForeignScan(ForeignScanState *node) +{ + NoRescanFdwExecState *exec_state = (NoRescanFdwExecState *) node->fdw_state; + + if (exec_state) + { + elog(NOTICE, "no_rescan_test_fdw: EndForeignScan - scanned %d of %d rows", + exec_state->current_row, exec_state->max_rows); + pfree(exec_state); + } +} + +/* + * Note: We deliberately DO NOT implement ReScanForeignScan. + * This is the whole point of this test FDW - to demonstrate that + * the planner will automatically insert a Material node when + * rescanning is required. + * + * If you uncomment the following and add it to the FdwRoutine, + * you'll see that Material nodes are no longer inserted: + * + * static void + * noRescanReScanForeignScan(ForeignScanState *node) + * { + * NoRescanFdwExecState *exec_state = (NoRescanFdwExecState *) node->fdw_state; + * exec_state->current_row = 0; + * elog(NOTICE, "no_rescan_test_fdw: ReScan called"); + * } + */ diff --git a/src/test/fdw/no_rescan_test_extension/no_rescan_test_fdw.control b/src/test/fdw/no_rescan_test_extension/no_rescan_test_fdw.control new file mode 100644 index 00000000000..3ce333c07a7 --- /dev/null +++ b/src/test/fdw/no_rescan_test_extension/no_rescan_test_fdw.control @@ -0,0 +1,4 @@ +comment = 'Dummy extension to test FDW handling not rescannable' +default_version = '1.0' +module_pathname = '$libdir/no_rescan_test_fdw' +relocatable = true diff --git a/src/test/fdw/sql/no_rescan_test.sql b/src/test/fdw/sql/no_rescan_test.sql new file mode 100644 index 00000000000..586473de766 --- /dev/null +++ b/src/test/fdw/sql/no_rescan_test.sql @@ -0,0 +1,177 @@ +-- Test script for no_rescan_test_fdw +-- This demonstrates automatic materialization when FDW doesn't support rescan + +-- Configure planner to use Nested Loop Join so we can see Material nodes +SET enable_hashjoin = off; -- Disable Hash Join +SET enable_mergejoin = off; -- Disable Merge Join +SET enable_material = on; -- Ensure Material nodes are allowed +SET enable_nestloop = on; -- Ensure Nested Loop is allowed + +-- Create the extension +CREATE EXTENSION no_rescan_test_fdw; + +-- Create server +CREATE SERVER no_rescan_server FOREIGN DATA WRAPPER no_rescan_test_fdw; + +-- Create foreign table +-- The FDW will generate 10 rows with (id, data) columns +CREATE FOREIGN TABLE test_no_rescan_ft ( + id int, + data text +) SERVER no_rescan_server; + +-- Test 1: Simple scan (no rescan needed) +-- This should work fine without any Material node +SELECT * FROM test_no_rescan_ft ORDER BY id; + +-- Test 2: Verify the foreign scan works +EXPLAIN (COSTS OFF) SELECT * FROM test_no_rescan_ft; + +-- Note: EXPLAIN triggers BeginForeignScan/EndForeignScan for cost estimation +-- This is normal behavior and shows "scanned 0 of 10 rows" because EXPLAIN +-- doesn't actually fetch tuples, only initializes the scan + +-- Create a small local table for join testing +CREATE TABLE test_local_small ( + id int, + name text +); + +INSERT INTO test_local_small VALUES + (1, 'one'), + (2, 'two'), + (3, 'three'), + (4, 'four'), + (5, 'five'); + +-- Test 3: Nested Loop Join - Material node should be automatically inserted +-- Because we disabled hash/merge joins, planner will use Nested Loop +-- and because no_rescan_test_fdw doesn't provide ReScanForeignScan, +-- a Material node will be automatically inserted +EXPLAIN (COSTS OFF) +SELECT l.id, l.name, f.data +FROM test_local_small l +INNER JOIN test_no_rescan_ft f ON l.id = f.id +ORDER BY l.id; + +-- Expected plan +-- Gather Motion +-- -> Sort +-- -> Nested Loop +-- -> Seq Scan on test_local_small l +-- -> Material <-- Automatically inserted! +-- -> Foreign Scan on test_no_rescan_ft f + +-- Test 4: Execute the join to verify it works correctly +-- This should return 5 rows (only IDs 1-5 match) +SELECT l.id, l.name, f.data +FROM test_local_small l +INNER JOIN test_no_rescan_ft f ON l.id = f.id +ORDER BY l.id; + +-- The foreign scan will be executed once and materialized +-- Even though test_local_small has 5 rows, the Material node buffers the +-- foreign scan results, so we don't need to rescan + +-- Test 5: Alternative - using LATERAL join which naturally requires rescan +-- LATERAL joins require the inner side to be rescanned for each outer row +-- The Material node allows this even though the FDW doesn't support rescan +EXPLAIN (COSTS OFF) +SELECT l.id, l.name, f.data +FROM test_local_small l, + LATERAL (SELECT * FROM test_no_rescan_ft f WHERE f.id = l.id) f +ORDER BY l.id; + +-- Execute the LATERAL join - should return 5 rows +SELECT l.id, l.name, f.data +FROM test_local_small l, + LATERAL (SELECT * FROM test_no_rescan_ft f WHERE f.id = l.id) f +ORDER BY l.id; + +-- Test 6: More complex join scenario +CREATE TABLE test_local_medium ( + id int, + category text +); + +INSERT INTO test_local_medium +SELECT i, 'category_' || (i % 3) +FROM generate_series(1, 8) i; + +-- This should also show Material node with Nested Loop +EXPLAIN (COSTS OFF) +SELECT m.id, m.category, f.data +FROM test_local_medium m +INNER JOIN test_no_rescan_ft f ON m.id = f.id +WHERE m.id <= 7 +ORDER BY m.id; + +-- Execute the query - should return 7 rows (IDs 1-7) +SELECT m.id, m.category, f.data +FROM test_local_medium m +INNER JOIN test_no_rescan_ft f ON m.id = f.id +WHERE m.id <= 7 +ORDER BY m.id; + +-- Test 7: Verify that without rescan the query still works +-- (Material node buffers the data) +-- Should return count = 5 +SELECT count(*) +FROM test_local_small l1 +INNER JOIN test_local_small l2 ON l1.id = l2.id +INNER JOIN test_no_rescan_ft f ON l1.id = f.id; + +-- Test 8: Correlated subquery limitation - execution time error +-- A correlated subquery in the SELECT list creates a SubPlan that must rescan +-- the foreign table for each outer row. SubPlans are planned independently, +-- so the foreign table scan doesn't know it will need rescanning until execution. +-- This is a known limitation: FDWs without ReScanForeignScan cannot be used +-- in correlated subqueries. +EXPLAIN (COSTS OFF) +SELECT l.id, l.name, + (SELECT f.data FROM test_no_rescan_ft f WHERE f.id = l.id LIMIT 1) as fdata +FROM test_local_small l +ORDER BY l.id; + +-- Execution fails because each SubPlan execution requires rescanning the +-- foreign table with different parameter values, which is impossible without +-- the ReScanForeignScan callback. +SELECT l.id, l.name, + (SELECT f.data FROM test_no_rescan_ft f WHERE f.id = l.id LIMIT 1) as fdata +FROM test_local_small l +ORDER BY l.id; + +-- Test 9: LATERAL query - planner avoids parameterization +-- Even though this uses LATERAL syntax, the planner can convert it to a +-- regular nested loop join with a filter condition (l.id = f.id), avoiding +-- the need for a parameterized foreign scan path. A Material node is inserted +-- to buffer the foreign scan results for rescanning. +-- This works because the foreign scan itself doesn't need parameters - the +-- filtering happens after the scan. +EXPLAIN (COSTS OFF) +SELECT l.id, l.name, f.data +FROM test_local_small l, + LATERAL (SELECT * FROM test_no_rescan_ft f WHERE f.id = l.id) f +WHERE l.id < 3 +ORDER BY l.id; + +-- Executes successfully: Material buffers all foreign scan results, then +-- rescans from the buffer for each outer row while applying the join filter. +SELECT l.id, l.name, f.data +FROM test_local_small l, + LATERAL (SELECT * FROM test_no_rescan_ft f WHERE f.id = l.id) f +WHERE l.id < 3 +ORDER BY l.id; + +-- Reset planner settings +RESET enable_hashjoin; +RESET enable_mergejoin; +RESET enable_material; +RESET enable_nestloop; + +-- Cleanup +DROP TABLE test_local_small; +DROP TABLE test_local_medium; +DROP FOREIGN TABLE test_no_rescan_ft; +DROP SERVER no_rescan_server; +DROP EXTENSION no_rescan_test_fdw; -- 2.47.3
