psql-07表:分区表

表继承与分区表

表继承

• 表继承是PostgreSQL特有的

create table persons (
  age int,
  sex boolean
);

create table students (
  class_no int
) inherits (persons);

• select,update,delete,alter table父表操作时，同时也会操作或影响子表, 反之不会;
• 只查询父表数据

select * from only persons;

• 父表的检查约束和非空约束会被继承，其他的约束则不会;
• 一个子表可以继承多张父表;如果一个字段在多个父表出现会和成一个，同时继承所有父表的这个字段的约束；如果不同父表这个字段类型不同，则会报错;

分区表

• 分区表就是把逻辑上的大表分隔成物理上的小块;PostgreSQL通过继承来实现;
• 一般在表的大小超过了数据库服务器的物理内存大小时使用;
• 在使用继承实现分区表时，一般会让父表为空，数据都存储在字表中;
• 建立分区表的基本步骤,以一个销售表为例子

1)创建父表，没有数据，不定义任何约束与索引；所有分区都从这个表继承

//建立一张销售表
create table sales_detail (
  product_id int not null,    //产品编号
  price  numeric(12, 2),     //单价
  amount int not null,  //数量
  sale_date date not null,  //销售日期
  buyer  varchar(40),   //买家名称
  buyer_contact text  //买家联系
);

2) 继承创建子表，通常这些表不会增加任何字段; 同时增加约束并定义各个分区允许的键值;

//按销售季度创建分区

create table sales_details_y2015q1 (check (
  sale_date >= date '2015-01-01' and sale_date < date '2015-04-01'
)) inherits (sales_details);

create table sales_details_y2015q2 (check (
  sale_date >= date '2015-04-01' and sale_date < date '2015-07-01'
)) inherits (sales_details);

create table sales_details_y2015q3 (check (
  sale_date >= date '2015-07-01' and sale_date < date '2015-10-01'
)) inherits (sales_details);

create table sales_details_y2015q4 (check (
  sale_date >= date '2015-07-01' and sale_date < date '2016-01-01'
)) inherits (sales_details);


3) 对于每个分区，在关键字段创建一个索引，也可以在其他地方；虽然不是必须，但比较有帮助;

create table index sale_detail_y2015q1_sale_date on sales_detail_y2015q1 (sale_date);
create table index sale_detail_y2015q2_sale_date on sales_detail_y2015q2 (sale_date);
create table index sale_detail_y2015q3_sale_date on sales_detail_y2015q3 (sale_date);
create table index sale_detail_y2015q4_sale_date on sales_detail_y2015q4 (sale_date);

4) 定义一个规则或触发器，把对主表的数据插入重定向到合适的分区表

create or replace function sale_detail_insert_trigger()
returns trigger as $$
begin
  if (new.sale_date >= date '2015-01-01' and new.sale_date < date '2015-04-01') then
    insert into sales_detail_y2015q1 values (new.*);
  elseif (new.sale_date >= date '2015-04-01' and new.sale_date < date '2015-07-01') then
    insert into sales_detail_y2015q2 values (new.*);
  elseif (new.sale_date >= date '2015-07-01' and new.sale_date < date '2015-10-01') then
    insert into sales_detail_y2015q3 values (new.*);
  elseif (new.sale_date >= date '2015-10-01' and new.sale_date < date '2016-01-01') then
    insert into sales_detail_y2015q4 values (new.*);
  else
    raise exception 'Date out of range, Fix the sale_detail_insert_tigger() function !';
  end if;
  return null;
end;
$$
language plpgsql;

[create function]
//
create trigger insert_sale_detail_trigger
  before insert on sales_details  //父表
  for each row execute procedure sale_detail_insert_trigger();

[create trigger]


//也可以使用规则替代，但触发器更优;

5) 确保constraint_exclusion(约束排除)里的配置参数postgresql.conf是打开的;打开后,如果查询中where子句的过滤条件与分区的约束条件匹配，这个查询会职能地只查询这个分区

explain select count(*) from sales_details where sale_date < date '2015-04-01';

//这里将条件与各个分区的check比较，就知道只需要扫描主表和第一季度的表;

set constraint_exclusion = 'off'; //修改参数后

explain select count(*) from sales_details where sale_date < date '2015-04-01';  //会扫描每一张表;

触发器

• 由事件自动触发执行的特殊存储过程；可以对进行insert, update, delete等操作;
• 用于加强数据的完整性约束和业务规则的约束;

建立一个触发器

• 建立触发器的步骤；以删除学生表记录同时删除成绩表记录为例

//建表
create table student (
  student_no int primety key,
  student_name varchar(40),
  age int
);

create table score (
  student_no int,
  score int
);

//建立触发器的执行函数
create or replace function student_delete_trigger()
returns trigger as $$
begin
  delete from score where student_no = old.student_no;
  return old;
end;
$$
language plpgsql;

//建立触发器
create trigger delete_student_trigger
  after delete on student
  for each row execute procedure student_delete_trigger();
  
//插入数据后测试
delete from student where student_no = 1;

删除触发器

drop trigger [if exists] name on table [restrict | cascade]

restrict: 默认值,如果有任何依赖对象存在，拒绝删除
cascade: 级联删除依赖此触发器的对象

删除函数

drop function [if exists] name name([[argmode, argname] argtype, [...]])   [restrict | cascade]

• 触发器实际保存在表pg_trigger中,也可以通过这里删除，但不推荐;

触发器函数中的特殊变量

• new: 为insert/update操作触发的行为触发器中存储新的数据行;数据类型是recored;
  * 语句级别触发器和delete操作中此变量没有分配;
• old: 为update/delete操作触发的行为触发器中存储旧的数据行;数据类型是recored;
  • 语句级别触发器和insert操作中此变量没有分配;
• tg_name: 数据类型是name，包含实际触发的触发器名;
• tg_when: 内容为before或after的字符串，用于指定before触发器还是after触发器;
• tg_level: 内容为row或statement,指定触发器级别;
• tg_op: 内容为insert,update,delete,truncate之一,用于指定DML语言的类型;
• tg_relid: 触发器所在表的OID;
• tg_table_name: 触发器所在表的名称;
• tg_table_schema: 触发器所在表的模式;
• tg_nargs: 在create trigger语句里赋予触发器过程的参数个数;
• tg_argv[]: text类型数组,create trigger语句里的参数;