在数据库管理中，序列刷新是一项重要操作。GBase 8c 数据库推出的增强版序列刷新存储过程，为用户带来了更灵活、精确的序列刷新体验。本文将详细介绍该存储过程的使用方法、应用场景及注意事项，助您轻松应对各种复杂的序列刷新需求。
01 功能概述
GBase 8c 数据库的增强版序列刷新存储过程是一项重要的功能升级。它新增了两个关键参数，分别是 target_table 和 increment_value。
target_table 参数允许用户指定要刷新序列的表名，这使得序列刷新能够更有针对性地进行，避免了对无关表序列的操作。而 increment_value 参数则让用户可以自定义序列增加值，默认值为 1。通过这两个参数的结合使用，用户能够根据实际需求灵活调整序列刷新的方式，实现更加精确的序列管理。
这两个新增参数的引入，大大提升了序列刷新的灵活性和精确性。在以往的序列刷新操作中，用户可能只能进行较为宽泛的操作，无法精准地对特定表的序列进行处理。而现在借助 target_table 参数，用户可以直接指定目标表，确保只对相关表的序列进行刷新。同时，increment_value 参数让用户可以根据业务需求，灵活设置序列的增加值，满足不同场景下的序列增长要求。例如，在批量插入数据时，可以预先将序列值增加一定数量，避免插入过程中出现序列冲突。
02 存储过程定义
GBase 8c 数据库提供了两种版本的存储过程，分别是详细版和简化版。
详细版存储过程
refresh_all_sequences_enhanced
功能强大，它接受三个参数，分别是 target_schema、target_table 和 increment_value。该存储过程返回一个表，包含序列名称、表名、列名、旧值、新值和状态等信息。通过这些信息，用户可以详细了解每个序列的刷新情况，便于进行监控和管理。
CREATE OR REPLACE FUNCTION bind_sequences_without_creation( target_schema TEXT DEFAULT NULL, default_column_name TEXT DEFAULT 'id_' ) RETURNS TABLE( table_name TEXT, sequence_name TEXT, column_name TEXT, status TEXT, message TEXT ) AS $$ DECLARE table_rec RECORD; seq_name TEXT; sql_stmt TEXT; col_exists BOOLEAN; seq_exists BOOLEAN; is_bound BOOLEAN; primary_key_col TEXT; BEGIN -- 遍历指定模式下的所有基表（排除系统表和视图） FOR table_rec IN SELECT n.nspname AS schema_name, c.relname AS table_name FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' -- 基表 AND (target_schema IS NULL OR n.nspname = target_schema) -- 模式过滤 AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'information_schema_priv') -- 排除系统模式 LOOP BEGIN -- 生成序列名（表名_seq） seq_name := table_rec.table_name || '_seq'; -- 尝试查找表的主键列 SELECT a.attname INTO primary_key_col FROM pg_index i JOIN pg_class c ON c.oid = i.indrelid JOIN pg_namespace n ON n.oid = c.relnamespace JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(i.indkey) WHERE n.nspname = table_rec.schema_name AND c.relname = table_rec.table_name AND i.indisprimary -- 主键索引 LIMIT 1; -- 如果没有找到主键列，则使用默认列名 IF primary_key_col IS NULL THEN primary_key_col := default_column_name; END IF; -- 检查目标列是否存在 SELECT EXISTS ( SELECT 1 FROM pg_attribute a JOIN pg_class tc ON tc.oid = a.attrelid JOIN pg_namespace tn ON tn.oid = tc.relnamespace WHERE tc.relname = table_rec.table_name AND tn.nspname = table_rec.schema_name AND a.attname = primary_key_col AND a.attnum > 0 ) INTO col_exists; -- 检查序列是否存在 SELECT EXISTS ( SELECT 1 FROM pg_class sc JOIN pg_namespace sn ON sn.oid = sc.relnamespace WHERE sc.relname = seq_name AND sn.nspname = table_rec.schema_name AND sc.relkind = 'S' ) INTO seq_exists; -- 如果目标列不存在，记录并跳过 IF NOT col_exists THEN RETURN QUERY SELECT table_rec.schema_name || '.' || table_rec.table_name, seq_name, primary_key_col, 'SKIPPED'::TEXT, '表中不存在目标列'::TEXT; CONTINUE; END IF; -- 如果序列不存在，记录并跳过（不再自动创建） IF NOT seq_exists THEN RETURN QUERY SELECT table_rec.schema_name || '.' || table_rec.table_name, seq_name, primary_key_col, 'SKIPPED'::TEXT, '序列不存在'::TEXT; CONTINUE; END IF; -- 检查序列是否已经绑定到该列 SELECT EXISTS ( SELECT 1 FROM pg_attrdef ad JOIN pg_class ac ON ac.oid = ad.adrelid JOIN pg_namespace an ON an.oid = ac.relnamespace JOIN pg_attribute aa ON aa.attrelid = ad.adrelid AND aa.attnum = ad.adnum WHERE ac.relname = table_rec.table_name AND an.nspname = table_rec.schema_name AND aa.attname = primary_key_col AND (ad.adbin ILIKE '%' || seq_name || '%' OR ad.adbin ILIKE '%' || table_rec.schema_name || '.' || seq_name || '%') ) INTO is_bound; -- 如果已经绑定，记录并跳过 IF is_bound THEN RETURN QUERY SELECT table_rec.schema_name || '.' || table_rec.table_name, seq_name, primary_key_col, 'SKIPPED'::TEXT, '序列已绑定到目标列'::TEXT; CONTINUE; END IF; -- 绑定序列到目标列（设置默认值） sql_stmt := 'ALTER TABLE ' || quote_ident(table_rec.schema_name) || '.' || quote_ident(table_rec.table_name) || ' ALTER COLUMN ' || quote_ident(primary_key_col) || ' SET DEFAULT nextval(''' || table_rec.schema_name || '.' || seq_name || ''')'; EXECUTE sql_stmt; -- 设置序列所有权 sql_stmt := 'ALTER SEQUENCE ' || quote_ident(table_rec.schema_name) || '.' || quote_ident(seq_name) || ' OWNED BY ' || quote_ident(table_rec.schema_name) || '.' || quote_ident(table_rec.table_name) || '.' || quote_ident(primary_key_col); EXECUTE sql_stmt; -- 返回成功信息 RETURN QUERY SELECT table_rec.schema_name || '.' || table_rec.table_name, seq_name, primary_key_col, 'SUCCESS'::TEXT, '序列已成功绑定到目标列'::TEXT; EXCEPTION WHEN OTHERS THEN -- 记录错误信息并继续处理下一个表 RETURN QUERY SELECT table_rec.schema_name || '.' || table_rec.table_name, seq_name, COALESCE(primary_key_col, default_column_name), 'ERROR'::TEXT, SQLERRM::TEXT; END; END LOOP; RETURN; END; $$ LANGUAGE plpgsql;简化版存储过程
refresh_all_sequences_simple_enhanced
则更加简洁，它同样接受三个参数，但返回值为 VOID。该存储过程主要用于简单地执行序列刷新操作，并输出相应的日志信息。对于只需要进行序列刷新而不需要详细结果信息的用户来说，简化版存储过程是一个不错的选择。这两种存储过程的设计，满足了不同用户的需求，无论是需要详细信息的管理员，还是只关注操作结果的普通用户，都能找到适合自己的存储过程。
CREATE OR REPLACE FUNCTION bind_sequences_without_creation_simple( target_schema TEXT DEFAULT NULL, default_column_name TEXT DEFAULT 'id_' ) RETURNS VOID AS $$ DECLARE table_rec RECORD; seq_name TEXT; sql_stmt TEXT; col_exists BOOLEAN; seq_exists BOOLEAN; is_bound BOOLEAN; primary_key_col TEXT; processed_count INTEGER := 0; bound_count INTEGER := 0; skipped_count INTEGER := 0; error_count INTEGER := 0; BEGIN -- 遍历指定模式下的所有基表（排除系统表和视图） FOR table_rec IN SELECT n.nspname AS schema_name, c.relname AS table_name FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' -- 基表 AND (target_schema IS NULL OR n.nspname = target_schema) -- 模式过滤 AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'information_schema_priv') -- 排除系统模式 LOOP BEGIN processed_count := processed_count + 1; -- 生成序列名（表名_seq） seq_name := table_rec.table_name || '_seq'; -- 尝试查找表的主键列 SELECT a.attname INTO primary_key_col FROM pg_index i JOIN pg_class c ON c.oid = i.indrelid JOIN pg_namespace n ON n.oid = c.relnamespace JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(i.indkey) WHERE n.nspname = table_rec.schema_name AND c.relname = table_rec.table_name AND i.indisprimary -- 主键索引 LIMIT 1; -- 如果没有找到主键列，则使用默认列名 IF primary_key_col IS NULL THEN primary_key_col := default_column_name; END IF; -- 检查目标列是否存在 SELECT EXISTS ( SELECT 1 FROM pg_attribute a JOIN pg_class tc ON tc.oid = a.attrelid JOIN pg_namespace tn ON tn.oid = tc.relnamespace WHERE tc.relname = table_rec.table_name AND tn.nspname = table_rec.schema_name AND a.attname = primary_key_col AND a.attnum > 0 ) INTO col_exists; -- 检查序列是否存在 SELECT EXISTS ( SELECT 1 FROM pg_class sc JOIN pg_namespace sn ON sn.oid = sc.relnamespace WHERE sc.relname = seq_name AND sn.nspname = table_rec.schema_name AND sc.relkind = 'S' ) INTO seq_exists; -- 如果目标列不存在，记录并跳过 IF NOT col_exists THEN skipped_count := skipped_count + 1; RAISE NOTICE '跳过表 %.%: 表中不存在目标列 %', table_rec.schema_name, table_rec.table_name, primary_key_col; CONTINUE; END IF; -- 如果序列不存在，记录并跳过（不再自动创建） IF NOT seq_exists THEN skipped_count := skipped_count + 1; RAISE NOTICE '跳过表 %.%: 序列 %.% 不存在', table_rec.schema_name, table_rec.table_name, table_rec.schema_name, seq_name; CONTINUE; END IF; -- 检查序列是否已经绑定到该列 SELECT EXISTS ( SELECT 1 FROM pg_attrdef ad JOIN pg_class ac ON ac.oid = ad.adrelid JOIN pg_namespace an ON an.oid = ac.relnamespace JOIN pg_attribute aa ON aa.attrelid = ad.adrelid AND aa.attnum = ad.adnum WHERE ac.relname = table_rec.table_name AND an.nspname = table_rec.schema_name AND aa.attname = primary_key_col AND (ad.adbin ILIKE '%' || seq_name || '%' OR ad.adbin ILIKE '%' || table_rec.schema_name || '.' || seq_name || '%') ) INTO is_bound; -- 如果已经绑定，记录并跳过 IF is_bound THEN skipped_count := skipped_count + 1; RAISE NOTICE '跳过表 %.%: 序列已绑定到目标列 %', table_rec.schema_name, table_rec.table_name, primary_key_col; CONTINUE; END IF; -- 绑定序列到目标列（设置默认值） sql_stmt := 'ALTER TABLE ' || quote_ident(table_rec.schema_name) || '.' || quote_ident(table_rec.table_name) || ' ALTER COLUMN ' || quote_ident(primary_key_col) || ' SET DEFAULT nextval(''' || table_rec.schema_name || '.' || seq_name || ''')'; EXECUTE sql_stmt; -- 设置序列所有权 sql_stmt := 'ALTER SEQUENCE ' || quote_ident(table_rec.schema_name) || '.' || quote_ident(seq_name) || ' OWNED BY ' || quote_ident(table_rec.schema_name) || '.' || quote_ident(table_rec.table_name) || '.' || quote_ident(primary_key_col); EXECUTE sql_stmt; bound_count := bound_count + 1; RAISE NOTICE '序列 %.% 已成功绑定到表 %.% 的 % 列', table_rec.schema_name, seq_name, table_rec.schema_name, table_rec.table_name, primary_key_col; EXCEPTION WHEN OTHERS THEN error_count := error_count + 1; RAISE WARNING '处理表 %.% 时发生错误: %', table_rec.schema_name, table_rec.table_name, SQLERRM; END; END LOOP; RAISE NOTICE '处理完成: 总计处理 % 个表，绑定 % 个序列，跳过 % 个表，错误 % 个', processed_count, bound_count, skipped_count, error_count); END; $$ LANGUAGE plpgsql; $$03 参数说明
在使用存储过程时，了解参数的含义和作用至关重要。
target_schema 参数用于指定模式名，当该参数为 NULL 时，表示对所有模式进行操作。这为用户提供了灵活的选择，用户可以根据实际情况，选择对特定模式或所有模式的序列进行刷新。
target_table 参数用于指定表名，同样，当该参数为 NULL 时，表示对所有表进行操作。该参数使用模糊匹配，会匹配包含指定表名的所有表，方便用户对相关表的序列进行统一处理。
increment_value 参数是用于指定序列增加值的，默认值为 1。用户可以根据业务需求，自定义该参数的值。例如，在批量插入大量数据时，可以将该参数设置为较大的值，预先为插入操作预留足够的序列值，避免插入过程中出现序列冲突。在使用这些参数时，需要注意参数的顺序，必须按顺序传递，如果要使用后面的参数，前面的参数不能省略，但可以传 NULL。
04 详细使用示例
示例 1: 刷新所有序列（默认行为）
-- 刷新所有序列，增加值为默认值 1
SELECT * FROM refresh_all_sequences_enhanced();
示例 2: 刷新指定模式下的所有序列
-- 刷新 public 模式下的所有序列
SELECT * FROM refresh_all_sequences_enhanced('public');
示例 3: 刷新指定表相关的序列
--刷新所有模式下 users 表相关的序列
SELECT * FROM refresh_all_sequences_enhanced(NULL, 'users');
示例 4: 刷新指定模式下指定表的序列
-- 刷新 public 模式下 users 表相关的序列 SELECT * FROM refresh_all_sequences_enhanced('public', 'users');
示例 5: 自定义序列增加值
-- 刷新 public 模式下 users 表相关的序列，增加值为 10SELECT * FROM refresh_all_sequences_enhanced('public', 'users', 10);
示例 6: 使用简化版存储过程刷新所有序列
-- 刷新所有序列，仅输出日志
SELECT refresh_all_sequences_simple_enhanced();
示例 7: 使用简化版存储过程刷新指定表的序列
-- 刷新 public 模式下 users 表相关的序列 SELECT refresh_all_sequences_simple_enhanced('public', 'users');
示例 8: 使用简化版存储过程并自定义增加值
-- 刷新 public 模式下 users 表相关的序列，增加值为 5
SELECT refresh_all_sequences_simple_enhanced('public', 'users', 5);
05 实际应用场景
场景 1: 数据迁移后刷新特定表的序列
--假设我们从外部系统导入了 users 表的数据--现在需要刷新 users 表相关的序列
SELECT refresh_all_sequences_simple_enhanced('public', 'users');
场景 2: 为批量插入预留序列值
--如果计划批量插入 100 条记录，可以预先将序列值增加 100--这样可以避免在批量插入过程中序列冲突
SELECT refresh_all_sequences_simple_enhanced('public', 'orders', 100);
场景 3: 监控特定表的序列状态
--检查 users 表相关序列的状态
SELECT sequence_name, table_name, old_value, new_value, statusFROM refresh_all_sequences_enhanced(NULL, 'users')WHERE status != 'SUCCESS';
场景 4: 在数据导入脚本中使用
--完整的数据导入和序列刷新示例
DO $$ BEGIN RAISE NOTICE '开始导入 users 数据...'; -- 执行数据导入（示例） -- COPY users FROM '/path/to/users.csv' WITH CSV HEADER; RAISE NOTICE '数据导入完成，开始刷新序列...'; -- 刷新users表相关序列 PERFORM refresh_all_sequences_simple_enhanced('public', 'users'); RAISE NOTICE '序列刷新完成'; END $$;高级用法-组合使用多个参数
-- 复杂场景：刷新特定模式下特定表的序列，并设置较大的增量值
SELECT refresh_all_sequences_simple_enhanced('sales', 'orders', 1000);
查询失败的序列刷新操作-- 查找刷新失败的序列以便进一步处理
SELECT * FROM refresh_all_sequences_enhanced('public', 'users')WHERE status LIKE 'ERROR%';
高级用法-仅查看将要刷新的序列（不实际执行）
-- 可以先查看将要处理哪些序列，再决定是否执行刷新
SELECT sequence_name, table_name, column_nameFROM refresh_all_sequences_enhanced('public', 'users')WHERE status = 'SUCCESS';
06 注意事项与性能考虑
在使用存储过程时，有一些注意事项需要用户牢记。首先，参数顺序必须严格按照定义传递，如果要使用后面的参数，前面的参数不能省略，但可以传 NULL。其次，target_table 参数使用模糊匹配，会匹配包含指定表名的所有表，用户在使用时需要注意这一点，避免误操作。当不指定参数时，存储过程的行为与原始版本一致，用户可以根据实际情况选择是否使用新增参数。此外，存储过程包含了完善的错误处理机制，即使某个序列刷新失败也不会影响其他序列的处理，但执行这些存储过程需要有足够的权限访问系统表和序列。
在性能方面，也有一些需要考虑的因素。如果数据库中有大量序列，建议指定模式或表名来减少处理时间，避免对不必要的序列进行操作。在高并发环境下执行序列刷新时，建议在维护窗口期间进行，以减少对业务的影响。同时，建议将序列刷新操作包装在事务中，以便在出现问题时可以回滚，保证数据的一致性和完整性。通过注意这些事项和考虑性能因素，用户可以更加高效、安全地使用 GBase 8c 数据库的序列刷新存储过程。
