1.建立主表

成都創(chuàng)新互聯(lián)公司長期為上千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為太湖企業(yè)提供專業(yè)的成都網(wǎng)站制作、成都網(wǎng)站建設(shè)、外貿(mào)營銷網(wǎng)站建設(shè)，太湖網(wǎng)站改版等技術(shù)服務(wù)。擁有十載豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。

CREATE TABLE part_tab (c1 int, c2 int,name varchar(20)) PARTITION BY RANGE (c1);

2.建立子分區(qū)表

CREATE TABLE part1 PARTITION OF part_tab FOR VALUES FROM (0) TO (100);

CREATE TABLE part2 PARTITION OF part_tab FOR VALUES FROM (100) TO (200);

3.附加已經(jīng)存在的表作為主表的子分區(qū)

1).attach partition（附加表分區(qū)）

-- 執(zhí)行附加分區(qū)命令前，要附加分區(qū)的表必須已經(jīng)存在

create table ext_part(c1 int not null, c2 int,name varchar(20));

附加分區(qū)前，要附加分區(qū)的數(shù)據(jù)必須滿足主表分區(qū)列的約束條件，如果不滿足條件則無法把新的分區(qū)附加到主表。

ALTER TABLE part_tab ATTACH PARTITION ext_part FOR VALUES FROM (400) to (500);

2).detach partition（摘除分區(qū)表）

--解除分區(qū)綁定，解除后\d+命令顯示分區(qū)表中就不包含已經(jīng)摘除的分區(qū)。

ALTER TABLE part_tab DETACH PARTITION ext_part;

4.準(zhǔn)備外部服務(wù)器(子表服務(wù)器)

$ psql testdb

# create table fpart3 (c1 int not null, c2 int,name varchar(20));

# create table fpart4 (c1 int not null, c2 int,name varchar(20));

testdb=# \dt

         List of relations

 Schema |  Name  | Type  |  Owner   

--------+--------+-------+----------

 public | fpart3 | table | postgres

 public | fpart4 | table | postgres

(2 rows)

testdb=# 

5.增加擴(kuò)展

$ psql testdb

# create extension postgres_fdw;

# create server server_remote_226 foreign data wrapper postgres_fdw options(host '172.16.3.226',port '5432',dbname 'testdb');

# create user mapping for postgres server server_remote_226 options(user 'postgres',password '111111');

testdb=# \des+

                                                                   List of foreign servers

       Name        |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |                     FDW Options         

            | Description 

-------------------+----------+----------------------+-------------------+------+---------+-----------------------------------------

------------+-------------

 server_remote_226 | postgres | postgres_fdw         |                   |      |         | (host '172.16.3.226', port '5432', dbnam

e 'testdb') | 

(1 row)

testdb=# \deu+

                         List of user mappings

      Server       | User name |              FDW Options               

-------------------+-----------+----------------------------------------

 server_remote_226 | postgres  | ("user" 'postgres', password '111111')

(1 row)

6.建立外部表

CREATE FOREIGN TABLE part3 PARTITION OF part_tab FOR VALUES FROM (200) TO (300) SERVER server_remote_226 options (schema_name 'public',table_name 'fpart3');

CREATE FOREIGN TABLE part4 PARTITION OF part_tab FOR VALUES FROM (300) TO (400) SERVER server_remote_226 options (schema_name 'public',table_name 'fpart4');

testdb=# \det+

                                     List of foreign tables

 Schema | Table |      Server       |                 FDW Options                 | Description 

--------+-------+-------------------+---------------------------------------------+-------------

 public | part3 | server_remote_226 | (schema_name 'public', table_name 'fpart3') | 

 public | part4 | server_remote_226 | (schema_name 'public', table_name 'fpart4') | 

(2 rows)

testdb=# 

7.插入測試數(shù)據(jù)

-- 外部分區(qū)表無法通過主表插入數(shù)據(jù),需要通過外部表方式插入

testdb=# insert into part_tab values(1,1,'Chris'),(101,101,'Peter'),(201,201,'William'),(301,301,'Feng');

ERROR:  cannot route inserted tuples to a foreign table

testdb=# 

數(shù)據(jù)分別在各自服務(wù)器插入

主表服務(wù)器,通過主表插入本地分區(qū)數(shù)據(jù)

# insert into part_tab values(1,1,'Chris'),(101,101,'Peter');

INSERT 0 2

testdb=# 

外部表服務(wù)器，通過外部表分別插入

testdb=# insert into part3 values(201,201,'William');

INSERT 0 1

testdb=# insert into part4 values(301,301,'Feng');

INSERT 0 1

testdb=# 

testdb=# 

testdb=# select * from part_tab ;

 c1  | c2  |  name   

-----+-----+---------

   1 |   1 | Chris

 101 | 101 | Peter

 201 | 201 | William

 301 | 301 | Feng

(4 rows)

testdb=# 

外部分區(qū)表對違反分區(qū)列的插入無約束機(jī)制，這樣的數(shù)據(jù)可以任意插入。

testdb=# insert into part4 values(201,301,'Feng');

INSERT 0 1

testdb=# select * from part_tab ;

 c1  | c2  |  name   

-----+-----+---------

   1 |   1 | Chris

 101 | 101 | Peter

 201 | 201 | William

 301 | 301 | Feng

 201 | 301 | Feng

(5 rows)

testdb=# 

8.添加主鍵和約束

-- 主表分區(qū)列不支持建立主鍵約束

testdb=# alter table part_tab add constraint part_tab_c1_pkey primary key(c1);

ERROR:  primary key constraints are not supported on partitioned tables

LINE 1: alter table part_tab add constraint part_tab_c1_pkey primary...

                                 ^

testdb=# 

--- 約束、索引需在子表添加

--主表服務(wù)器

testdb=# alter table part1 add constraint part1_c1_pkey primary key(c1);

ALTER TABLE

testdb=# alter table part2 add constraint part2_c1_pkey primary key(c1);

ALTER TABLE

testdb=# 

testdb=# create index idx_part1_c1_c2_name on part1(c1,c2,name);

CREATE INDEX

testdb=# create index idx_part2_c1_c2_name on part2(c1,c2,name);

CREATE INDEX

testdb=# 

testdb=# \d part1

                      Table "public.part1"

 Column |         Type          | Collation | Nullable | Default 

--------+-----------------------+-----------+----------+---------

 c1     | integer               |           | not null | 

 c2     | integer               |           |          | 

 name   | character varying(20) |           |          | 

Partition of: part_tab FOR VALUES FROM (0) TO (100)

Indexes:

    "part1_c1_pkey" PRIMARY KEY, btree (c1)

    "idx_part1_c1_c2_name" btree (c1, c2, name)

testdb=# 

testdb=# \d part2

                      Table "public.part2"

 Column |         Type          | Collation | Nullable | Default 

--------+-----------------------+-----------+----------+---------

 c1     | integer               |           | not null | 

 c2     | integer               |           |          | 

 name   | character varying(20) |           |          | 

Partition of: part_tab FOR VALUES FROM (100) TO (200)

Indexes:

    "part2_c1_pkey" PRIMARY KEY, btree (c1)

    "idx_part2_c1_c2_name" btree (c1, c2, name)

testdb=#  

-- 子表服務(wù)器

testdb=# alter table fpart3 add constraint fpart3_c1_pkey primary key(c1);

ALTER TABLE

testdb=# alter table fpart4 add constraint fpart4_c1_pkey primary key(c1);

ALTER TABLE

testdb=# create index idx_fpart3_c1_c2_name on fpart3(c1,c2,name);

CREATE INDEX

testdb=# create index idx_fpart4_c1_c2_name on fpart4(c1,c2,name);

CREATE INDEX

testdb=# 

testdb=# \d fpart3

                      Table "public.fpart3"

 Column |         Type          | Collation | Nullable | Default 

--------+-----------------------+-----------+----------+---------

 c1     | integer               |           | not null | 

 c2     | integer               |           |          | 

 name   | character varying(20) |           |          | 

Indexes:

    "fpart3_c1_pkey" PRIMARY KEY, btree (c1)

    "idx_fpart3_c1_c2_name" btree (c1, c2, name)

testdb=# 

testdb=# \d fpart4

                      Table "public.fpart4"

 Column |         Type          | Collation | Nullable | Default 

--------+-----------------------+-----------+----------+---------

 c1     | integer               |           | not null | 

 c2     | integer               |           |          | 

 name   | character varying(20) |           |          | 

Indexes:

    "fpart4_c1_pkey" PRIMARY KEY, btree (c1)

    "idx_fpart4_c1_c2_name" btree (c1, c2, name)

testdb=# 

9.查詢語句的分區(qū)修剪

testdb=# explain analyze select * from part_tab where c1=1;

                                              QUERY PLAN                                              

------------------------------------------------------------------------------------------------------

 Append  (cost=0.00..1.01 rows=1 width=66) (actual time=0.009..0.009 rows=1 loops=1)

   ->  Seq Scan on part1  (cost=0.00..1.01 rows=1 width=66) (actual time=0.008..0.009 rows=1 loops=1)

         Filter: (c1 = 1)

 Planning time: 0.234 ms

 Execution time: 0.027 ms

(5 rows)

testdb=# explain analyze select * from part_tab where c1=101;

                                              QUERY PLAN                                              

------------------------------------------------------------------------------------------------------

 Append  (cost=0.00..1.01 rows=1 width=66) (actual time=0.025..0.028 rows=1 loops=1)

   ->  Seq Scan on part2  (cost=0.00..1.01 rows=1 width=66) (actual time=0.024..0.026 rows=1 loops=1)

         Filter: (c1 = 101)

 Planning time: 0.271 ms

 Execution time: 0.066 ms

(5 rows)

testdb=# explain analyze select * from part_tab where c1=201;

                                                  QUERY PLAN                                                  

--------------------------------------------------------------------------------------------------------------

 Append  (cost=100.00..121.47 rows=5 width=66) (actual time=2.179..2.180 rows=1 loops=1)

   ->  Foreign Scan on part3  (cost=100.00..121.47 rows=5 width=66) (actual time=2.178..2.178 rows=1 loops=1)

 Planning time: 0.308 ms

 Execution time: 3.551 ms

(4 rows)

testdb=# explain analyze select * from part_tab where c1=301;

                                                  QUERY PLAN                                                  

--------------------------------------------------------------------------------------------------------------

 Append  (cost=100.00..121.47 rows=5 width=66) (actual time=1.218..1.219 rows=1 loops=1)

   ->  Foreign Scan on part4  (cost=100.00..121.47 rows=5 width=66) (actual time=1.217..1.218 rows=1 loops=1)

 Planning time: 0.312 ms

 Execution time: 2.178 ms

(4 rows)

testdb=# explain analyze select * from part_tab where c1<201;

                                                   QUERY PLAN                                                   

----------------------------------------------------------------------------------------------------------------

 Append  (cost=0.00..129.46 rows=305 width=66) (actual time=0.014..2.881 rows=2 loops=1)

   ->  Seq Scan on part1  (cost=0.00..1.01 rows=1 width=66) (actual time=0.014..0.015 rows=1 loops=1)

         Filter: (c1 < 201)

   ->  Seq Scan on part2  (cost=0.00..1.01 rows=1 width=66) (actual time=0.009..0.009 rows=1 loops=1)

         Filter: (c1 < 201)

   ->  Foreign Scan on part3  (cost=100.00..127.44 rows=303 width=66) (actual time=2.855..2.855 rows=0 loops=1)

 Planning time: 0.234 ms

 Execution time: 3.884 ms

(8 rows)

testdb=# 

當(dāng)前題目：PostgreSQL 10 Beta 1分區(qū)和分區(qū)外部表測試說明
文章路徑：http://chinadenli.net/article16/pigsgg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)頁設(shè)計(jì)公司、全網(wǎng)營銷推廣、微信公眾號、品牌網(wǎng)站設(shè)計(jì)、網(wǎng)站排名、網(wǎng)站導(dǎo)航

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)