KingbaseES例程_普通表在线转分区表（基于触发器）

KingbaseES例程_普通表在线转分区表

概述

普通表转分区表，使用视图的替换式触发器，以路由方式，实现在线转移数据。

数据准备

/*普通大表*/
create table tab_single
as
select id, (random() * 100)::int + 1 c1, md5(id::text) name
from generate_series(1, 1e7) id;

alter table tab_single add primary key (id);

/*表结构一样的分区表*/
create table tab_part
( like tab_single )
partition by list (c1)
;
select format($$ create table tab_part_%s partition of tab_part for values in ( %s ) $$, id, id)
from generate_series(1, 100) id \gexec

/*分区表可以创建查询所需的索引*/
create index idx_tab_part_id on tab_part(id);

/*创建合并视图，作为操作入口*/
create or replace view tab_view as
select *
from tab_single
union all
select *
from tab_part;

/*视图不可更新*/
update tab_view
set name = 'a' 
where id = 1;
[55000] ERROR: cannot update view "tab_view" 详细：Views containing UNION, INTERSECT, or EXCEPT are not automatically updatable. 建议：To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

通过触发器实现视图更新

通用函数

通用的触发器函数：

• 函数的第一个参数是单表名
• 函数的第二个参数是分区表名

create or replace function ftg_tab_view()
    returns trigger
    parallel safe volatile
    language plpgsql
as
$$
declare
    sqlsta  text;
    tabsngl text ;--单表名
    tabpart text; --分区表名
begin
    --函数的第一个参数是单表名
    tabsngl := tg_argv[0];
    --函数的第二个参数是分区表名
    tabpart := tg_argv[1];

    CASE TG_OP
        WHEN 'INSERT'
            THEN sqlsta := format('INSERT INTO %s select $1.*', tabpart);
                 execute sqlsta using new;

                 RETURN new;
        WHEN 'DELETE'
            THEN sqlsta := format('DELETE FROM %s a where row (a.*) = row ($1.*) ', tabsngl);
                 execute sqlsta using old;

                 sqlsta := format('DELETE FROM %s a where row (a.*) = row ($1.*) ', tabpart);
                 execute sqlsta using old;

                 RETURN OLD;
        WHEN 'UPDATE'
            THEN sqlsta := format(
                    'insert into %1$s select $1.* where (select count(*) cnt from %1$s a where row (a.*) = row ($1.*)) = 0',
                    tabpart);
                 execute sqlsta using new;

                 sqlsta := format(' delete from %1$s a where row (a.*) = row ($1.*)', tabsngl);
                 execute sqlsta using old;

                 if old <> new then
                     sqlsta := format('delete from %1$s a where row (a.*) = row ($1.*) and row (a.*) != row ($2.*) ',
                                      tabpart);
                     execute sqlsta using old,new;
                 end if;

                 RETURN NEW;
        END CASE;
end;
$$;

触发器

--单表与分区表，通过函数的参数，实现通用。
CREATE TRIGGER trg_iud_tab_view
    INSTEAD OF INSERT OR UPDATE OR DELETE
    ON tab_view
    FOR EACH ROW
EXECUTE FUNCTION ftg_tab_view('tab_single', 'tab_part');

分步转移数据

do
$$
    declare
        stprow int;
        chgrow int;
        movrow int;
        
        timstm timestamp;
    begin
        stprow := 10000; /*每步迁移10000行数据*/
        chgrow := 0; /*实际影响的行*/
        movrow := 0; /*累计迁移的行*/

        loop
            timstm := clock_timestamp();
            with del as (delete from tab_single
                where ctid = any (array(select ctid from tab_single limit stprow))
                returning *)
            insert into tab_part
            select * from del;
            commit;
            get diagnostics chgrow = row_count;
            exit when chgrow = 0;
            movrow := movrow + chgrow;
            raise info 'moved rows : % / % , % ms',chgrow, movrow,  (date_part('sec',clock_timestamp()-timstm)*1000)::numeric(10,3);
        end loop;

    end;
$$
;

SHELL脚本高并发迁移数据

time seq 0 1000 | xargs -i -n1 -P10 psql -c "update tab_view set id = id where id between 10000*{}+0 and 10000*({}+1)-1"

real    2m14.988s
user    0m0.782s
sys     0m1.223s

抛弃普通表，保留分区表

/*分区表代替视图*/
do
$$
    begin
        drop view tab_view;
        alter table tab_part
            rename to tab_view;
        commit;
    end;
$$
;

或者

/*保留数据入口视图，删除视图的规则，修改视图定义*/
do
$$
    begin
        drop  RULE rul_ins_tab_view TO tab_view;
        drop  RULE rul_del_tab_view TO tab_view;
        drop  RULE rul_upd_tab_view TO tab_view;

        create or replace view tab_view as
        select *
        from tab_part;
        
        commit;
    end;
$$
;

注意

DML操作，主键值可能会出现重复，但可以通过校验语句避免。