Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita3740c4

Browse files
committed
postgres_fdw: Allow partitions specified in LIMIT TO to be imported.
Commitf49bcd4 disallowed postgres_fdw to import table partitions.Because all data can be accessed through the partitioned table whichis the root of the partitioning hierarchy, importing only partitionedtable should allow access to all the data without creating extra objects.This is a reasonable default when importing a whole schema. But theremay be the case where users want to explicitly import one ofa partitioned tables' partitions.For that use case, this commit allows postgres_fdw to import tables orforeign tables which are partitions of some other table only when theyare explicitly specified in LIMIT TO clause. It doesn't changethe behavior that any partitions not specified in LIMIT TO areautomatically excluded in IMPORT FOREIGN SCHEMA command.Author: Matthias van de MeentReviewed-by: Bernd Helmle, Amit Langote, Michael Paquier, Fujii MasaoDiscussion:https://postgr.es/m/CAEze2Whwg4i=mzApMe+PXxCEfgoZmHGqdqQFW7J4bmj_5p6t1A@mail.gmail.com
1 parent90c885c commita3740c4

File tree

4 files changed

+37
-26
lines changed

4 files changed

+37
-26
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 20 additions & 16 deletions
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

Lines changed: 7 additions & 4 deletions
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

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2366,6 +2366,8 @@ ALTER TABLE import_source."x 5" DROP COLUMN c1;
23662366
CREATETABLEimport_source.t4 (c1int) PARTITION BY RANGE (c1);
23672367
CREATETABLEimport_source.t4_part PARTITION OFimport_source.t4
23682368
FORVALUESFROM (1) TO (100);
2369+
CREATETABLEimport_source.t4_part2 PARTITION OFimport_source.t4
2370+
FORVALUESFROM (100) TO (200);
23692371

23702372
CREATESCHEMAimport_dest1;
23712373
IMPORT FOREIGN SCHEMA import_sourceFROM 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
CREATESCHEMAimport_dest4;
2389-
IMPORT FOREIGN SCHEMA import_sourceLIMIT TO (t1, nonesuch)
2391+
IMPORT FOREIGN SCHEMA import_sourceLIMIT 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

Lines changed: 6 additions & 4 deletions
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)
[8]ページ先頭
©2009-2025 Movatter.jp