南大通用GBase 8c 數據庫使用存儲過程刷新序列值詳情 - 存儲過程,ide,主鍵,GBase 8c,MySQL,數據庫南大通用GBase 博客

在數據庫管理中，序列刷新是一項重要操作。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 數據庫的序列刷新存儲過程。

南大通用GBase 博客

南大通用GBase 博客

博客 / 詳情