创建结果表的存储过程执行时间过长解决方案

结果表不要建太多，不方便维护，所以要尽可能的考虑让多业务通用。

数据库

本文基于 PostgreSQL。

问题

若创建结果表的存储过程执行时间较长，如果先TRUNCATE，再INSERT，在INSERT期间此表将无法使用。

方案

两种方案：大概思路都是向临时表刷入数据，再将临时表更名为业务表。

方案一：先建临时表，刷入数据；删除业务表，将临时表更名为业务表。

方案二：先建业务表和临时表（临时表先删再建），向临时表刷入数据；将业务表更名为old表，将临时表更名为业务表，再将old表更名为临时表并TRUNCATE(以备下次使用)。

临时表和业务表应该使用同一个序列。

方案一具体逻辑

索引创建放在插入数据之后，因为后建索引 Insert 更快。

若想先建立索引，为了避免索引重名可以使用下面方式：

execute 'CREATE INDEX IF NOT EXISTS idx_t_user_' || datetime || ' ON "t_user" ( account)';

步骤：

1、创建序列 t_user_seq 如果不存在。

2、删除 t_user_temp 表如果存在，创建 t_user_temp 表如果不存在，并添加表和字段注释。

3、向 t_user_temp 表插入数据，使用 t_user_seq 序列。

4、删除 t_user 表如果存在，将 t_user_temp 表重命名为 t_user。

5、创建 t_user 表索引。

存储过程：

call update_t_user();

CREATE OR REPLACE PROCEDURE "update_t_user"() AS $BODY$ 

DECLARE
    date varchar(10);
    time varchar(10);
    datetime varchar(20) := to_char(clock_timestamp(), 'yyyymmddhh24miss');
BEGIN
    select to_char(clock_timestamp(), 'yyyy-mm-dd') into date;
    time := to_char(clock_timestamp(), 'hh24:mi:ss');
    raise notice 'date: %', date;
    raise notice 'time: %', time;
    raise notice 'datetime: %', datetime;
    
    raise notice '开始同步数据';

    -- DROP SEQUENCE IF EXISTS t_user_seq; -- DROP的前提是任何表id都没将其用做默认值
    CREATE SEQUENCE IF NOT EXISTS t_user_seq MINVALUE 1 NO MAXVALUE START WITH 1 INCREMENT BY 1 CACHE 10;
    -- ALTER SEQUENCE IF EXISTS t_user_seq OWNED BY NONE;

    DROP TABLE IF EXISTS t_user_temp;
    
    CREATE TABLE IF NOT EXISTS t_user_temp (
        -- "id" bigserial PRIMARY KEY,
        "id" bigint PRIMARY KEY DEFAULT nextval( 't_user_seq' :: regclass ),
        "account" VARCHAR ( 255 ),
        "name" VARCHAR ( 255 ),
        "sex" NUMERIC,
        "age" NUMERIC,
        "birthday" date,	
        "generation_time" TIMESTAMP ( 6 ) 
    );
    COMMENT ON TABLE t_user_temp IS '用户信息表';
    COMMENT ON COLUMN t_user_temp.account IS '账号';
    -- ALTER SEQUENCE t_user_seq OWNED BY t_user_temp.id;


    raise notice '开始同步数据到temp表';
    -- 这里写插入数据sql
    -- 方式一：INSERT INTO t_user_temp SELECT... 需先建表
    -- 方式二：CREATE TABLE t_user_temp AS SELECT...
    INSERT INTO t_user_temp
    SELECT
        nextval( 't_user_seq' ),
        'zhangsan' AS account,
        '张三' AS NAME,
        1 AS sex,
        25 AS age,
        CURRENT_DATE AS birthday,
        clock_timestamp( ) AS generation_time;

    raise notice '删除业务表，temp表重命名为业务表';
    DROP TABLE IF EXISTS t_user;
    ALTER TABLE t_user_temp rename to t_user;

    -- 创建索引
    CREATE INDEX IF NOT EXISTS idx_t_user_0001 ON "t_user" ( account, name);

    raise notice '同步完成';
END
$BODY$ LANGUAGE plpgsql;

方案二具体逻辑

由于有规定，不允许在脚本中执行 DDL 语句，需另外提供表结构变动sql（建表、索引等），可采用此方案。

步骤：

1、清空 t_user_temp 表数据，并插入数据，使用 t_user_seq 序列。

2、将 t_user_temp 表和 t_user 表替换。

建表语句：

DROP TABLE IF EXISTS t_user;
DROP TABLE IF EXISTS t_user_temp;
DROP SEQUENCE IF EXISTS t_user_seq; -- DROP的前提是任何表id都没将其用做默认值

CREATE SEQUENCE IF NOT EXISTS t_user_seq MINVALUE 1 NO MAXVALUE START WITH 1 INCREMENT BY 1 CACHE 10;

CREATE TABLE IF NOT EXISTS t_user (
    -- "id" bigserial PRIMARY KEY,
    "id" bigint PRIMARY KEY DEFAULT nextval( 't_user_seq' :: regclass ),
    "account" VARCHAR ( 255 ),
    "name" VARCHAR ( 255 ),
    "sex" NUMERIC,
    "age" NUMERIC,
    "birthday" date,
    "generation_time" TIMESTAMP ( 6 ) 
);
CREATE INDEX IF NOT EXISTS idx_t_user_0001 ON "t_user" ( account, name);
COMMENT ON TABLE t_user IS '用户信息表';
COMMENT ON COLUMN t_user.account IS '账号';

CREATE TABLE IF NOT EXISTS t_user_temp (
    -- "id" bigserial PRIMARY KEY,
    "id" bigint PRIMARY KEY DEFAULT nextval( 't_user_seq' :: regclass ),
    "account" VARCHAR ( 255 ),
    "name" VARCHAR ( 255 ),
    "sex" NUMERIC,
    "age" NUMERIC,
    "birthday" date,
    "generation_time" TIMESTAMP ( 6 ) 
);
CREATE INDEX IF NOT EXISTS idx_t_user_temp_0001 ON "t_user_temp" ( account, name);
COMMENT ON TABLE t_user IS '用户信息表';
COMMENT ON COLUMN t_user.account IS '账号';

存储过程：

call update_t_user();

CREATE OR REPLACE PROCEDURE "update_t_user"() AS $BODY$ 

BEGIN
    raise notice '开始同步数据到temp表';
    
    TRUNCATE TABLE t_user_temp;
    INSERT INTO t_user_temp
    SELECT
        nextval( 't_user_seq' ),
        'zhangsan' AS account,
        '张三' AS NAME,
        1 AS sex,
        25 AS age,
        CURRENT_DATE AS birthday,
        clock_timestamp( ) AS generation_time;

    raise notice 'temp表和业务表替换';
    
    ALTER TABLE t_user rename to t_user_20220101;
    ALTER TABLE t_user_temp rename to t_user;
    ALTER TABLE t_user_20220101 rename to t_user_temp;

    raise notice '同步完成';
END
$BODY$ LANGUAGE plpgsql;

选择哪一个？

如果对表结构的修改没有严格规定，推荐方案一，因为它可以灵活修改表结构，项目上生产以后会很方便。

如果严格规定不允许脚本含有修改表结构语句，那么只能选方案二。

方案二优化

因为业务需要记录同步日志，所以新增 同步记录表 sync_record。

DROP TABLE IF EXISTS t_sync_record;
DROP SEQUENCE IF EXISTS t_sync_record_seq; -- DROP的前提是任何表id都没将其用做默认值

CREATE SEQUENCE IF NOT EXISTS t_sync_record_seq MINVALUE 1 NO MAXVALUE START WITH 1 INCREMENT BY 1 CACHE 10;

CREATE TABLE t_sync_record (
    -- "id" bigserial PRIMARY KEY,
    "id" int8 NOT NULL DEFAULT nextval( 't_sync_record_seq' :: regclass ),
    "table_name" varchar(255),
    "sync_date" varchar(255),
    "flag" smallint,
    "remark" varchar(255),
    "start_time" timestamp(6) DEFAULT clock_timestamp(),
    "end_time" timestamp(6) DEFAULT clock_timestamp(),
    "records_number" numeric(11,0),
    "last_update_time" timestamp(6),
    CONSTRAINT "pk_sync_record_id" PRIMARY KEY ("id")
);
COMMENT ON TABLE t_sync_record IS '数据同步记录表';
COMMENT ON COLUMN t_sync_record.table_name IS '表名';
COMMENT ON COLUMN t_sync_record.sync_date IS '同步日期。格式：yyyy-mm-dd';
COMMENT ON COLUMN t_sync_record.flag IS '同步标识：1-正在同步，2-同步结束，0-同步失败';
COMMENT ON COLUMN t_sync_record.remark IS '备注';
COMMENT ON COLUMN t_sync_record.start_time IS '任务开始时间';
COMMENT ON COLUMN t_sync_record.end_time IS '任务结束时间';
COMMENT ON COLUMN t_sync_record.records_number IS '表名对应的记录数';
COMMENT ON COLUMN t_sync_record.last_update_time IS '最后更新时间';
ALTER SEQUENCE t_sync_record_seq OWNED BY t_sync_record.id;

存储过程：

call update_t_user();

CREATE OR REPLACE PROCEDURE update_t_user() AS $BODY$
DECLARE
    var_id numeric(10); -- 记录id
    var_table_name varchar(255) := 't_user';
    var_start_time timestamp(6);
    var_records_number numeric; -- 记录数
BEGIN
    set time zone 'PRC';
		var_start_time := clock_timestamp();
		
		
    begin
        raise notice '开始同步数据到temp表';
				
        var_id := nextval('t_sync_record_seq');
        -- 记录表同步信息，插入一条记录
        insert into t_sync_record(id, table_name, sync_date, flag, remark, start_time, last_update_time)
        values (var_id, var_table_name, to_char(var_start_time, 'yyyy-mm-dd'), 1, to_char(var_start_time, 'hh24:mi:ss') || 'start', var_start_time, clock_timestamp());
				
        TRUNCATE TABLE t_user_temp;
        INSERT INTO t_user_temp
        SELECT
            nextval( 't_user_seq' ),
            'zhangsan' AS account,
            '张三' AS NAME,
            1 AS sex,
            25 AS age,
            CURRENT_DATE AS birthday,
            clock_timestamp( ) AS generation_time;

        select count(1) into var_records_number from t_user_temp;
        update t_sync_record set flag = 2, end_time = clock_timestamp(), records_number = var_records_number, remark = remark || '->' || to_char(clock_timestamp(), 'hh24:mi:ss') || 'end', last_update_time = clock_timestamp() where id = var_id;
	
        raise notice 'temp表和业务表替换';
        ALTER TABLE t_user rename to t_user_20220101;
        ALTER TABLE t_user_temp rename to t_user;
        ALTER TABLE t_user_20220101 rename to t_user_temp;

        raise notice '同步完成';
    end;
    -- 如果子块及其父块都没有出现异常，则事务自动提交，否则回滚。
		-- 如果其他子块出现异常没有处理，而是继续抛向父块，此时，由于父块中出现异常，将导致所有子块回滚。


    begin
        raise notice '异常测试！';
        raise exception '抛出一个异常！';
        exception
            when others then 
            raise notice 'error: %',sqlerrm; -- 因为这里并没有继续抛出，所以前面的begin end块不会回滚。
    end;



    EXCEPTION
        when others then 
        raise notice 'error: %',sqlerrm;
				set time zone 'PRC';
        insert into t_sync_record(id, table_name, sync_date, flag, remark, start_time, end_time, records_number, last_update_time)
        values (var_id, var_table_name, to_char(var_start_time, 'yyyy-mm-dd'), 0, 'error: ' || sqlerrm, var_start_time, clock_timestamp(), var_records_number, clock_timestamp());
		
END $BODY$ LANGUAGE plpgsql;

PostgreSQL时区

select to_char(now(), 'yyyy-mm-dd hh24:mi:ss');

-- 查看支持的时区
SELECT * FROM pg_timezone_names t1 WHERE t1.NAME LIKE ANY ( ARRAY [ '%Shanghai%', '%PRC%', '%UTC%' ] );

-- 修改当前终端的时区，临时修改
set time zone 'UTC';
set time zone 'PRC';
set time zone 'Asia/Shanghai';
-- 查看当前终端时区
show time zone;

PostgreSQL时间

PostgreSQL 获取当前日期时间及注意事项

-- 1、当前事务开始的时间。
-- 在同一个事务期间，多次调用相同的函数将会返回相同的值，结果不会随着时间增加。这一点与其他数据库的实现可能不同。
select CURRENT_DATE;
-- 包含时区信息，precision 用于指定小数秒的位数，取值为 0 - 6，默认为 6。
select CURRENT_TIME;
select CURRENT_TIME(6);
select CURRENT_TIMESTAMP;
select CURRENT_TIMESTAMP(6);
-- 不包含时区信息
select LOCALTIME;
select LOCALTIME(6);
select LOCALTIMESTAMP;
select LOCALTIMESTAMP(6);


-- 2、当前语句开始时间。
select transaction_timestamp() from generate_series(1,10); -- 等价于 CURRENT_TIMESTAMP，但是作用更加明确。
select statement_timestamp() from generate_series(1,10); -- 返回当前语句的开始时间，更准确地说，应该是接收到客户端最新命令的时间。
-- transaction_timestamp() 和 statement_timestamp() 对于事务中的第一个命令返回的结果相同，但随后再执行 statement_timestamp() 将会返回不同的值。
select clock_timestamp() from generate_series(1,10); -- 返回当前实际的时间，即使在同一个 SQL 语句中也可能返回不同的值。
select timeofday() from generate_series(1,10); -- 一个历史遗留函数，它与 clock_timestamp() 一样返回当前实际时间，但是返回类型是一个格式化的字符串，而不是 timestamp with time zone。
select now() from generate_series(1,10); -- 与 transaction_timestamp() 等价的一个传统函数，同一个事务中的结果不会改变。