PolarDB：ALTER TABLE ... ENABLE/DISABLE CONSTRAINT

功能簡介

ENABLE/DISABLE CONSTRAINT功能通過修改約束的活動狀態來最佳化特定情境下的資料庫效能。該功能支援CHECK、FOREIGN KEY、PRIMARY KEY和UNIQUE四種類型的約束。

• DISABLE CONSTRAINT：將指定約束置為禁用狀態，對於所有約束類型，資料庫將不再對新的資料寫入和更新操作（INSERT、UPDATE）進行校正。

• ENABLE CONSTRAINT：將指定約束置為啟用狀態。此操作包含兩種校正模式：

  • ENABLE CONSTRAINT（預設模式）：啟用約束時，不僅會對後續的新資料進行校正，還會掃描表中的全部存量資料，確保它們都符合約束規則。對於大表而言，此過程可能非常耗時並消耗大量I/O資源。

  • ENABLE CONSTRAINT ... NOT VALID：啟用約束時，僅對後續的新資料進行校正，跳過對存量資料的校正。此模式的核心優勢是速度快，因為它假定表中的現有資料已經滿足約束。

前提條件

您的叢集版本需為Oracle文法相容 1.0，且修訂版本為2.0.11.9.11.0及以上。

使用限制

• 外掛程式依賴：此功能依賴polar_constraint外掛程式，需按指導完成安裝配置。

• 約束依賴限制：

  1. 被參考條件約束的禁用限制：

     如果一個主鍵（PRIMARY KEY）或唯一約束（UNIQUE）被其他表的外鍵約束（FOREIGN KEY）所引用，那麼在禁用或刪除引用它的外鍵約束之前，無法直接禁用此主鍵或唯一約束。系統會阻止此操作以維護資料完整性。

  2. 外鍵約束的啟用限制：

     在啟用一個外鍵約束（FOREIGN KEY）之前，系統會檢查它所引用的、位於另一張表上的主鍵（PRIMARY KEY）或唯一約束（UNIQUE）的狀態。只有當被引用的主鍵或唯一約束處於啟用（ENABLED）狀態時，該外鍵約束才能被成功啟用。如果被引用的約束處于禁用狀態，啟用外鍵約束的操作將會失敗並報錯。

• 相容性：

  • 此功能為PolarDB PostgreSQL版（相容Oracle）擴充功能，原生PostgreSQL不支援禁用主鍵（PRIMARY KEY）和唯一約束（UNIQUE）。

  • 文法風格基於PostgreSQL，與Oracle的ENABLE VALIDATE及ENABLE NOVALIDATE等文法不同。

安裝外掛程式

首次使用該功能前，需要為叢集安裝polar_constraint外掛程式。

1. 請檢查您的PolarDB PostgreSQL版（相容Oracle）叢集是否已預裝polar_constraint外掛程式。如果返回了polar_constraint外掛程式及其版本資訊，則表示您的叢集已成功安裝外掛程式。

   SELECT extname, extversion FROM pg_extension WHERE extname = 'polar_constraint';

2. （可選）若您的叢集尚未安裝，則需要按照以下步驟安裝外掛程式，對於不同核心版本，安裝外掛程式方式不同：

   1. （可選）對於修訂版本在2.0.11.9.25.0以下的叢集：您需先前往PolarDB控制台的配置與管理 > 參數配置頁面，修改叢集參數shared_preload_libraries，添加polar_constraint。

      說明

      • 若參數中已有其他外掛程式，請使用逗號分隔。例如：pg_stat_statements,polar_constraint。

      • 修改當前參數會自動重啟叢集，這將導致商務服務中斷。請在業務低峰期或維護視窗內進行操作。

   2. 為您指定的業務資料庫中安裝外掛程式。

      -- 安裝擴充
      CREATE EXTENSION IF NOT EXISTS polar_constraint;

文法參考

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

操作建議

不同的約束操作會施加不同層級的鎖，對並發事務的影響也不同。理解這些差異是安全使用此功能的關鍵。

為大表安全添加約束

直接為大表添加約束（ADD CONSTRAINT）會立即觸發全表資料校正，並持有ACCESS EXCLUSIVE鎖，長時間阻塞所有讀寫操作，對生產環境業務造成嚴重影響。推薦採用以下兩階段添加法，將影響降至最低。

樣本情境

為一張包含數億行資料的products表添加一個CHECK約束，確保price欄位大於0。

流程概述

1. 添加啟用狀態的約束，但不驗證存量資料：快速添加約束定義，不校正存量資料，鎖時間短。

2. 後台驗證存量資料：在業務低峰期掃描並驗證存量資料，確保表中欄位滿足約束。此過程允許並發讀取，對業務影響最小。

操作步驟

1. 快速添加一個處於啟用狀態的約束定義。使用ADD CONSTRAINT ... NOT VALID文法，此操作僅修改中繼資料，不校正存量資料，操作時間短。

   -- 建立測試表和資料
   CREATE TABLE products (
       id SERIAL PRIMARY KEY,
       name TEXT,
       price NUMERIC
   );
   
   -- 插入一條不合規的資料
   INSERT INTO products (name, price) VALUES ('Bag', -1); 
   
   -- 插入一條合規的資料
   INSERT INTO products (name, price) VALUES ('Book', 10); 
   
   -- 添加開啟狀態的約束，不校正存量資料
   ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0) NOT VALID;

2. （可選）驗證約束對新寫入和更新的資料是否生效。

   -- 新插入的非法資料會被阻止
   INSERT INTO products (name, price) VALUES ('Pen', -5);
   -- 報錯：
   -- ERROR: new row for relation "products" violates check constraint "chk_price_positive"
   -- DETAIL: Failing row contains (3, Pen, -5).
   
   -- 更新的非法資料會被阻止
   UPDATE products SET price = -2 WHERE id = 1;
   -- 報錯：
   -- ERROR:  new row for relation "products" violates check constraint "chk_price_positive"
   -- DETAIL:  Failing row contains (1, Bag, -2).

3. 驗證歷史資料是否滿足約束。使用SELECT查詢不合規的存量資料，並使用UPDATE修複存量資料。

   -- 尋找不合規的歷史資料
   SELECT * FROM products WHERE NOT (price > 0);
   
   -- 修複不合規的歷史資料
   UPDATE products SET price = 10 WHERE id = 1;

最佳化大批量資料匯入效能

在進行大批量資料匯入（如COPY/INSERT）時，啟用的約束（尤其是主鍵、唯一鍵和外鍵）會對每一行資料進行檢查，嚴重拖慢匯入速度。通過在匯入前後禁用和啟用約束，可以大幅提升效能。

樣本情境

將大量資料從CSV檔案匯入到target_table。

操作步驟

1. 在資料匯入前，臨時禁用目標表的所有約束。

   -- 假設target_table已有唯一鍵、外鍵和檢查約束
   ALTER TABLE target_table DISABLE CONSTRAINT uq_target;
   ALTER TABLE target_table DISABLE CONSTRAINT fk_target;
   ALTER TABLE target_table DISABLE CONSTRAINT chk_target;

   說明

   通常不建議也無法直接禁用主鍵，因為主鍵索引對資料群組織至關重要。主要禁用的是唯一約束（UNIQUE）、外鍵（FOREIGN KEY）和檢查（CHECK）約束。

2. 執行資料匯入。由於約束被禁用，資料庫不會進行檢查，匯入速度會顯著加快。

   COPY target_table FROM '/path/to/data.csv' WITH CSV;

3. 在資料匯入後，重新啟用約束並驗證所有資料。

   1. 不驗證匯入資料。

      說明

      ENABLE CONSTRAINT ... NOT VALID存量資料的不一致性被忽略，只有新資料和修改的資料會被校正。建議在啟用前最好先手動檢查資料。

      -- 啟用唯一約束
      ALTER TABLE target_table ENABLE CONSTRAINT uq_target NOT VALID;
      
      -- 啟用外鍵約束
      ALTER TABLE target_table ENABLE CONSTRAINT fk_target NOT VALID;
      
      -- 啟用CKECK約束
      ALTER TABLE target_table ENABLE CONSTRAINT chk_target NOT VALID;

   2. 驗證匯入資料。

      說明

      ENABLE CONSTRAINT會全表掃描消耗資源並阻塞寫入。建議在業務低峰期執行，且啟用前最好先手動檢查資料。

      -- 啟用唯一約束，這將觸發對全表資料的校正
      ALTER TABLE target_table ENABLE CONSTRAINT uq_target;
      -- 如果校正失敗，會報錯：ERROR: could not enable unique constraint "uq_target"
      
      -- 啟用外鍵約束，這將觸發對全表資料的校正
      ALTER TABLE target_table ENABLE CONSTRAINT fk_target;
      -- 如果校正失敗，會報錯：ERROR: insert or update on table "target_table" violates foreign key constraint "fk_target"
      
      -- 啟用CKECK約束，這將觸發對全表資料的校正
      ALTER TABLE target_table ENABLE CONSTRAINT chk_target;
      -- 如果校正失敗，會報錯：ERROR: check constraint "chk_target" is violated by some row

常見問題