postgresql分割槽表

leo_qin發表於2018-02-04
SQL

建立自動分割槽採用兩種方式

  1. 採用檢視分割槽方式
  2. 採用直接分割槽方式

建立表

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`."$
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`."$
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 $$
BEGIN
    DELETE FROM sales_view WHERE id = NEW.id;
    INSERT INTO sales_view VALUES (NEW.*);
    RETURN NEW;
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`."$
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`."$
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操作，檢視分割槽不能。

相關文章