从folder系列表迁移数据至access权限表的优化方案咨询

原脚本存在的核心问题 #

1. access_id关联错误：插入access_group时，直接用max_access_id + ROW_NUMBER()计算access_id，该值无法与access表中对应folder的记录匹配——若access表因冲突跳过部分行，行号累加结果会完全偏离实际的access.id。
2. access_group_id映射错误：插入access_group_permission时，错误使用access_group_permission的max_id计算access_group_id，且关联条件ag.id = fgp.folder_group_id不成立（目标access_group.id并非源folder_group.id）。
3. id计算逻辑不可靠：多次在SELECT子查询中调用MAX(id)，批量插入时可能因并发或中间数据变化导致id重复或断层。
4. 缺乏事务保障：整个迁移过程未包裹事务，部分步骤失败会导致数据不一致。

优化思路 #

1. 临时表缓存源数据：将跨库查询的源数据导入本地临时表，减少dblink调用次数，提升效率并简化关联逻辑。
2. 建立映射关系：插入access_group时，通过folder_id关联access表获取正确的access_id；同时记录源folder_group.id与目标access_group.id的映射，用于后续权限表插入。
3. 事务原子性：将整个迁移逻辑包裹在事务中，确保所有步骤要么全部成功，要么全部回滚。
4. 可靠的id生成：提前获取目标表的max_id，统一用行号累加生成新id，避免子查询带来的不确定性；若目标表使用自增序列，可直接依赖序列生成id（更推荐）。

正确实现脚本 #

DO $$
DECLARE
    conn text := 'dbname= host= user= password=';
    max_access_id INT;
    max_access_group_id INT;
    max_access_perm_id INT;
BEGIN
    -- 建立跨库连接
    PERFORM dblink_connect('db_connection', conn);

    -- 创建临时表缓存源数据
    CREATE TEMP TABLE temp_folder (id BIGINT PRIMARY KEY);
    CREATE TEMP TABLE temp_folder_group (id BIGINT PRIMARY KEY, group_id BIGINT, folder_id BIGINT);
    CREATE TEMP TABLE temp_folder_perm (id BIGINT PRIMARY KEY, folder_group_id BIGINT, permission_code VARCHAR(128));

    -- 导入源数据到临时表
    INSERT INTO temp_folder
    SELECT id FROM dblink('db_connection', 'SELECT id FROM folder') AS f(id BIGINT);

    INSERT INTO temp_folder_group
    SELECT id, group_id, folder_id FROM dblink('db_connection', 'SELECT id, group_id, folder_id FROM folder_group') AS fg(id BIGINT, group_id BIGINT, folder_id BIGINT);

    INSERT INTO temp_folder_perm
    SELECT id, folder_group_id, permission_code FROM dblink('db_connection', 'SELECT id, folder_group_id, permission_code FROM folder_group_permission') AS fgp(id BIGINT, folder_group_id BIGINT, permission_code VARCHAR(128));

    -- 获取目标表当前最大id
    SELECT COALESCE(MAX(id), 0) INTO max_access_id FROM access_control.public.access;
    SELECT COALESCE(MAX(id), 0) INTO max_access_group_id FROM access_control.public.access_group;
    SELECT COALESCE(MAX(id), 0) INTO max_access_perm_id FROM access_control.public.access_group_permission;

    -- 开启事务
    BEGIN
        -- 插入access表：关联folder，处理冲突
        INSERT INTO access_control.public.access (id, access_type_id, id_entity)
        SELECT
            max_access_id + ROW_NUMBER() OVER () AS id,
            (SELECT id FROM access_type WHERE type_name = 'folder') AS access_type_id,
            f.id AS id_entity
        FROM temp_folder f
        ON CONFLICT (access_type_id, id_entity) DO NOTHING;

        -- 插入access_group表：关联access表获取正确access_id，同时记录映射
        CREATE TEMP TABLE temp_ag_mapping (src_folder_group_id BIGINT, target_access_group_id BIGINT PRIMARY KEY);

        INSERT INTO access_control.public.access_group (id, group_id, access_id)
        SELECT
            max_access_group_id + ROW_NUMBER() OVER () AS id,
            fg.group_id,
            a.id AS access_id
        FROM temp_folder_group fg
        JOIN access_control.public.access a 
            ON a.access_type_id = (SELECT id FROM access_type WHERE type_name = 'folder')
            AND a.id_entity = fg.folder_id
        RETURNING id, fg.id INTO temp_ag_mapping(target_access_group_id, src_folder_group_id);

        -- 插入access_group_permission表：通过映射获取正确access_group_id
        INSERT INTO access_control.public.access_group_permission (id, access_group_id, permission_code)
        SELECT
            max_access_perm_id + ROW_NUMBER() OVER () AS id,
            ag_map.target_access_group_id,
            fgp.permission_code
        FROM temp_folder_perm fgp
        JOIN temp_ag_mapping ag_map ON ag_map.src_folder_group_id = fgp.folder_group_id
        ON CONFLICT (id) DO NOTHING;

        -- 提交事务
        COMMIT;
    EXCEPTION
        -- 异常回滚
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;
    END;

    -- 清理临时表与连接
    DROP TABLE temp_folder;
    DROP TABLE temp_folder_group;
    DROP TABLE temp_folder_perm;
    DROP TABLE temp_ag_mapping;
    PERFORM dblink_disconnect('db_connection');
END $$;

关键说明 #

• 临时表的作用：将跨库数据本地化，避免多次远程查询，同时方便后续关联操作。
• 映射表的使用：通过temp_ag_mapping记录源folder_group.id和目标access_group.id的对应关系，确保权限表能正确关联到对应的分组。
• 事务处理：所有数据插入操作包裹在事务中，任何步骤失败都会回滚，保证数据一致性。
• 冲突处理：保留ON CONFLICT逻辑，避免重复插入已存在的记录，支持增量迁移。

内容的提问来源于stack exchange，提问作者George