Hi,
Recently I was trying to copy some of the data of one database to
another through postgres_fdw, and found that it wouldn't import that
partition through IMPORT FOREIGN SCHEMA, even when I explicitly
specified the name of the table that contained the data in the LIMIT
TO clause.
I realised the reason is that currently, postgres_fdw explicitly
disallows importing foreign partitions. This is a reasonable default
when importing a whole schema, but if I wanted to explicitly import
one of a partitioned tables' partitions, that would now require me to
manually copy the foreign table's definitions through the use of
CREATE FOREIGN TABLE, which is a hassle and prone to mistakes.
As such, I propose the attached patch, in which the 'no
partitions'-restriction of postgres_fdw is lifted for the LIMIT TO
clause. This has several benefits, including not holding locks on the
foreign root partition during queries, and less suprising behaviour
for LIMIT TO ("table that happens to be a partition").
Regards,
Matthias van de Meent
From f69b961d743b3fdd647b761d6069d8fa9163e2c7 Mon Sep 17 00:00:00 2001
From: Matthias van de Meent <boekew...@gmail.com>
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');
</para>
<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
- creating extra objects.
+ All tables or foreign tables which are partitions of some other table
+ are automatically excluded from <xref linkend="sql-importforeignschema"/>
+ unless they are explicitly included in the <literal>LIMIT TO</literal>
+ 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.
</para>
</sect3>
--
2.20.1
