diff options
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 36 | ||||
| -rw-r--r-- | contrib/postgres_fdw/postgres_fdw.c | 11 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 6 | ||||
| -rw-r--r-- | doc/src/sgml/postgres-fdw.sgml | 10 |
4 files changed, 37 insertions, 26 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 59e4e27ffbe..9d472d2d3d6 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -8228,6 +8228,8 @@ ALTER TABLE import_source."x 5" DROP COLUMN c1; CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1); CREATE TABLE import_source.t4_part PARTITION OF import_source.t4 FOR VALUES FROM (1) TO (100); +CREATE TABLE import_source.t4_part2 PARTITION OF import_source.t4 + FOR VALUES FROM (100) TO (200); CREATE SCHEMA import_dest1; IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1; \det+ import_dest1.* @@ -8419,27 +8421,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 16c2979f2d0..b6442070a35 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -5095,9 +5095,11 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid) * should save a few cycles to not process excluded tables in the * first place.) * - * Ignore table data for partitions and only include the definitions - * of the root partitioned tables to allow access to the complete - * remote data set locally in the schema imported. + * Import table data for partitions only when they are explicitly + * specified in LIMIT TO clause. Otherwise ignore them and only + * include the definitions of the root partitioned tables to allow + * access to the complete remote data set locally in the schema + * imported. * * Note: because we run the connection with search_path restricted to * pg_catalog, the format_type() and pg_get_expr() outputs will always @@ -5153,7 +5155,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 107d1c0e030..3b4f90a99ca 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -2366,6 +2366,8 @@ ALTER TABLE import_source."x 5" DROP COLUMN c1; CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1); CREATE TABLE import_source.t4_part PARTITION OF import_source.t4 FOR VALUES FROM (1) TO (100); +CREATE TABLE import_source.t4_part2 PARTITION OF import_source.t4 + FOR VALUES FROM (100) TO (200); CREATE SCHEMA import_dest1; IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1; @@ -2386,10 +2388,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 a7c695b000f..b0792a13b13 100644 --- a/doc/src/sgml/postgres-fdw.sgml +++ b/doc/src/sgml/postgres-fdw.sgml @@ -510,10 +510,12 @@ OPTIONS (ADD password_required 'false'); <para> 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 + imported only when they are explicitly specified in + <literal>LIMIT TO</literal> clause. Otherwise they are automatically + excluded from <xref linkend="sql-importforeignschema"/>. + Since all data can be accessed through the partitioned table + which is the root of the partitioning hierarchy, importing only + partitioned tables should allow access to all the data without creating extra objects. </para> |