Skip to content

Commit a3740c4

Browse files
MasaoFujiiMasaoFujii
committed
postgres_fdw: Allow partitions specified in LIMIT TO to be imported.
Commit f49bcd4 disallowed postgres_fdw to import table partitions. Because all data can be accessed through the partitioned table which is the root of the partitioning hierarchy, importing only partitioned table should allow access to all the data without creating extra objects. This is a reasonable default when importing a whole schema. But there may be the case where users want to explicitly import one of a partitioned tables' partitions. For that use case, this commit allows postgres_fdw to import tables or foreign tables which are partitions of some other table only when they are explicitly specified in LIMIT TO clause. It doesn't change the behavior that any partitions not specified in LIMIT TO are automatically excluded in IMPORT FOREIGN SCHEMA command. Author: Matthias van de Meent Reviewed-by: Bernd Helmle, Amit Langote, Michael Paquier, Fujii Masao Discussion: https://postgr.es/m/CAEze2Whwg4i=mzApMe+PXxCEfgoZmHGqdqQFW7J4bmj_5p6t1A@mail.gmail.com
1 parent 90c885c commit a3740c4

File tree

4 files changed

+37
-26
lines changed

4 files changed

+37
-26
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

+20-16
Original file line numberDiff line numberDiff line change
@@ -8228,6 +8228,8 @@ ALTER TABLE import_source."x 5" DROP COLUMN c1;
82288228
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
82298229
CREATE TABLE import_source.t4_part PARTITION OF import_source.t4
82308230
FOR VALUES FROM (1) TO (100);
8231+
CREATE TABLE import_source.t4_part2 PARTITION OF import_source.t4
8232+
FOR VALUES FROM (100) TO (200);
82318233
CREATE SCHEMA import_dest1;
82328234
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
82338235
\det+ import_dest1.*
@@ -8419,27 +8421,29 @@ FDW options: (schema_name 'import_source', table_name 'x 5')
84198421

84208422
-- Check LIMIT TO and EXCEPT
84218423
CREATE SCHEMA import_dest4;
8422-
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch)
8424+
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch, t4_part)
84238425
FROM SERVER loopback INTO import_dest4;
84248426
\det+ import_dest4.*
8425-
List of foreign tables
8426-
Schema | Table | Server | FDW options | Description
8427-
--------------+-------+----------+------------------------------------------------+-------------
8428-
import_dest4 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
8429-
(1 row)
8427+
List of foreign tables
8428+
Schema | Table | Server | FDW options | Description
8429+
--------------+---------+----------+-----------------------------------------------------+-------------
8430+
import_dest4 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
8431+
import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') |
8432+
(2 rows)
84308433

8431-
IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch)
8434+
IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part)
84328435
FROM SERVER loopback INTO import_dest4;
84338436
\det+ import_dest4.*
8434-
List of foreign tables
8435-
Schema | Table | Server | FDW options | Description
8436-
--------------+-------+----------+-------------------------------------------------+-------------
8437-
import_dest4 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
8438-
import_dest4 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
8439-
import_dest4 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
8440-
import_dest4 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
8441-
import_dest4 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
8442-
(5 rows)
8437+
List of foreign tables
8438+
Schema | Table | Server | FDW options | Description
8439+
--------------+---------+----------+-----------------------------------------------------+-------------
8440+
import_dest4 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
8441+
import_dest4 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
8442+
import_dest4 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
8443+
import_dest4 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
8444+
import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') |
8445+
import_dest4 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
8446+
(6 rows)
84438447

84448448
-- Assorted error cases
84458449
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4;

‎contrib/postgres_fdw/postgres_fdw.c

+7-4
Original file line numberDiff line numberDiff line change
@@ -5095,9 +5095,11 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
50955095
* should save a few cycles to not process excluded tables in the
50965096
* first place.)
50975097
*
5098-
* Ignore table data for partitions and only include the definitions
5099-
* of the root partitioned tables to allow access to the complete
5100-
* remote data set locally in the schema imported.
5098+
* Import table data for partitions only when they are explicitly
5099+
* specified in LIMIT TO clause. Otherwise ignore them and only
5100+
* include the definitions of the root partitioned tables to allow
5101+
* access to the complete remote data set locally in the schema
5102+
* imported.
51015103
*
51025104
* Note: because we run the connection with search_path restricted to
51035105
* pg_catalog, the format_type() and pg_get_expr() outputs will always
@@ -5153,7 +5155,8 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
51535155
deparseStringLiteral(&buf, stmt->remote_schema);
51545156

51555157
/* Partitions are supported since Postgres 10 */
5156-
if (PQserverVersion(conn) >= 100000)
5158+
if (PQserverVersion(conn) >= 100000 &&
5159+
stmt->list_type != FDW_IMPORT_SCHEMA_LIMIT_TO)
51575160
appendStringInfoString(&buf, " AND NOT c.relispartition ");
51585161

51595162
/* Apply restrictions for LIMIT TO and EXCEPT */

‎contrib/postgres_fdw/sql/postgres_fdw.sql

+4-2
Original file line numberDiff line numberDiff line change
@@ -2366,6 +2366,8 @@ ALTER TABLE import_source."x 5" DROP COLUMN c1;
23662366
CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1);
23672367
CREATE TABLE import_source.t4_part PARTITION OF import_source.t4
23682368
FOR VALUES FROM (1) TO (100);
2369+
CREATE TABLE import_source.t4_part2 PARTITION OF import_source.t4
2370+
FOR VALUES FROM (100) TO (200);
23692371

23702372
CREATE SCHEMA import_dest1;
23712373
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
23862388

23872389
-- Check LIMIT TO and EXCEPT
23882390
CREATE SCHEMA import_dest4;
2389-
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch)
2391+
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch, t4_part)
23902392
FROM SERVER loopback INTO import_dest4;
23912393
\det+ import_dest4.*
2392-
IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch)
2394+
IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part)
23932395
FROM SERVER loopback INTO import_dest4;
23942396
\det+ import_dest4.*
23952397

‎doc/src/sgml/postgres-fdw.sgml

+6-4
Original file line numberDiff line numberDiff line change
@@ -510,10 +510,12 @@ OPTIONS (ADD password_required 'false');
510510

511511
<para>
512512
Tables or foreign tables which are partitions of some other table are
513-
automatically excluded. Partitioned tables are imported, unless they
514-
are a partition of some other table. Since all data can be accessed
515-
through the partitioned table which is the root of the partitioning
516-
hierarchy, this approach should allow access to all the data without
513+
imported only when they are explicitly specified in
514+
<literal>LIMIT TO</literal> clause. Otherwise they are automatically
515+
excluded from <xref linkend="sql-importforeignschema"/>.
516+
Since all data can be accessed through the partitioned table
517+
which is the root of the partitioning hierarchy, importing only
518+
partitioned tables should allow access to all the data without
517519
creating extra objects.
518520
</para>
519521

0 commit comments

Comments
 (0)