南大通用GBase 8c 数据库使用存储过程刷新序列值

用户627994718262
20 阅读5分钟

原文链接：www.gbase.cn/community/p…
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。

在数据库管理中，序列刷新是一项重要操作。GBase 8c 数据库推出的增强版序列刷新存储过程，为用户带来了更灵活、精确的序列刷新体验。本文将详细介绍该存储过程的使用方法、应用场景及注意事项，助您轻松应对各种复杂的序列刷新需求。

01 功能概述

GBase 8c 数据库的增强版序列刷新存储过程是一项重要的功能升级。它新增了两个关键参数，分别是 target_table 和 increment_value。

target_table 参数允许用户指定要刷新序列的表名，这使得序列刷新能够更有针对性地进行，避免了对无关表序列的操作。而 increment_value 参数则让用户可以自定义序列增加值，默认值为 1。通过这两个参数的结合使用，用户能够根据实际需求灵活调整序列刷新的方式，实现更加精确的序列管理。

这两个新增参数的引入，大大提升了序列刷新的灵活性和精确性。在以往的序列刷新操作中，用户可能只能进行较为宽泛的操作，无法精准地对特定表的序列进行处理。而现在借助 target_table 参数，用户可以直接指定目标表，确保只对相关表的序列进行刷新。同时，increment_value 参数让用户可以根据业务需求，灵活设置序列的增加值，满足不同场景下的序列增长要求。例如，在批量插入数据时，可以预先将序列值增加一定数量，避免插入过程中出现序列冲突。

0​2 存储过程定义

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 数据库的序列刷新存储过程。

原文链接：www.gbase.cn/community/p…
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。

avatar
文章
阅读
粉丝