PostgreSQL 10 Beta1分区和分区外部表测试说明

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了PostgreSQL 10 Beta1分区和分区外部表测试说明，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

1.建立主表

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

2.建立子分区表

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.附加已经存在的表作为主表的子分区

1).attach partition（附加表分区）

-- 执行附加分区命令前，要附加分区的表必须已经存在

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

附加分区前，要附加分区的数据必须满足主表分区列的约束条件，如果不满足条件则无法把新的分区附加到主表。

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


2).detach partition（摘除分区表）

--解除分区绑定，解除后\d+命令显示分区表中就不包含已经摘除的分区。

ALTER TABLE part_tab DETACH PARTITION ext_part;


4.准备外部服务器(子表服务器)

$ psql testdb

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

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


testdb=# \dt

List of relations

Schema | Name | Type | Owner

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

public | fpart3 | table | postgres

public | fpart4 | table | postgres

(2 rows)


testdb=#


5.增加扩展

$ 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',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.插入测试数据


-- 外部分区表无法通过主表插入数据,需要通过外部表方式插入

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=#

数据分别在各自服务器插入

主表服务器,通过主表插入本地分区数据

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

INSERT 0 2

testdb=#


外部表服务器，通过外部表分别插入

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

INSERT 0 1

testdb=# insert into part4 values(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=#


外部分区表对违反分区列的插入无约束机制，这样的数据可以任意插入。

testdb=# insert into part4 values(201,'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.添加主键和约束


-- 主表分区列不支持建立主键约束

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=#


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


--主表服务器

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,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,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,name)


testdb=#


-- 子表服务器

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,name);

CREATE INDEX

testdb=# create index idx_fpart4_c1_c2_name on fpart4(c1,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,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,name)


testdb=#


9.查询语句的分区修剪


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=#

猜你在找的Postgre SQL相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1.&#160;标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1.&#160;数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1.&#160;表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4.&#160;从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1.&#160;读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问