pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres_fdw IMPORT SCHEMA and partitioned tables
14+ messages / 6 participants
[nested] [flat]
Commits from this thread:
2017-03-31 f49bcd4ef3 postgres_fdw: Teach IMPORT FOREIGN SCHEMA about partitioning.
* postgres_fdw IMPORT SCHEMA and partitioned tables
@ 2017-03-09 14:15 Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Stephen Frost @ 2017-03-09 14:15 UTC (permalink / raw)
To: pgsql-hackers
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres_fdw IMPORT SCHEMA and partitioned tables
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
@ 2017-03-10 01:26 ` Michael Paquier <michael.paquier@gmail.com>
2017-03-10 01:47 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
0 siblings, 1 reply; 14+ messages in thread
From: Michael Paquier @ 2017-03-10 01:26 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-hackers
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres_fdw IMPORT SCHEMA and partitioned tables
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
@ 2017-03-10 01:47 ` Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-29 03:22 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Amit Langote @ 2017-03-10 01:47 UTC (permalink / raw)
To: Michael Paquier <michael.paquier@gmail.com>; Stephen Frost <sfrost@snowman.net>; +Cc: 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres_fdw IMPORT SCHEMA and partitioned tables
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-10 01:47 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
@ 2017-03-29 03:22 ` Robert Haas <robertmhaas@gmail.com>
2017-03-29 03:30 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Robert Haas @ 2017-03-29 03:22 UTC (permalink / raw)
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; +Cc: Michael Paquier <michael.paquier@gmail.com>; Stephen Frost <sfrost@snowman.net>; 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres_fdw IMPORT SCHEMA and partitioned tables
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-10 01:47 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-29 03:22 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
@ 2017-03-29 03:30 ` Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:23 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Michael Paquier @ 2017-03-29 03:30 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; Stephen Frost <sfrost@snowman.net>; 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres_fdw IMPORT SCHEMA and partitioned tables
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-10 01:47 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-29 03:22 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
2017-03-29 03:30 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
@ 2017-03-31 04:23 ` Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:37 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
0 siblings, 1 reply; 14+ messages in thread
From: Michael Paquier @ 2017-03-31 04:23 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; Stephen Frost <sfrost@snowman.net>; 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachments:
pgfdw-partitions.patch (application/octet-stream, 8.7K) download
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>
* Re: postgres_fdw IMPORT SCHEMA and partitioned tables
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-10 01:47 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-29 03:22 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
2017-03-29 03:30 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:23 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
@ 2017-03-31 04:37 ` Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 04:46 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Amit Langote @ 2017-03-31 04:37 UTC (permalink / raw)
To: Michael Paquier <michael.paquier@gmail.com>; Robert Haas <robertmhaas@gmail.com>; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-hackers
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres_fdw IMPORT SCHEMA and partitioned tables
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-10 01:47 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-29 03:22 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
2017-03-29 03:30 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:23 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:37 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
@ 2017-03-31 04:46 ` Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:51 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
0 siblings, 1 reply; 14+ messages in thread
From: Michael Paquier @ 2017-03-31 04:46 UTC (permalink / raw)
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; +Cc: Robert Haas <robertmhaas@gmail.com>; Stephen Frost <sfrost@snowman.net>; 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachments:
pgfdw-partitions-v2.patch (application/octet-stream, 8.7K) download
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>
* Re: postgres_fdw IMPORT SCHEMA and partitioned tables
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-10 01:47 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-29 03:22 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
2017-03-29 03:30 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:23 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:37 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 04:46 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
@ 2017-03-31 04:51 ` Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 19:25 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Amit Langote @ 2017-03-31 04:51 UTC (permalink / raw)
To: Michael Paquier <michael.paquier@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Stephen Frost <sfrost@snowman.net>; pgsql-hackers
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres_fdw IMPORT SCHEMA and partitioned tables
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-10 01:47 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-29 03:22 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
2017-03-29 03:30 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:23 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:37 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 04:46 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:51 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
@ 2017-03-31 19:25 ` Robert Haas <robertmhaas@gmail.com>
2017-03-31 19:31 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 14+ messages in thread
From: Robert Haas @ 2017-03-31 19:25 UTC (permalink / raw)
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; +Cc: Michael Paquier <michael.paquier@gmail.com>; Stephen Frost <sfrost@snowman.net>; 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres_fdw IMPORT SCHEMA and partitioned tables
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-10 01:47 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-29 03:22 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
2017-03-29 03:30 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:23 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:37 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 04:46 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:51 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 19:25 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
@ 2017-03-31 19:31 ` Andres Freund <andres@anarazel.de>
2017-03-31 19:32 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Andres Freund @ 2017-03-31 19:31 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; Michael Paquier <michael.paquier@gmail.com>; Stephen Frost <sfrost@snowman.net>; 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres_fdw IMPORT SCHEMA and partitioned tables
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-10 01:47 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-29 03:22 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
2017-03-29 03:30 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:23 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:37 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 04:46 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:51 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 19:25 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
2017-03-31 19:31 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Andres Freund <andres@anarazel.de>
@ 2017-03-31 19:32 ` Robert Haas <robertmhaas@gmail.com>
2017-03-31 19:55 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Robert Haas @ 2017-03-31 19:32 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; Michael Paquier <michael.paquier@gmail.com>; Stephen Frost <sfrost@snowman.net>; 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres_fdw IMPORT SCHEMA and partitioned tables
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-10 01:47 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-29 03:22 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
2017-03-29 03:30 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:23 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:37 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 04:46 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:51 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 19:25 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
2017-03-31 19:31 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Andres Freund <andres@anarazel.de>
2017-03-31 19:32 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
@ 2017-03-31 19:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-03-31 23:19 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2017-03-31 19:55 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Andres Freund <andres@anarazel.de>; Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; Michael Paquier <michael.paquier@gmail.com>; Stephen Frost <sfrost@snowman.net>; 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres_fdw IMPORT SCHEMA and partitioned tables
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-10 01:47 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-29 03:22 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
2017-03-29 03:30 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:23 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:37 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 04:46 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:51 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 19:25 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
2017-03-31 19:31 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Andres Freund <andres@anarazel.de>
2017-03-31 19:32 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Robert Haas <robertmhaas@gmail.com>
2017-03-31 19:55 ` Re: postgres_fdw IMPORT SCHEMA and partitioned tables Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-03-31 23:19 ` Michael Paquier <michael.paquier@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Michael Paquier @ 2017-03-31 23:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Andres Freund <andres@anarazel.de>; Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; Stephen Frost <sfrost@snowman.net>; 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
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2017-03-31 23:19 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-03-09 14:15 postgres_fdw IMPORT SCHEMA and partitioned tables Stephen Frost <sfrost@snowman.net>
2017-03-10 01:26 ` Michael Paquier <michael.paquier@gmail.com>
2017-03-10 01:47 ` Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-29 03:22 ` Robert Haas <robertmhaas@gmail.com>
2017-03-29 03:30 ` Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:23 ` Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:37 ` Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 04:46 ` Michael Paquier <michael.paquier@gmail.com>
2017-03-31 04:51 ` Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
2017-03-31 19:25 ` Robert Haas <robertmhaas@gmail.com>
2017-03-31 19:31 ` Andres Freund <andres@anarazel.de>
2017-03-31 19:32 ` Robert Haas <robertmhaas@gmail.com>
2017-03-31 19:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-03-31 23:19 ` Michael Paquier <michael.paquier@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox