postgresql表继承与分区_postgres inherits

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

表继承注意事项

查看数据：查询父表可看子表，查询子表仅看子表。如果在父表中只想查询父表的数据,需要添加only关键字。
删除数据：如果删除父表的数据,那么子表和父表的数据都会被删除,如果删除子表的数据,则不会删除父表的数据。
字段继承：一个子表可以从多个父表继承，当同名字段出现在多个父表中（或者父表和子表中），这些字段会被融合（此时字段类型必须相同，否则会抛出一个错误）。
约束继承：父表的检查约束和非空约束会被子表继承。其他约束（如唯一约束，主键，外键）则不会被继承。
索引继承：索引将不会被继承。

使用表继承做范围分区（V10之前）

创建父表，所有的分区都从它继承。这个表中没有数据，不要在这个表上定义任何检查和约束，除非你想约束所有的分区，同样在其上定义任何索引和约束也没有任何意义。

创建几个子表，每个表都是从父表继承，通常这些表不会增加任何字段，我们把子表称为分区，实际上他们就是普通的pg表。

 create table p(id int, info text, crt_time timestamp);	
 create table c1(like p) inherits(p);
 create table c2(like p) inherits(p);
 create table c3(like p) inherits(p);

给分区表增加约束，定义每个分区允许的键值。

 alter table c1 add constraint ck check (crt_time>='2018-04-04' and crt_time<'2018-05-04');
 alter table c2 add constraint ck check (crt_time>='2018-05-04' and crt_time<'2018-06-04');
 alter table c3 add constraint ck check (crt_time>='2018-06-04' and crt_time<'2018-07-04');

对于每个分区，在关键字段上创建索引。
（可选操作，由于索引不继承，因此根据需要选择性创建索引）

创建触发器调用的函数。

 create or replace function ins_tg() returns trigger as $$		
 declare					
 begin					
 if NEW.crt_time>='2018-04-04'and NEW.crt_time<'2018-05-04' then	
 insert into c1(id, info, crt_time) values(NEW.*);			
 elseif NEW.crt_time>='2018-05-04' and NEW.crt_time<'2018-06-04' then	
 insert into c2(id, info, crt_time) values(NEW.*);			
 elseif NEW.crt_time>='2018-06-04' and NEW.crt_time<'2018-07-04' then	
 insert into c3(id, info, crt_time) values(NEW.*);			
 raise exception 'crt_time overflow.';				
 end if;					
 return null;					
 $$language plpgsql strict;

创建触发器。

 create trigger tg1 before insert on p for each row execute procedure ins_tg();

插入测试。

 insert into p(id, info, crt_time) values(1, 'test', '2018-06-23’);
 select * from  c3;               -----有1条数据
 select * from  p;                 -----有1条数据
 select * from only p;         -----查询不到数据
分区表（V10）
 
分区表特性是PG V10新加的一个很重要的特性。
 PG V10的分区特性也是在内置继承表的基础上实现的，所以创建的分区实质上也是普通的表结构。目前PG V10支持范围分区和列表分区。暂不支持哈希分区。 
范围分区—创建主表
 
 创建主表语法：
  CREATE TABLE 表名 ( [{ 列名称 数据_类型} [, ... ] ] )
 PARTITION BY RANGE ( [{ 列名称 } [, ...] ] );
 范围分区的KEY值可由多个字段组成（最多32个字段）。
  例
  postgres=# create table test(n int) partition by range(n);
 CREATE TABLE
范围分区—创建分区
 
 创建分区语法：
  CREATE TABLE 表名 PARTITION OF 主表 FOR VALUES
 FROM{ ( 表达式 [, ...] ) | MINVALUE } [, ...]
 TO { ( 表达式 [, ...] ) | MAXVALUE } [, ...] [ TABLESPACE 表空间名 ];
  例
  postgres=# create table test_1 partition of test for values from (MINVALUE) to (10);
 CREATE TABLE
 postgres=# create table test_2 partition of test for values from (10) to (100);
 CREATE TABLE
 postgres=# create table test_3 partition of test for values from (100) to (1000);
 CREATE TABLE
 postgres=# create table test_4 partition of test for values from (1000) to (10000);
 CREATE TABLE
范围分区—查看分区相关信息
 
 使用元命令\d+ 查看主表相关信息：
 
 
 使用元命令\d+查看分区相关信息：
 
  
范围分区—插入数据测试
 
 插入1w条数据：
  postgres=# insert into test select generate_series(0, 9999);
 INSERT 0 10000
   仅查询主表：
 
 
 带条件查询分区test_1：
  select * from test_1 where n>5;
 
 
 带条件查询分区test_2：
  select * from test_2 where n<15;
 
 
 
 在主表创建索引：
  postgres=# create index test_index on test(n);
 ERROR:  cannot create index on partitioned table “test“      --------无法创建索引
 PG12支持：
  https://www.postgresql.org/docs/12/ddl-partitioning.html
 
  在子表创建索引：
  postgres=# create index test1_index on test_1(n);
 CREATE INDEX
 创建主表语法：
  CREATE TABLE 表名 ( [{ 列名称 数据_类型} [, ... ] ] )
 PARTITION BY LIST( { 列名称 } );   ---列表的KEY只支持一个字段
  创建分区语法：
  CREATE TABLE 表名 PARTITION OF 主表 FOR VALUES
 IN ( 表达式 [, ...] ) [ TABLESPACE 表空间名 ];
列表分区与范围分区
 
范围分区就是根据数据库表中某一字段的值的范围来划分分区。
 列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。 
ATTACH与DETACH操作
 
 ATTACH操作：
 ATTACH操作是把和主表有相同表结构的主表变成该主表的一个分区：
 范围分区：
 ALTER TABLE 主表名 ATTACH PARTITION 表名 FOR VALUES
 FROM{ ( 表达式 [, …] ) | MINVALUE } [, …] TO { ( 表达式 [, …] ) | MAXVALUE } [, …];
 列表分区：
 ALTER TABLE 主表名 ATTACH PARTITION 表名 FOR VALUES IN ( 表达式 [, …] );
 在对普通表进行ATTACH操作时，如果该普通表中有数据，则表中的数据会进行校验是否符合约束条件。
 
 DETACH操作:
 DETACH操作是把主表的分区变成普通表，是ATTACH的反操作：
 语法：
 ALTER TABLE 主表名 DETACH PARTITION 分区名;
  
分区和约束排除
 
约束排除（Constraint exclusive）是一种查询优化技术，在应用在分区特性中，可以提高分区表检索性能，这也是使用分区特性关键的一点。
 排除约束在系统中可以设置开启或关闭，使用参数constraint_exclusion进行设置。
 注：constraint_exclusion的默认值是partition，是介于off和on之间的一个中间值，用来只对继承表和分区表做检查约束，on是对所有的表做检查约束，off是不做检查约束。
 在PG V10中，使用分区表一定不要禁用constraint_exclusion，否则分区将失去其意义。 
分区相关系统表与函数
 
pg_partitioned_table 记录主表信息的系统表：
  
获取系统信息（分区函数）：
 
 pg_get_partkeydef (oid relid) – 根据主表OID返回分区类型及KEY：
  select pg_get_partkeydef('test'::regclass);
  pg_get_partition_constraintdef (oid relid) – 根据分区OID获取分区约束条件：
  select pg_get_partition_constraintdef('test_1'::regclass);
PG V11新特性
 
分区表增加哈希分区
 分区表支持创建主键、外键、索引
 分区表支持UPDATE分区键
 分区表增加 Default Partition
                    目标掌握表继承相关知识。掌握分区（V10）相关知识。数据分区分区将表拆分为多个表，并且通常以一种访问表的应用程序注意不到任何差异的方式完成。PG V10之前的版本：继承表+约束+规则或触发器。PG V10特性：分区表特性，管理分区方便，数据插入效率高。表继承例 创建cities父表，capitals子表继承于cities。postgres=# create table cities(name text,population float,altitude int);&nbsp; 
				最近发现大家越来越关注 PostgreSQL了。2008年以来，通过对PostgreSQL的实际使用，发现其对象-关系数据库机制对现实问题非常有帮助。在多重表继承下，对上亿条不同类别的数据条目进行按型号、按月份双层分区管理，既可在总表查阅所有条目的共有字段，也可在各类型字表查询附加字段，非常高效。下面把这种分区机制介绍如下！
 实验平台：PostgreSQL 9.1
 实验...
				实在是很强大的功能
可以通过Check的制约把结构相同的表合并起来，或者反过来说，可以在一个表名下数据库自动的根据Check条件往对应的分表里存储数据
[USER_DATA表]
CREATE TABLE test."USER_DATA"(  "MAC" character varying(12) NOT NULL,  "ID" character(3) NOT NULL,  "YEARS" ...
				使用INHERITS创建的新表会继承一个或多个父表，子表只会继承父表的表结构和NOT NULL，DEFAULT，CHECK三种约束，主键，外键和唯一键以及索引不会被继承，所以修改父表的结构（增删字段），NOT NULL，DEFAULT和CHECK约束会自动同步子表修改。
create table tbl_inherits_parent(
a int not null,
**seam @Scope---Session, Conversation都什么意思
a:support, ajax support的onsubmit, action, oncomplete分别什么意思
request, seesion, application三个级别什么区别
converstion是结余request和session之间的状态
后台对象在同一个cid, conversion ID下不会创建新的
什么是索引，什
事实上，PG10的分区特性也是在内置继承表的基础上实现的，所以创建的分区实质上也是普通的表结构。
目前PG10支持范围分区和列表分区，哈希分区还不支持。
范围分...