创建自动分区采用两种方式
- 采用视图分区方式
- 采用直接分区方式
创建表
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
操作,视图分区不能。
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。