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语句里的参数;