欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

postgresql分区表和创建方式介绍

程序员文章站 2022-03-24 13:44:55
分区表介绍 主表/父表/master table 该表是创建子表的模板。它是一个正常的普通表,但正常情况下它并不储存任何数据。 子表/分区表/child table/partition table ...

分区表介绍

主表/父表/master table 该表是创建子表的模板。它是一个正常的普通表,但正常情况下它并不储存任何数据。

子表/分区表/child table/partition table 这些表继承并属于一个主表。子表中存储所有的数据。主表与分区表属于一对多的关系,也就是说,一个主表包含多个分区表,而一个分区表只从属于一个主表

创建自动分区表采用两种方式

采用视图分区表方式

采用直接分区表方式

创建表

create table if not exists public.sales

(

    id bigserial primary key ,

    store_id varchar(50) ,

    business_date date,

    start_time time,

    end_time time,

    dine_in_tc int,

    delivery_tc int,

    takeout_tc int,

    dine_in_s decimal(20,4),

    delivery_s decimal(20,4),

    takeout_s decimal(20,4),

    voucher_overcharge decimal(20,4),

    freight decimal(20,4),

    currency varchar(16),

    created_at timestamp default now(),

    updated_at timestamp default now()

);

创建索引

“` 

create index sales_store_id on public.sales (store_id); 

create index sales_business_date on public.sales (business_date); 

alter table public.sales add constraint sales_storeid_businessdate_starttime_endtime unique(store_id,business_date,start_time,end_time);

### 1.采用视图分区方式

####  建立视图

create view public.sales_view as select * from public.sales;

#### 定义分表function

create or replace function public.insert_sales() 

returns trigger as 

“.’body′."body′." 

declare 

_start_dt text; 

_end_dt text; 

_table_name text; 

begin 

if new.id is null then 

new.id := nextval(‘sales_id_seq’); // 保证分区后的id可以自增 

end if; 

table_name := ‘sales_view’ || to_char(new.business_date, ‘yyyy_mm’); // 按照时间进行分区

perform 1 

from pg_catalog.pg_class c 

join pg_catalog.pg_namespace n on n.oid = c.relnamespace 

where c.relkind = ‘r’ 

and c.relname = _table_name 

and n.nspname = public; 

if not found then 

_start_dt := to_char(date_trunc(‘month’, new.business_date), ‘yyyy-mm-dd’); 

_end_dt:=_start_dt::timestamp + interval ‘1 month’; 

execute 

‘create table public.’ 

|| quote_ident(_table_name) 

|| ’ (check (business_date >= ’ 

|| quote_literal(_start_dt) 

|| ‘and business_date < ’ 

|| quote_literal(_end_dt) 

|| ‘)) inherits (public.sales)’; 

execute ‘create index ’ || quote_ident(_table_name||’_business_date’||_start_dt) || ’ on public.’ || quote_ident(_table_name) || ’ (business_date)’; 

execute ‘create index ’ || quote_ident(_table_name||’_store_id’||_start_dt) || ’ on public.’ || quote_ident(_table_name) || ’ (store_id)’; 

execute ‘alter table public.’ || quote_ident(_table_name) || ’ add constraint ’ || quote_ident(_table_name||’_storeid_businessdate_starttime_endtime’||_start_dt) || ’ unique (store_id,business_date,start_time,end_time)’; 

execute ‘alter table public.’ || quote_ident(_table_name) || ’ owner to ’ || quote_ident(current_user); 

execute ‘grant all on table public.’ || quote_ident(_table_name) || ’ to ’ || quote_ident(current_user); 

end if; 

execute ‘insert into public.’ || quote_ident(_table_name) || ’ values ($1.) returning ’ using new; 

return new; 

end; 

“.’body′."body′." 

language plpgsql; 

“;

#### 分表触发器

create trigger insert_sales_trigger instead of insert on public.sales_view for each row execute procedure insert_sales();

#### 定义更新function

create or replace function update_sales() 

returns trigger as

begindeletefromsalesviewwhereid=new.id;insertintosalesviewvalues(new.∗);returnnew;end;

begindeletefromsalesviewwhereid=new.id;insertintosalesviewvalues(new.∗);returnnew;end;

language plpgsql; 

“;

#### 更新触发器

create trigger update_sales_trigger instead of update on sales_view for each row execute procedure update_oc_sales();

### 2.直接分区方式

create or replace function insert_sales() 

returns trigger as 

“.’body′."body′." 

declare 

_start_dt text; 

_end_dt text; 

_table_name text; 

begin 

if new.id is null then 

new.id := nextval(‘”.$this->tablename.”_id_seq’); 

end if; 

table_name := ‘sales’ || to_char(new.business_date, ‘yyyy_mm’); 

perform 1 

from pg_catalog.pg_class c 

join pg_catalog.pg_namespace n on n.oid = c.relnamespace 

where c.relkind = ‘r’ 

and c.relname = _table_name 

and n.nspname = ‘public’; 

if not found then 

_start_dt := to_char(date_trunc(‘month’, new.business_date), ‘yyyy-mm-dd’); 

_end_dt:=_start_dt::timestamp + interval ‘1 month’; 

execute 

‘create table if not exists public.’ 

|| quote_ident(_table_name) 

|| ’ (check (business_date >= ’ 

|| quote_literal(_start_dt) 

|| ‘and business_date < ’ 

|| quote_literal(_end_dt) 

|| ‘)) inherits (public.sales)’; 

execute ‘create index if not exists’ || quote_ident(_table_name||’_business_date’||_start_dt) || ’ on public.’ || quote_ident(_table_name) || ’ (business_date)’; 

execute ‘create index if not exists’ || quote_ident(_table_name||’_store_id’||_start_dt) || ’ on public.’ || quote_ident(_table_name) || ’ (store_id)’; 

execute ‘create unique index if not exists’ || quote_ident(_table_name||’_storeid_businessdate_starttime_endtime’||_start_dt) || ’ on public.’ || quote_ident(_table_name) || ’ (store_id,business_date,start_time,end_time)’; 

execute ‘alter table public.’ || quote_ident(_table_name) || ’ owner to ’ || quote_ident(current_user); 

execute ‘grant all on table public.’ || quote_ident(_table_name) || ’ to ’ || quote_ident(current_user); 

end if; 

execute ‘insert into public.’ || quote_ident(_table_name) || ’ values ($1.) on conflict(store_id,business_date,start_time,end_time) do nothing returning ’ using new; 

return null; 

end; 

“.’body′."body′." 

language plpgsql;

#### 分表触发器

create trigger insert_sales_trigger before insert on public.salses for each row execute procedure insert_sales();

“`

两种方式比较

视图分区所有操作都是对视图的操作,直接分区是对主表进行操作;

视图分区触发器使用instead of,直接分区使用before,因为无法直接用触发器替代对主表的操作,只能操作视图;

视图分区用instead of,在function中可以return new,对操作后有明确的返回,直接分区用before方式,在function中采用return null,数据库操作没有返回;

直接分区可以用on conflict对主表insert进行ignore操作,视图分区不能。