GaussDB分布式版为源任务序列值同步

操作步骤

1. 使用DRS同步任务测试连接时的用户登录GaussDB分布式版实例。
2. 创建用于生成与表关联序列的 setval 语句的函数。

   create or replace function gen_sequence_setval_sql(p_safety_offset bigint)
       returns text
       language plpgsql
   as
   $fun$
   declare
       sequence_name_query_sql text := 'select quote_ident(n.nspname) || ''.'' || quote_ident(c.relname) as tablename, quote_ident(ns.nspname) || ''.'' || quote_ident(s.relname) as seqname from pg_depend d join pg_class c on c.oid = d.refobjid join pg_namespace n on n.oid = c.relnamespace join pg_class s on s.oid = d.objid join pg_namespace ns on ns.oid = s.relnamespace where d.refclassid = ''pg_class''::regclass and s.relkind in (''S'', ''L'') union select quote_ident(n.nspname) || ''.'' || quote_ident(c.relname) as tablename, quote_ident(ns.nspname) || ''.'' || quote_ident(s.relname) as seqname from pg_attrdef a join pg_depend d on d.objid = a.oid join pg_class c on c.oid = a.adrelid join pg_namespace n on n.oid = c.relnamespace join pg_class s on s.oid = d.refobjid join pg_namespace ns on ns.oid = s.relnamespace where d.classid = ''pg_attrdef''::regclass and s.relkind in (''S'', ''L'') order by tablename, seqname;';
       sequence_query_sql text;
       seq_rec record;
       rec record;
       target_value bigint := null;
       v_dir int := null;
       result_sql text := '';
   begin
       for seq_rec in execute sequence_name_query_sql loop
           sequence_query_sql := format('execute direct on all $$select is_called, last_value, increment_by, cache_value, (last_value + increment_by * cache_value) as next_new_val from %s$$;', seq_rec.seqname); target_value = null;
           for rec in execute sequence_query_sql loop
               v_dir := sign(rec.increment_by);
               if rec.is_called and (target_value is null or v_dir * rec.next_new_val > v_dir * target_value) then
                   target_value := rec.next_new_val;
               end if;
           end loop;
           if target_value is not null then
               target_value := target_value + v_dir * p_safety_offset;
               result_sql := result_sql || format('select setval(%L, %s);%s', seq_rec.seqname, target_value, E'\n');
           end if;
       end loop;
       return result_sql;
   end;
   $fun$;

3. 调用上述函数，传入安全偏移量（例如10000，表示在源序列当前值基础上预留的安全阈值），生成用于同步与表关联序列值的 setval 语句。

   select gen_sequence_setval_sql(10000);

4. 在目标库执行3中生成的 setval 语句，同步与表关联的序列值。

   -- 示例（实际内容以函数返回为准）
   select setval('public.panda_id_seq', 666);
   select setval('public.ptd_id_seq', 888);
   --

5. 检查目标库的序列值。

   针对每个同步的表关联的序列，使用如下语句获取目标库序列的新值。

   select nextval('<seqname>');

   检查得到的结果是否大于等于（自增序列，increment_by > 0）或小于等于（自减序列，increment_by < 0）源库所有节点的序列值。

6. 清理临时函数。

   drop function gen_sequence_setval_sql;