From f69b961d743b3fdd647b761d6069d8fa9163e2c7 Mon Sep 17 00:00:00 2001 From: Matthias van de Meent Date: Thu, 21 Jan 2021 13:03:32 +0100 Subject: [PATCH v1] Update postgres_fdw to import partitions when named in IMPORT SCHEMA LIMIT TO. --- .../postgres_fdw/expected/postgres_fdw.out | 34 ++++++++++--------- contrib/postgres_fdw/postgres_fdw.c | 3 +- contrib/postgres_fdw/sql/postgres_fdw.sql | 4 +-- doc/src/sgml/postgres-fdw.sgml | 12 +++---- 4 files changed, 28 insertions(+), 25 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index b4a04d2c14..cb04e7a2ab 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -8435,27 +8435,29 @@ FDW options: (schema_name 'import_source', table_name 'x 5') -- Check LIMIT TO and EXCEPT CREATE SCHEMA import_dest4; -IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch) +IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, 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') | -(1 row) + List of foreign tables + Schema | Table | Server | FDW options | Description +--------------+---------+----------+-----------------------------------------------------+------------- + import_dest4 | t1 | loopback | (schema_name 'import_source', table_name 't1') | + import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') | +(2 rows) -IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch) +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 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') | -(5 rows) + 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') | +(6 rows) -- Assorted error cases IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4; diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 9a31bbb86b..5f33d5cf1e 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -5017,7 +5017,8 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid) deparseStringLiteral(&buf, stmt->remote_schema); /* Partitions are supported since Postgres 10 */ - if (PQserverVersion(conn) >= 100000) + 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 */ diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 28b82f5f9d..7b5710dea6 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -2373,10 +2373,10 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3 -- Check LIMIT TO and EXCEPT CREATE SCHEMA import_dest4; -IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch) +IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch, t4_part) FROM SERVER loopback INTO import_dest4; \det+ import_dest4.* -IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch) +IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part) FROM SERVER loopback INTO import_dest4; \det+ import_dest4.* diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml index fb4c22ac69..666ccd69d9 100644 --- a/doc/src/sgml/postgres-fdw.sgml +++ b/doc/src/sgml/postgres-fdw.sgml @@ -481,12 +481,12 @@ OPTIONS (ADD password_required 'false'); - Tables or foreign tables which are partitions of some other table are - automatically excluded. Partitioned tables are imported, unless they - are a partition of some other table. Since all data can be accessed - through the partitioned table which is the root of the partitioning - hierarchy, this approach should allow access to all the data without - creating extra objects. + All tables or foreign tables which are partitions of some other table + are automatically excluded from + unless they are explicitly included in the LIMIT TO + clause. Since all data can be accessed through the partitioned table + which is the root of the partitioning hierarchy, this approach should + allow access to all the data without creating extra objects. -- 2.20.1