v3-0002-postgres_fdw-Add-IMPORT-FOREIGN-SCHEMA-support-for-n.patch

From d774c5107fd68ff9d6edb4f9f23b79b9cbf71dc8 Mon Sep 17 00:00:00 2001
From: Etsuro Fujita <efujita@postgresql.org>
Date: Fri, 5 Jun 2026 20:35:19 +0900
Subject: [PATCH 2/2] postgres_fdw: Add IMPORT FOREIGN SCHEMA support for new
 option.

---
 .../postgres_fdw/expected/postgres_fdw.out    | 129 +++++++++-----
 contrib/postgres_fdw/postgres_fdw.c           | 162 ++++++++++++++----
 contrib/postgres_fdw/sql/postgres_fdw.sql     |  17 +-
 3 files changed, 234 insertions(+), 74 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index ff9c9e878e4..a631ea76dc0 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10092,16 +10092,16 @@ CREATE TABLE import_source.t4_part2 PARTITION OF import_source.t4
 CREATE SCHEMA import_dest1;
 IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
 \det+ import_dest1.*
-                                     List of foreign tables
-    Schema    | Table |  Server  |                   FDW options                   | Description 
---------------+-------+----------+-------------------------------------------------+-------------
- import_dest1 | t1    | loopback | (schema_name 'import_source', table_name 't1')  | 
- import_dest1 | t2    | loopback | (schema_name 'import_source', table_name 't2')  | 
- import_dest1 | t3    | loopback | (schema_name 'import_source', table_name 't3')  | 
- import_dest1 | t4    | loopback | (schema_name 'import_source', table_name 't4')  | 
- import_dest1 | x 4   | loopback | (schema_name 'import_source', table_name 'x 4') | 
- import_dest1 | x 5   | loopback | (schema_name 'import_source', table_name 'x 5') | 
- import_dest1 | x 6   | loopback | (schema_name 'import_source', table_name 'x 6') | 
+                                                  List of foreign tables
+    Schema    | Table |  Server  |                                FDW options                                | Description 
+--------------+-------+----------+---------------------------------------------------------------------------+-------------
+ import_dest1 | t1    | loopback | (schema_name 'import_source', table_name 't1')                            | 
+ import_dest1 | t2    | loopback | (schema_name 'import_source', table_name 't2')                            | 
+ import_dest1 | t3    | loopback | (schema_name 'import_source', table_name 't3')                            | 
+ import_dest1 | t4    | loopback | (schema_name 'import_source', table_name 't4', remotely_inherited 'true') | 
+ import_dest1 | x 4   | loopback | (schema_name 'import_source', table_name 'x 4')                           | 
+ import_dest1 | x 5   | loopback | (schema_name 'import_source', table_name 'x 5')                           | 
+ import_dest1 | x 6   | loopback | (schema_name 'import_source', table_name 'x 6')                           | 
 (7 rows)
 
 \d import_dest1.*
@@ -10135,7 +10135,7 @@ FDW options: (schema_name 'import_source', table_name 't3')
 --------+---------+-----------+----------+---------+--------------------
  c1     | integer |           |          |         | (column_name 'c1')
 Server: loopback
-FDW options: (schema_name 'import_source', table_name 't4')
+FDW options: (schema_name 'import_source', table_name 't4', remotely_inherited 'true')
 
                            Foreign table "import_dest1.x 4"
  Column |         Type          | Collation | Nullable | Default |     FDW options     
@@ -10165,16 +10165,16 @@ CREATE SCHEMA import_dest2;
 IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
   OPTIONS (import_default 'true');
 \det+ import_dest2.*
-                                     List of foreign tables
-    Schema    | Table |  Server  |                   FDW options                   | Description 
---------------+-------+----------+-------------------------------------------------+-------------
- import_dest2 | t1    | loopback | (schema_name 'import_source', table_name 't1')  | 
- import_dest2 | t2    | loopback | (schema_name 'import_source', table_name 't2')  | 
- import_dest2 | t3    | loopback | (schema_name 'import_source', table_name 't3')  | 
- import_dest2 | t4    | loopback | (schema_name 'import_source', table_name 't4')  | 
- import_dest2 | x 4   | loopback | (schema_name 'import_source', table_name 'x 4') | 
- import_dest2 | x 5   | loopback | (schema_name 'import_source', table_name 'x 5') | 
- import_dest2 | x 6   | loopback | (schema_name 'import_source', table_name 'x 6') | 
+                                                  List of foreign tables
+    Schema    | Table |  Server  |                                FDW options                                | Description 
+--------------+-------+----------+---------------------------------------------------------------------------+-------------
+ import_dest2 | t1    | loopback | (schema_name 'import_source', table_name 't1')                            | 
+ import_dest2 | t2    | loopback | (schema_name 'import_source', table_name 't2')                            | 
+ import_dest2 | t3    | loopback | (schema_name 'import_source', table_name 't3')                            | 
+ import_dest2 | t4    | loopback | (schema_name 'import_source', table_name 't4', remotely_inherited 'true') | 
+ import_dest2 | x 4   | loopback | (schema_name 'import_source', table_name 'x 4')                           | 
+ import_dest2 | x 5   | loopback | (schema_name 'import_source', table_name 'x 5')                           | 
+ import_dest2 | x 6   | loopback | (schema_name 'import_source', table_name 'x 6')                           | 
 (7 rows)
 
 \d import_dest2.*
@@ -10208,7 +10208,7 @@ FDW options: (schema_name 'import_source', table_name 't3')
 --------+---------+-----------+----------+---------+--------------------
  c1     | integer |           |          |         | (column_name 'c1')
 Server: loopback
-FDW options: (schema_name 'import_source', table_name 't4')
+FDW options: (schema_name 'import_source', table_name 't4', remotely_inherited 'true')
 
                            Foreign table "import_dest2.x 4"
  Column |         Type          | Collation | Nullable | Default |     FDW options     
@@ -10237,16 +10237,16 @@ CREATE SCHEMA import_dest3;
 IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
   OPTIONS (import_collate 'false', import_generated 'false', import_not_null 'false');
 \det+ import_dest3.*
-                                     List of foreign tables
-    Schema    | Table |  Server  |                   FDW options                   | Description 
---------------+-------+----------+-------------------------------------------------+-------------
- import_dest3 | t1    | loopback | (schema_name 'import_source', table_name 't1')  | 
- import_dest3 | t2    | loopback | (schema_name 'import_source', table_name 't2')  | 
- import_dest3 | t3    | loopback | (schema_name 'import_source', table_name 't3')  | 
- import_dest3 | t4    | loopback | (schema_name 'import_source', table_name 't4')  | 
- import_dest3 | x 4   | loopback | (schema_name 'import_source', table_name 'x 4') | 
- import_dest3 | x 5   | loopback | (schema_name 'import_source', table_name 'x 5') | 
- import_dest3 | x 6   | loopback | (schema_name 'import_source', table_name 'x 6') | 
+                                                  List of foreign tables
+    Schema    | Table |  Server  |                                FDW options                                | Description 
+--------------+-------+----------+---------------------------------------------------------------------------+-------------
+ import_dest3 | t1    | loopback | (schema_name 'import_source', table_name 't1')                            | 
+ import_dest3 | t2    | loopback | (schema_name 'import_source', table_name 't2')                            | 
+ import_dest3 | t3    | loopback | (schema_name 'import_source', table_name 't3')                            | 
+ import_dest3 | t4    | loopback | (schema_name 'import_source', table_name 't4', remotely_inherited 'true') | 
+ import_dest3 | x 4   | loopback | (schema_name 'import_source', table_name 'x 4')                           | 
+ import_dest3 | x 5   | loopback | (schema_name 'import_source', table_name 'x 5')                           | 
+ import_dest3 | x 6   | loopback | (schema_name 'import_source', table_name 'x 6')                           | 
 (7 rows)
 
 \d import_dest3.*
@@ -10280,7 +10280,7 @@ FDW options: (schema_name 'import_source', table_name 't3')
 --------+---------+-----------+----------+---------+--------------------
  c1     | integer |           |          |         | (column_name 'c1')
 Server: loopback
-FDW options: (schema_name 'import_source', table_name 't4')
+FDW options: (schema_name 'import_source', table_name 't4', remotely_inherited 'true')
 
                            Foreign table "import_dest3.x 4"
  Column |         Type          | Collation | Nullable | Default |     FDW options     
@@ -10320,16 +10320,16 @@ IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch, t4_part)
 IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part)
   FROM SERVER loopback INTO import_dest4;
 \det+ import_dest4.*
-                                        List of foreign tables
-    Schema    |  Table  |  Server  |                     FDW options                     | Description 
---------------+---------+----------+-----------------------------------------------------+-------------
- import_dest4 | t1      | loopback | (schema_name 'import_source', table_name 't1')      | 
- import_dest4 | t2      | loopback | (schema_name 'import_source', table_name 't2')      | 
- import_dest4 | t3      | loopback | (schema_name 'import_source', table_name 't3')      | 
- import_dest4 | t4      | loopback | (schema_name 'import_source', table_name 't4')      | 
- import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') | 
- import_dest4 | x 5     | loopback | (schema_name 'import_source', table_name 'x 5')     | 
- import_dest4 | x 6     | loopback | (schema_name 'import_source', table_name 'x 6')     | 
+                                                   List of foreign tables
+    Schema    |  Table  |  Server  |                                FDW options                                | Description 
+--------------+---------+----------+---------------------------------------------------------------------------+-------------
+ import_dest4 | t1      | loopback | (schema_name 'import_source', table_name 't1')                            | 
+ import_dest4 | t2      | loopback | (schema_name 'import_source', table_name 't2')                            | 
+ import_dest4 | t3      | loopback | (schema_name 'import_source', table_name 't3')                            | 
+ import_dest4 | t4      | loopback | (schema_name 'import_source', table_name 't4', remotely_inherited 'true') | 
+ import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part')                       | 
+ import_dest4 | x 5     | loopback | (schema_name 'import_source', table_name 'x 5')                           | 
+ import_dest4 | x 6     | loopback | (schema_name 'import_source', table_name 'x 6')                           | 
 (7 rows)
 
 -- Assorted error cases
@@ -10363,6 +10363,49 @@ QUERY:  CREATE FOREIGN TABLE t5 (
 OPTIONS (schema_name 'import_source', table_name 't5');
 CONTEXT:  importing foreign table "t5"
 ROLLBACK;
+-- Check that the remotely_inherited option is set when needed.
+CREATE TABLE import_source.inhchild (c1 int);
+CREATE TABLE import_source.t6 (c1 int);
+ALTER TABLE import_source.inhchild INHERIT import_source.t6;
+CREATE TABLE import_source.t7 (c1 int);
+ALTER TABLE import_source.inhchild INHERIT import_source.t7;
+ALTER TABLE import_source.inhchild NO INHERIT import_source.t7;
+CREATE FOREIGN TABLE import_source.t8 (c1 int) SERVER loopback
+  OPTIONS (remotely_inherited 'true');
+CREATE SCHEMA import_dest6;
+IMPORT FOREIGN SCHEMA import_source LIMIT TO (t6, t7, t8)
+  FROM SERVER loopback INTO import_dest6;
+\det+ import_dest6.*
+                                                  List of foreign tables
+    Schema    | Table |  Server  |                                FDW options                                | Description 
+--------------+-------+----------+---------------------------------------------------------------------------+-------------
+ import_dest6 | t6    | loopback | (schema_name 'import_source', table_name 't6', remotely_inherited 'true') | 
+ import_dest6 | t7    | loopback | (schema_name 'import_source', table_name 't7')                            | 
+ import_dest6 | t8    | loopback | (schema_name 'import_source', table_name 't8', remotely_inherited 'true') | 
+(3 rows)
+
+\d import_dest6.*
+                    Foreign table "import_dest6.t6"
+ Column |  Type   | Collation | Nullable | Default |    FDW options     
+--------+---------+-----------+----------+---------+--------------------
+ c1     | integer |           |          |         | (column_name 'c1')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 't6', remotely_inherited 'true')
+
+                    Foreign table "import_dest6.t7"
+ Column |  Type   | Collation | Nullable | Default |    FDW options     
+--------+---------+-----------+----------+---------+--------------------
+ c1     | integer |           |          |         | (column_name 'c1')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 't7')
+
+                    Foreign table "import_dest6.t8"
+ Column |  Type   | Collation | Nullable | Default |    FDW options     
+--------+---------+-----------+----------+---------+--------------------
+ c1     | integer |           |          |         | (column_name 'c1')
+Server: loopback
+FDW options: (schema_name 'import_source', table_name 't8', remotely_inherited 'true')
+
 BEGIN;
 CREATE SERVER fetch101 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( fetch_size '101' );
 SELECT count(*)
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 3466a8e70b5..8a5471b2b05 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -726,6 +726,9 @@ static bool import_fetched_statistics(const char *schemaname,
 static void map_field_to_arg(PGresult *res, int row, int field,
 							 int arg, Datum *values, char *nulls);
 static bool import_spi_query_ok(void);
+static void append_import_schema_restrictions(StringInfo buf,
+											  ImportForeignSchemaStmt *stmt,
+											  PGconn *conn);
 static void produce_tuple_asynchronously(AsyncRequest *areq, bool fetch);
 static void fetch_more_data_begin(AsyncRequest *areq);
 static void complete_pending_request(AsyncRequest *areq);
@@ -6389,7 +6392,10 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
 	PGconn	   *conn;
 	StringInfoData buf;
 	PGresult   *res;
-	int			numrows,
+	char	  **inherited = NULL;
+	int			numinherited,
+				inherited_idx,
+				numrows,
 				i;
 	ListCell   *lc;
 
@@ -6444,6 +6450,60 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
 	PQclear(res);
 	resetStringInfo(&buf);
 
+	/*
+	 * First, fetch/save all remotely-inherited table names from this schema,
+	 * possibly restricted by EXCEPT or LIMIT TO.
+	 */
+	appendStringInfoString(&buf,
+						   "SELECT relname "
+						   "FROM pg_class c "
+						   "  JOIN pg_namespace n ON "
+						   "    relnamespace = n.oid "
+						   "  LEFT JOIN (pg_foreign_table t "
+						   "    JOIN pg_foreign_server s ON "
+						   "      s.oid = t.ftserver "
+						   "    JOIN pg_foreign_data_wrapper w ON "
+						   "      w.oid = s.srvfdw) ON "
+						   "    t.ftrelid = c.oid "
+						   "WHERE (c.relkind = "
+						   CppAsString2(RELKIND_PARTITIONED_TABLE) " "
+						   "  OR (c.relkind IN ("
+						   CppAsString2(RELKIND_RELATION) ","
+						   CppAsString2(RELKIND_FOREIGN_TABLE) ") "
+						   "    AND c.relhassubclass "
+						   "    AND EXISTS (SELECT 1 FROM pg_inherits "
+						   "      WHERE inhparent = c.oid)) "
+						   "  OR (c.relkind = "
+						   CppAsString2(RELKIND_FOREIGN_TABLE) " "
+						   "    AND w.fdwname = \'postgres_fdw\' "
+						   "    AND t.ftoptions @> "
+						   "      ARRAY[\'remotely_inherited=true\'])) "
+						   "  AND n.nspname = ");
+	deparseStringLiteral(&buf, stmt->remote_schema);
+
+	/* Append EXCEPT/LIMIT TO restrictions */
+	append_import_schema_restrictions(&buf, stmt, conn);
+
+	/* Append ORDER BY at the end of query to ensure output ordering */
+	appendStringInfoString(&buf, " ORDER BY c.relname");
+
+	/* Fetch the data */
+	res = pgfdw_exec_query(conn, buf.data, NULL);
+	if (PQresultStatus(res) != PGRES_TUPLES_OK)
+		pgfdw_report_error(res, conn, buf.data);
+
+	/* Save the data */
+	numinherited = PQntuples(res);
+	if (numinherited > 0)
+	{
+		inherited = (char **) palloc0(numinherited * sizeof(char *));
+		for (i = 0; i < numinherited; i++)
+			inherited[i] = pstrdup(PQgetvalue(res, i, 0));
+	}
+
+	PQclear(res);
+	resetStringInfo(&buf);
+
 	/*
 	 * Fetch all table data from this schema, possibly restricted by EXCEPT or
 	 * LIMIT TO.  (We don't actually need to pay any attention to EXCEPT/LIMIT
@@ -6511,35 +6571,8 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
 						   "  AND n.nspname = ");
 	deparseStringLiteral(&buf, stmt->remote_schema);
 
-	/* Partitions are supported since Postgres 10 */
-	if (PQserverVersion(conn) >= 100000 &&
-		stmt->list_type != FDW_IMPORT_SCHEMA_LIMIT_TO)
-		appendStringInfoString(&buf, " AND NOT c.relispartition ");
-
-	/* Apply restrictions for LIMIT TO and EXCEPT */
-	if (stmt->list_type == FDW_IMPORT_SCHEMA_LIMIT_TO ||
-		stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
-	{
-		bool		first_item = true;
-
-		appendStringInfoString(&buf, " AND c.relname ");
-		if (stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
-			appendStringInfoString(&buf, "NOT ");
-		appendStringInfoString(&buf, "IN (");
-
-		/* Append list of table names within IN clause */
-		foreach(lc, stmt->table_list)
-		{
-			RangeVar   *rv = (RangeVar *) lfirst(lc);
-
-			if (first_item)
-				first_item = false;
-			else
-				appendStringInfoString(&buf, ", ");
-			deparseStringLiteral(&buf, rv->relname);
-		}
-		appendStringInfoChar(&buf, ')');
-	}
+	/* Append EXCEPT/LIMIT TO restrictions */
+	append_import_schema_restrictions(&buf, stmt, conn);
 
 	/* Append ORDER BY at the end of query to ensure output ordering */
 	appendStringInfoString(&buf, " ORDER BY c.relname, a.attnum");
@@ -6551,6 +6584,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
 
 	/* Process results */
 	numrows = PQntuples(res);
+	inherited_idx = 0;
 	/* note: incrementation of i happens in inner loop's while() test */
 	for (i = 0; i < numrows;)
 	{
@@ -6647,17 +6681,85 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
 		appendStringInfoString(&buf, ", table_name ");
 		deparseStringLiteral(&buf, tablename);
 
+		/*
+		 * Also add the remotely_inherited option if needed, to prevent unsafe
+		 * modifications to the foreign table (see check_result_rel()).
+		 *
+		 * By the definitions of the fetch queries using the same snapshot on
+		 * the remote server, the inherited is guaranteed to be a strictly
+		 * proper subset of the data processed here with the same order as it,
+		 * so we determine whether the foreign table is remotely-inherited or
+		 * not, by doing a merge join to it.
+		 */
+		if (numinherited > 0 && inherited_idx < numinherited &&
+			strcmp(tablename, inherited[inherited_idx]) == 0)
+		{
+			appendStringInfoString(&buf, ", remotely_inherited \'true\'");
+			inherited_idx++;
+		}
+
 		appendStringInfoString(&buf, ");");
 
 		commands = lappend(commands, pstrdup(buf.data));
 	}
 	PQclear(res);
 
+	if (numinherited > 0)
+	{
+		Assert(inherited != NULL);
+		for (i = 0; i < numinherited; i++)
+		{
+			Assert(inherited[i] != NULL);
+			pfree(inherited[i]);
+		}
+		pfree(inherited);
+	}
+
 	ReleaseConnection(conn);
 
 	return commands;
 }
 
+/*
+ * Append EXCEPT/LIMIT TO restrictions to a query.
+ */
+static void
+append_import_schema_restrictions(StringInfo buf,
+								  ImportForeignSchemaStmt *stmt,
+								  PGconn *conn)
+{
+	/* Partitions are supported since Postgres 10 */
+	if (PQserverVersion(conn) >= 100000 &&
+		stmt->list_type != FDW_IMPORT_SCHEMA_LIMIT_TO)
+		appendStringInfoString(buf, " AND NOT c.relispartition ");
+
+	/* Apply restrictions for LIMIT TO and EXCEPT */
+	if (stmt->list_type == FDW_IMPORT_SCHEMA_LIMIT_TO ||
+		stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
+	{
+		bool		first_item = true;
+		ListCell   *lc;
+
+		appendStringInfoString(buf, " AND c.relname ");
+		if (stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
+			appendStringInfoString(buf, "NOT ");
+		appendStringInfoString(buf, "IN (");
+
+		/* Append list of table names within IN clause */
+		foreach(lc, stmt->table_list)
+		{
+			RangeVar   *rv = (RangeVar *) lfirst(lc);
+
+			if (first_item)
+				first_item = false;
+			else
+				appendStringInfoString(buf, ", ");
+			deparseStringLiteral(buf, rv->relname);
+		}
+		appendStringInfoChar(buf, ')');
+	}
+}
+
 /*
  * Check if reltarget is safe enough to push down semi-join.  Reltarget is not
  * safe, if it contains references to inner rel relids, which do not belong to
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 31d5ea8a47d..12b7ad2235b 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3275,8 +3275,23 @@ IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5)
 
 ROLLBACK;
 
-BEGIN;
+-- Check that the remotely_inherited option is set when needed.
+CREATE TABLE import_source.inhchild (c1 int);
+CREATE TABLE import_source.t6 (c1 int);
+ALTER TABLE import_source.inhchild INHERIT import_source.t6;
+CREATE TABLE import_source.t7 (c1 int);
+ALTER TABLE import_source.inhchild INHERIT import_source.t7;
+ALTER TABLE import_source.inhchild NO INHERIT import_source.t7;
+CREATE FOREIGN TABLE import_source.t8 (c1 int) SERVER loopback
+  OPTIONS (remotely_inherited 'true');
+
+CREATE SCHEMA import_dest6;
+IMPORT FOREIGN SCHEMA import_source LIMIT TO (t6, t7, t8)
+  FROM SERVER loopback INTO import_dest6;
+\det+ import_dest6.*
+\d import_dest6.*
 
+BEGIN;
 
 CREATE SERVER fetch101 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( fetch_size '101' );
 
-- 
2.50.1 (Apple Git-155)