PostgreSQL 14 preview - 支持通过import foreign schema 导入指定远端分区表的子分区 - postgres_fdw , import foreign schema 支持 limit to (子分区)
digoal
2021-04-07
PostgreSQL , 分区表 , 外部表 , import foreign schema 导入外部表
2017年支持了import foreign schema 远端分区表, 但是只导入父表. 子分区默认exclude. 原因很简单, 因为分区表的数据通过root 表(父表)可以查询到所有数据. 所以不需要导入分区.
但是实际的情况是, 可能用户只需要访问某个分区呢? 或者通过分区来并行加速呢?
PostgreSQL 14 支持通过import foreign schema 导入指定远端分区表的子分区 - postgres_fdw , import foreign schema 支持 limit to (子分区)
postgres_fdw: Allow partitions specified in LIMIT TO to be imported.
author Fujii Masao <fujii@postgresql.org>
Tue, 6 Apr 2021 17:32:10 +0000 (02:32 +0900)
committer Fujii Masao <fujii@postgresql.org>
Tue, 6 Apr 2021 17:32:10 +0000 (02:32 +0900)
commit a3740c48eb2f91663c7c06c948dfcfb6493d2588
tree 0e40d532067bab79fef9c54f81794fb94c656a9c tree
parent 90c885cdab8bc5a5f12a243774fa0db51002a2fd commit | diff
postgres_fdw: Allow partitions specified in LIMIT TO to be imported.
Commit f49bcd4ef3 disallowed postgres_fdw to import table partitions.
Because all data can be accessed through the partitioned table which
is the root of the partitioning hierarchy, importing only partitioned
table should allow access to all the data without creating extra objects.
This is a reasonable default when importing a whole schema. But there
may be the case where users want to explicitly import one of
a partitioned tables' partitions.
For that use case, this commit allows postgres_fdw to import tables or
foreign tables which are partitions of some other table only when they
are explicitly specified in LIMIT TO clause. It doesn't change
the behavior that any partitions not specified in LIMIT TO are
automatically excluded in IMPORT FOREIGN SCHEMA command.
Author: Matthias van de Meent
Reviewed-by: Bernd Helmle, Amit Langote, Michael Paquier, Fujii Masao
Discussion: https://postgr.es/m/CAEze2Whwg4i=mzApMe+PXxCEfgoZmHGqdqQFW7J4bmj_5p6t1A@mail.gmail.com
PG 14 :
+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') |
+ import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') |
+(2 rows)
OLD:
+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') |
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
