postgres_fdw IMPORT SCHEMA and partitioned tables
Greetings,
While reviewing Amit Langote's patch to handle partitioned tables
properly in various contrib modules (mostly by throwing an error since
things like pageinspect aren't going to work on the empty 'parent'
table), I went looking through contrib for other modules that do
something with relkind and noticed that postgres_fdw's IMPORT SCHEMA
would pull in the child tables (relkind = 'r') but would ignore the
parent table (relkind = 'P', or soon to be 'p', I guess).
I tend to view this as an issue which should be added to the open items
list and resolved before PG10 (though perhaps it could be done after
feature freeze), but I could see an argument that it should be just a
documented limitation of postgres_fdw and that adding such support would
be a new feature.
In any case, this seems like an issue that should be addressed one way
or the other, so I'll add it to the open items list. I'm not planning
to work on fixing it myself, but if someone proposes a patch which looks
reasonable, I'll try to find time for it.
Thanks!
Stephen
On Thu, Mar 9, 2017 at 11:15 PM, Stephen Frost <sfrost@snowman.net> wrote:
While reviewing Amit Langote's patch to handle partitioned tables
properly in various contrib modules (mostly by throwing an error since
things like pageinspect aren't going to work on the empty 'parent'
table), I went looking through contrib for other modules that do
something with relkind and noticed that postgres_fdw's IMPORT SCHEMA
would pull in the child tables (relkind = 'r') but would ignore the
parent table (relkind = 'P', or soon to be 'p', I guess).
It is not as straight-forward as it seems. A foreign table can be
defined as a child (use of PARTITION OF), but not as a parent (use
PARTITION BY), and IMPORT SCHEMA has to issue queries to create
foreign tables. It seems to me that the correct fix here is to ignore
child tables that are part of a partition, and just include the parent
in what is imported so as when querying the parent through
postgres_fdw all the child partitions are considered automatically.
Thoughts?
Of course this should be documented.
I tend to view this as an issue which should be added to the open items
list and resolved before PG10 (though perhaps it could be done after
feature freeze), but I could see an argument that it should be just a
documented limitation of postgres_fdw and that adding such support would
be a new feature.
Agreed. I think that this is a bug, because any database having one
partitioning set of tables would fail to import automatically except
by excluding some tables, and that's annoying for the user.
In any case, this seems like an issue that should be addressed one way
or the other, so I'll add it to the open items list. I'm not planning
to work on fixing it myself, but if someone proposes a patch which looks
reasonable, I'll try to find time for it.
Well, I worked on IMPORT SCHEMA, so I'm fine to do something. After
the CF is done though.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017/03/10 10:26, Michael Paquier wrote:
On Thu, Mar 9, 2017 at 11:15 PM, Stephen Frost <sfrost@snowman.net> wrote:
While reviewing Amit Langote's patch to handle partitioned tables
properly in various contrib modules (mostly by throwing an error since
things like pageinspect aren't going to work on the empty 'parent'
table), I went looking through contrib for other modules that do
something with relkind and noticed that postgres_fdw's IMPORT SCHEMA
would pull in the child tables (relkind = 'r') but would ignore the
parent table (relkind = 'P', or soon to be 'p', I guess).It is not as straight-forward as it seems. A foreign table can be
defined as a child (use of PARTITION OF), but not as a parent (use
PARTITION BY), and IMPORT SCHEMA has to issue queries to create
foreign tables. It seems to me that the correct fix here is to ignore
child tables that are part of a partition, and just include the parent
in what is imported so as when querying the parent through
postgres_fdw all the child partitions are considered automatically.
Thoughts?
I think that makes sense. The query in postgresImportForeignSchema() that
fetches the information about remote tables should be fixed to include
relkind = 'P' tables (partitioned tables) but exclude relispartition =
true (partitions). Something like below:
- "WHERE c.relkind IN ('r', 'v', 'f', 'm') "
+ "WHERE c.relkind IN ('r', 'v', 'f', 'm', 'P') "
+ " AND NOT c.relispartition "It means we don't import tables that are supposed to be partitions of some
table. If we allow importing the latter, we get access to those
partitions anyway.
I would like to hear more opinions of course.
Of course this should be documented.
+1
I tend to view this as an issue which should be added to the open items
list and resolved before PG10 (though perhaps it could be done after
feature freeze), but I could see an argument that it should be just a
documented limitation of postgres_fdw and that adding such support would
be a new feature.Agreed. I think that this is a bug, because any database having one
partitioning set of tables would fail to import automatically except
by excluding some tables, and that's annoying for the user.
Agreed too.
In any case, this seems like an issue that should be addressed one way
or the other, so I'll add it to the open items list. I'm not planning
to work on fixing it myself, but if someone proposes a patch which looks
reasonable, I'll try to find time for it.Well, I worked on IMPORT SCHEMA, so I'm fine to do something. After
the CF is done though.
Thanks.
Regards,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Mar 9, 2017 at 8:47 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
It is not as straight-forward as it seems. A foreign table can be
defined as a child (use of PARTITION OF), but not as a parent (use
PARTITION BY), and IMPORT SCHEMA has to issue queries to create
foreign tables. It seems to me that the correct fix here is to ignore
child tables that are part of a partition, and just include the parent
in what is imported so as when querying the parent through
postgres_fdw all the child partitions are considered automatically.
Thoughts?I think that makes sense. The query in postgresImportForeignSchema() that
fetches the information about remote tables should be fixed to include
relkind = 'P' tables (partitioned tables) but exclude relispartition =
true (partitions). Something like below:- "WHERE c.relkind IN ('r', 'v', 'f', 'm') " + "WHERE c.relkind IN ('r', 'v', 'f', 'm', 'P') " + " AND NOT c.relispartition "It means we don't import tables that are supposed to be partitions of some
table. If we allow importing the latter, we get access to those
partitions anyway.I would like to hear more opinions of course.
For the most part, I'm not very exercised about this either way. I
think that the above definition seems reasonably likely to be a useful
one, but I certainly wouldn't try to insist on it in the face of
opposition; I don't think it's 100% clear what users will want here.
However, if we're going to do something about this, I think it should
be done soon. Otherwise, I'm going to advocate for reclassifying this
issue from "open item" to "possible area for future development".
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Mar 29, 2017 at 12:22 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Thu, Mar 9, 2017 at 8:47 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:It is not as straight-forward as it seems. A foreign table can be
defined as a child (use of PARTITION OF), but not as a parent (use
PARTITION BY), and IMPORT SCHEMA has to issue queries to create
foreign tables. It seems to me that the correct fix here is to ignore
child tables that are part of a partition, and just include the parent
in what is imported so as when querying the parent through
postgres_fdw all the child partitions are considered automatically.
Thoughts?I think that makes sense. The query in postgresImportForeignSchema() that
fetches the information about remote tables should be fixed to include
relkind = 'P' tables (partitioned tables) but exclude relispartition =
true (partitions). Something like below:- "WHERE c.relkind IN ('r', 'v', 'f', 'm') " + "WHERE c.relkind IN ('r', 'v', 'f', 'm', 'P') " + " AND NOT c.relispartition "It means we don't import tables that are supposed to be partitions of some
table. If we allow importing the latter, we get access to those
partitions anyway.I would like to hear more opinions of course.
For the most part, I'm not very exercised about this either way. I
think that the above definition seems reasonably likely to be a useful
one, but I certainly wouldn't try to insist on it in the face of
opposition; I don't think it's 100% clear what users will want here.
Users like things that are friendly, and we are most likely going to
piss them off when using postgres_fdw if they need to list manually
each parent table from the IMPORT FOREIGN SCHEMA command.
However, if we're going to do something about this, I think it should
be done soon. Otherwise, I'm going to advocate for reclassifying this
issue from "open item" to "possible area for future development".
I was just waiting for the end of the CF before sending in a patch,
allocating now some time to look at some patches pending for reviews.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Mar 29, 2017 at 12:30 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
Users like things that are friendly, and we are most likely going to
piss them off when using postgres_fdw if they need to list manually
each parent table from the IMPORT FOREIGN SCHEMA command.However, if we're going to do something about this, I think it should
be done soon. Otherwise, I'm going to advocate for reclassifying this
issue from "open item" to "possible area for future development".I was just waiting for the end of the CF before sending in a patch,
allocating now some time to look at some patches pending for reviews.
And here is the promised patch to address this open item.
--
Michael
Attachments:
pgfdw-partitions.patchapplication/octet-stream; name=pgfdw-partitions.patchDownload
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index a466bf2079..1a9e6c87f6 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -6907,6 +6907,9 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
CREATE TABLE import_source."x 5" (c1 float8);
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 SCHEMA import_dest1;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
\det+ import_dest1.*
@@ -6916,9 +6919,10 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
import_dest1 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
import_dest1 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
import_dest1 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
+ import_dest1 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(5 rows)
+(6 rows)
\d import_dest1.*
Foreign table "import_dest1.t1"
@@ -6946,6 +6950,13 @@ FDW Options: (schema_name 'import_source', table_name 't2')
Server: loopback
FDW Options: (schema_name 'import_source', table_name 't3')
+ Foreign table "import_dest1.t4"
+ Column | Type | Collation | Nullable | Default | FDW Options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | not null | | (column_name 'c1')
+Server: loopback
+FDW Options: (schema_name 'import_source', table_name 't4')
+
Foreign table "import_dest1.x 4"
Column | Type | Collation | Nullable | Default | FDW Options
--------+-----------------------+-----------+----------+---------+---------------------
@@ -6972,9 +6983,10 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
import_dest2 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
import_dest2 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
import_dest2 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
+ import_dest2 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(5 rows)
+(6 rows)
\d import_dest2.*
Foreign table "import_dest2.t1"
@@ -7002,6 +7014,13 @@ FDW Options: (schema_name 'import_source', table_name 't2')
Server: loopback
FDW Options: (schema_name 'import_source', table_name 't3')
+ Foreign table "import_dest2.t4"
+ Column | Type | Collation | Nullable | Default | FDW Options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | not null | | (column_name 'c1')
+Server: loopback
+FDW Options: (schema_name 'import_source', table_name 't4')
+
Foreign table "import_dest2.x 4"
Column | Type | Collation | Nullable | Default | FDW Options
--------+-----------------------+-----------+----------+---------+---------------------
@@ -7027,9 +7046,10 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
import_dest3 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
import_dest3 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
import_dest3 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
+ import_dest3 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest3 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(5 rows)
+(6 rows)
\d import_dest3.*
Foreign table "import_dest3.t1"
@@ -7057,6 +7077,13 @@ FDW Options: (schema_name 'import_source', table_name 't2')
Server: loopback
FDW Options: (schema_name 'import_source', table_name 't3')
+ Foreign table "import_dest3.t4"
+ Column | Type | Collation | Nullable | Default | FDW Options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+Server: loopback
+FDW Options: (schema_name 'import_source', table_name 't4')
+
Foreign table "import_dest3.x 4"
Column | Type | Collation | Nullable | Default | FDW Options
--------+-----------------------+-----------+----------+---------+---------------------
@@ -7092,8 +7119,9 @@ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch)
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') |
-(4 rows)
+(5 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 03f14800b0..87f6866e33 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3849,6 +3849,10 @@ 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 parent
+ * definitions 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
* include a schema name for types/functions in other schemas, which
@@ -3897,10 +3901,15 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
CppAsString2(RELKIND_RELATION) ","
CppAsString2(RELKIND_VIEW) ","
CppAsString2(RELKIND_FOREIGN_TABLE) ","
- CppAsString2(RELKIND_MATVIEW) ") "
+ CppAsString2(RELKIND_MATVIEW) ","
+ CppAsString2(RELKIND_PARTITIONED_TABLE) ") "
" AND n.nspname = ");
deparseStringLiteral(&buf, stmt->remote_schema);
+ /* Partitions are supported since Postgres 10 */
+ if (PQserverVersion(conn) >= 100000)
+ appendStringInfoString(&buf, " AND NOT c.relispartition ");
+
/* Apply restrictions for LIMIT TO and EXCEPT */
if (stmt->list_type == FDW_IMPORT_SCHEMA_LIMIT_TO ||
stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 8f3edc13e1..cf70ca2c01 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1618,6 +1618,9 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
CREATE TABLE import_source."x 5" (c1 float8);
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 SCHEMA import_dest1;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 7a9b655d36..fcd78edf50 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -425,6 +425,13 @@
For more detail about the treatment of <literal>CHECK</> constraints on
foreign tables, see <xref linkend="sql-createforeigntable">.
</para>
+
+ <para>
+ For partitioned tables, partitions are automatically excluded from the
+ schema data imported. Only the definition of partitioned tables is included
+ to give access to the full data set of all partitions present remotely.
+ </para>
+
</sect3>
</sect2>
On 2017/03/31 13:23, Michael Paquier wrote:
On Wed, Mar 29, 2017 at 12:30 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:Users like things that are friendly, and we are most likely going to
piss them off when using postgres_fdw if they need to list manually
each parent table from the IMPORT FOREIGN SCHEMA command.However, if we're going to do something about this, I think it should
be done soon. Otherwise, I'm going to advocate for reclassifying this
issue from "open item" to "possible area for future development".I was just waiting for the end of the CF before sending in a patch,
allocating now some time to look at some patches pending for reviews.And here is the promised patch to address this open item.
Looks good to me, except maybe:
+
+ <para>
+ For partitioned tables, partitions are automatically excluded from the
+ schema data imported. Only the definition of partitioned tables is
included
+ to give access to the full data set of all partitions present remotely.
+ </para>
+Only the definitions of "root" partitioned tables, because when using
multi-level partitioning, there would be partitioned tables that won't be
included (because, relispartition=true).
If you agree, then this code comment too could use the same terminology:
+ * Ignore table data for partitions and only include the parent
+ * definitions to allow access to the complete remote data set
+ * locally in the schema imported.
+ *Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Mar 31, 2017 at 1:37 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
+ <para> + For partitioned tables, partitions are automatically excluded from the + schema data imported. Only the definition of partitioned tables is included + to give access to the full data set of all partitions present remotely. + </para> +Only the definitions of "root" partitioned tables, because when using
multi-level partitioning, there would be partitioned tables that won't be
included (because, relispartition=true).If you agree, then this code comment too could use the same terminology:
+ * Ignore table data for partitions and only include the parent + * definitions to allow access to the complete remote data set + * locally in the schema imported. + *
That makes sense. Updated this way as I have my hands on it now.
--
Michael
Attachments:
pgfdw-partitions-v2.patchapplication/octet-stream; name=pgfdw-partitions-v2.patchDownload
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index a466bf2079..1a9e6c87f6 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -6907,6 +6907,9 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
CREATE TABLE import_source."x 5" (c1 float8);
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 SCHEMA import_dest1;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
\det+ import_dest1.*
@@ -6916,9 +6919,10 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
import_dest1 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
import_dest1 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
import_dest1 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
+ import_dest1 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(5 rows)
+(6 rows)
\d import_dest1.*
Foreign table "import_dest1.t1"
@@ -6946,6 +6950,13 @@ FDW Options: (schema_name 'import_source', table_name 't2')
Server: loopback
FDW Options: (schema_name 'import_source', table_name 't3')
+ Foreign table "import_dest1.t4"
+ Column | Type | Collation | Nullable | Default | FDW Options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | not null | | (column_name 'c1')
+Server: loopback
+FDW Options: (schema_name 'import_source', table_name 't4')
+
Foreign table "import_dest1.x 4"
Column | Type | Collation | Nullable | Default | FDW Options
--------+-----------------------+-----------+----------+---------+---------------------
@@ -6972,9 +6983,10 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
import_dest2 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
import_dest2 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
import_dest2 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
+ import_dest2 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(5 rows)
+(6 rows)
\d import_dest2.*
Foreign table "import_dest2.t1"
@@ -7002,6 +7014,13 @@ FDW Options: (schema_name 'import_source', table_name 't2')
Server: loopback
FDW Options: (schema_name 'import_source', table_name 't3')
+ Foreign table "import_dest2.t4"
+ Column | Type | Collation | Nullable | Default | FDW Options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | not null | | (column_name 'c1')
+Server: loopback
+FDW Options: (schema_name 'import_source', table_name 't4')
+
Foreign table "import_dest2.x 4"
Column | Type | Collation | Nullable | Default | FDW Options
--------+-----------------------+-----------+----------+---------+---------------------
@@ -7027,9 +7046,10 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
import_dest3 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
import_dest3 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
import_dest3 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
+ import_dest3 | t4 | loopback | (schema_name 'import_source', table_name 't4') |
import_dest3 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
-(5 rows)
+(6 rows)
\d import_dest3.*
Foreign table "import_dest3.t1"
@@ -7057,6 +7077,13 @@ FDW Options: (schema_name 'import_source', table_name 't2')
Server: loopback
FDW Options: (schema_name 'import_source', table_name 't3')
+ Foreign table "import_dest3.t4"
+ Column | Type | Collation | Nullable | Default | FDW Options
+--------+---------+-----------+----------+---------+--------------------
+ c1 | integer | | | | (column_name 'c1')
+Server: loopback
+FDW Options: (schema_name 'import_source', table_name 't4')
+
Foreign table "import_dest3.x 4"
Column | Type | Collation | Nullable | Default | FDW Options
--------+-----------------------+-----------+----------+---------+---------------------
@@ -7092,8 +7119,9 @@ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch)
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') |
-(4 rows)
+(5 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 03f14800b0..54b938734a 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3849,6 +3849,10 @@ 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.
+ *
* Note: because we run the connection with search_path restricted to
* pg_catalog, the format_type() and pg_get_expr() outputs will always
* include a schema name for types/functions in other schemas, which
@@ -3897,10 +3901,15 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
CppAsString2(RELKIND_RELATION) ","
CppAsString2(RELKIND_VIEW) ","
CppAsString2(RELKIND_FOREIGN_TABLE) ","
- CppAsString2(RELKIND_MATVIEW) ") "
+ CppAsString2(RELKIND_MATVIEW) ","
+ CppAsString2(RELKIND_PARTITIONED_TABLE) ") "
" AND n.nspname = ");
deparseStringLiteral(&buf, stmt->remote_schema);
+ /* Partitions are supported since Postgres 10 */
+ if (PQserverVersion(conn) >= 100000)
+ appendStringInfoString(&buf, " AND NOT c.relispartition ");
+
/* Apply restrictions for LIMIT TO and EXCEPT */
if (stmt->list_type == FDW_IMPORT_SCHEMA_LIMIT_TO ||
stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 8f3edc13e1..cf70ca2c01 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1618,6 +1618,9 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
CREATE TABLE import_source."x 5" (c1 float8);
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 SCHEMA import_dest1;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 7a9b655d36..86c7b0051a 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -425,6 +425,14 @@
For more detail about the treatment of <literal>CHECK</> constraints on
foreign tables, see <xref linkend="sql-createforeigntable">.
</para>
+
+ <para>
+ For partitioned tables, partitions are automatically excluded from the
+ schema data imported. Only the definition of root partitioned tables is
+ included to give access to the full data set of all partitions present
+ remotely.
+ </para>
+
</sect3>
</sect2>
On 2017/03/31 13:46, Michael Paquier wrote:
On Fri, Mar 31, 2017 at 1:37 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:+ <para> + For partitioned tables, partitions are automatically excluded from the + schema data imported. Only the definition of partitioned tables is included + to give access to the full data set of all partitions present remotely. + </para> +Only the definitions of "root" partitioned tables, because when using
multi-level partitioning, there would be partitioned tables that won't be
included (because, relispartition=true).If you agree, then this code comment too could use the same terminology:
+ * Ignore table data for partitions and only include the parent + * definitions to allow access to the complete remote data set + * locally in the schema imported. + *That makes sense. Updated this way as I have my hands on it now.
Thanks, no more comments from my side.
Regards,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Mar 31, 2017 at 12:51 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
Thanks, no more comments from my side.
Committed after rewording the documentation.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017-03-31 15:25:19 -0400, Robert Haas wrote:
On Fri, Mar 31, 2017 at 12:51 AM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:Thanks, no more comments from my side.
Committed after rewording the documentation.
Hm. Wonder if something like that shouldn't be backpatched - because
otherwise using postgres_fdw from an old server against a newer one will
do weird stuff. I don't know what kind of policy we've committed to
with postgresImportForeignSchema...
Greetings,
Andres Freund
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Mar 31, 2017 at 3:31 PM, Andres Freund <andres@anarazel.de> wrote:
Committed after rewording the documentation.
Hm. Wonder if something like that shouldn't be backpatched - because
otherwise using postgres_fdw from an old server against a newer one will
do weird stuff. I don't know what kind of policy we've committed to
with postgresImportForeignSchema...
I don't think I'd like to promise that postgres_fdw will always be
forward-compatible. Backward-compatibility is hard enough already.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
On Fri, Mar 31, 2017 at 3:31 PM, Andres Freund <andres@anarazel.de> wrote:
Hm. Wonder if something like that shouldn't be backpatched - because
otherwise using postgres_fdw from an old server against a newer one will
do weird stuff. I don't know what kind of policy we've committed to
with postgresImportForeignSchema...
I don't think I'd like to promise that postgres_fdw will always be
forward-compatible. Backward-compatibility is hard enough already.
Unless I'm missing something, the behavior will be that an older
version will simply ignore remote partitioned tables (they will not
pass the relkind filter in the query). Seems pretty fail-soft,
so I think it's fine.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sat, Apr 1, 2017 at 4:55 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Fri, Mar 31, 2017 at 3:31 PM, Andres Freund <andres@anarazel.de> wrote:
Hm. Wonder if something like that shouldn't be backpatched - because
otherwise using postgres_fdw from an old server against a newer one will
do weird stuff. I don't know what kind of policy we've committed to
with postgresImportForeignSchema...I don't think I'd like to promise that postgres_fdw will always be
forward-compatible. Backward-compatibility is hard enough already.
Thanks for the commit.
Unless I'm missing something, the behavior will be that an older
version will simply ignore remote partitioned tables (they will not
pass the relkind filter in the query). Seems pretty fail-soft,
so I think it's fine.
Yeah, I would suggest to revisit that if we get actual complaints, but
I would not push much in favor of it. It's not an area where nothing
can be done to improve the user experience.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers