Gunakan ALTER TABLE ... ENABLE/DISABLE CONSTRAINT untuk mengaktifkan atau menonaktifkan kendala. - PolarDB

ENABLE/DISABLE CONSTRAINT di PolarDB for PostgreSQL (Kompatibel dengan Oracle) memberikan cara fleksibel untuk mengelola kendala tabel tanpa menghapus definisinya. Dengan menonaktifkan sementara pemeriksaan kendala, Anda dapat melakukan operasi intensif seperti impor data, perbaikan data, dan penyebaran kendala secara aman dan efisien.

Ikhtisar

Saat menambahkan atau mengelola kendala pada tabel besar, operasi seperti validasi dan pengindeksan sering kali memerlukan pemindaian tabel penuh dan kunci yang berlangsung lama, yang dapat memengaruhi kinerja dan ketersediaan bisnis. ENABLE/DISABLE CONSTRAINT mengoptimalkan kinerja database dengan memodifikasi status aktif dari CHECK, FOREIGN KEY, PRIMARY KEY, dan UNIQUE kendala.

Dua perintah utama tersedia: DISABLE CONSTRAINT dan ENABLE CONSTRAINT:

DISABLE CONSTRAINT: Menonaktifkan kendala. Tidak ada validasi pada penulisan baru (INSERT dan UPDATE).
ENABLE CONSTRAINT: Mengaktifkan kendala dengan dua mode validasi berikut:
- ENABLE CONSTRAINT (Default): Memvalidasi data baru dan memindai semua data yang ada. Untuk tabel besar, proses ini bisa memakan waktu lama dan mengonsumsi sumber daya I/O yang signifikan.
- ENABLE CONSTRAINT ... NOT VALID: Hanya memvalidasi data baru, melewati pemeriksaan pada data yang ada untuk operasi cepat. Gunakan mode ini jika Anda yakin bahwa data yang ada sudah valid.

Prasyarat

Sebelum memulai, pastikan versi kluster Anda adalah PolarDB for PostgreSQL (Kompatibel dengan Oracle) dengan versi revisi 2.0.11.9.11.0 atau lebih baru.

Catatan

Anda dapat memeriksa versi Anda di Konsol atau dengan menjalankan SHOW polardb_version;. Jika diperlukan, tingkatkan versi mesin minor.

Ketergantungan

Ekstensi: Fitur ini bergantung pada ekstensi polar_constraint. Anda harus menginstal dan mengonfigurasinya sesuai instruksi di ALTER TABLE ... ENABLE/DISABLE CONSTRAINT.
Kendala:
- Menonaktifkan kendala yang direferensikan:
  Anda tidak dapat menonaktifkan kendala PRIMARY KEY atau UNIQUE jika sedang direferensikan oleh FOREIGN KEY yang aktif. FOREIGN KEY yang mereferensikan harus dinonaktifkan terlebih dahulu.
- Mengaktifkan foreign key:
  Anda dapat mengaktifkan FOREIGN KEY hanya jika PRIMARY KEY atau UNIQUE kendala yang direferensikan diaktifkan.
Kompatibilitas:
- Ini adalah fitur spesifik PolarDB. PostgreSQL asli tidak mendukung penonaktifan PRIMARY KEY dan UNIQUE kendala.
- Sintaks ini didasarkan pada PostgreSQL dan berbeda dari sintaks Oracle seperti ENABLE VALIDATE dan ENABLE NOVALIDATE.

Instal ekstensi

Sebelum menggunakan fitur ini, Anda harus menginstal ekstensi polar_constraint untuk kluster Anda.

Jalankan perintah berikut untuk memeriksa apakah ekstensi telah diinstal:
```
SELECT extname, extversion FROM pg_extension WHERE extname = 'polar_constraint';
```
Jika ekstensi dan informasi versinya dikembalikan, ekstensi tersebut telah diinstal.
(Opsional) Jika ekstensi belum diinstal, ikuti langkah-langkah berikut:
1. (Opsional) Untuk kluster dengan versi revisi sebelum 2.0.11.9.25.0: Buka halaman Configuration and Management > Parameter Configuration di Konsol PolarDB dan modifikasi parameter kluster shared_preload_libraries dengan menambahkan polar_constraint.
  Catatan
  - Jika parameter tersebut sudah berisi ekstensi lainnya, gunakan koma untuk memisahkannya. Contoh: pg_stat_statements,polar_constraint.
  - Tindakan ini akan memulai ulang kluster Anda dan menyebabkan gangguan layanan. Lakukan operasi ini selama jam-jam sepi atau dalam jendela pemeliharaan.
2. Instal ekstensi di database Anda.
```
-- Instal ekstensiCREATE EXTENSION IF NOT EXISTS polar_constraint;
CREATE EXTENSION IF NOT EXISTS polar_constraint;
```

Sintaks

Tambahkan kendala

Status dinonaktifkan: Saat Anda menambahkan kendala dalam keadaan dinonaktifkan, database memindai semua data yang ada untuk memastikan bahwa data tersebut memenuhi aturan kendala.
```
ALTER TABLE nama_tabel ADD CONSTRAINT nama_kendala DISABLE;
```
Catatan
Jika Anda menambahkan kendala CHECK (nama_kolom > 0) ke tabel dan tabel berisi data yang melanggar kendala ini, operasi gagal dengan kesalahan.
Status diaktifkan
- Saat Anda menambahkan kendala, database memindai semua data yang ada di tabel untuk memastikan bahwa data tersebut mematuhi aturan kendala.
```
ALTER TABLE nama_tabel ADD CONSTRAINT nama_kendala;
```
  Catatan
  Jika Anda menambahkan kendala CHECK (nama_kolom > 0) ke tabel yang berisi data yang melanggar kendala ini, operasi gagal dan kesalahan dikembalikan.
- Saat Anda menambahkan kendala, database hanya memvalidasi data baru dan melewati pemeriksaan pada data yang ada.
```
ALTER TABLE nama_tabel ADD CONSTRAINT nama_kendala NOT VALID;
```
  Catatan
  Jika Anda menambahkan kendala CHECK (nama_kolom > 0) ke tabel dan tabel berisi data yang melanggar kendala ini, operasi berhasil. Anda dapat memindai dan memvalidasi data yang ada nanti selama jam-jam sepi.

Aktifkan batasan

(Default) Saat Anda mengaktifkan kendala, database memvalidasi data baru dan memindai semua data yang ada di tabel untuk memastikan bahwa data tersebut memenuhi aturan kendala.
```
ALTER TABLE nama_tabel ENABLE CONSTRAINT nama_kendala;
```
Saat Anda mengaktifkan kendala, database hanya memvalidasi data baru dan melewati pemeriksaan pada data yang ada.
```
ALTER TABLE nama_tabel ENABLE CONSTRAINT nama_kendala NOT VALID;
```

Nonaktifkan kendala

ALTER TABLE nama_tabel DISABLE CONSTRAINT nama_kendala;

Hapus kendala

ALTER TABLE nama_tabel DROP CONSTRAINT nama_kendala;

Praktik terbaik: Memilih operasi yang tepat

Setiap perintah mengunci tabel database Anda dengan cara yang berbeda, yang memengaruhi cara kerjanya. Untuk menggunakan fitur ini dengan aman, penting untuk memahami perbedaan ini.

Operasi	Tingkat kunci	Dampak baca	Write Impact	Dampak DDL	Pemindaian tabel penuh	Risiko dan rekomendasi
`ADD CONSTRAINT ...` (Menambahkan kendala yang diaktifkan dan segera memvalidasi data yang ada)	`ACCESS EXCLUSIVE`	Tinggi (memblokir semua operasi baca)	Tinggi (memblokir semua operasi tulis)	Tinggi (memblokir operasi DDL lainnya)	Ya	Risiko: Menjalankan ini pada tabel besar mengunci seluruh tabel untuk waktu yang lama, menyebabkan gangguan layanan. Rekomendasi: Jangan gunakan operasi ini langsung pada tabel besar di lingkungan produksi. Gunakan metode dua fase sebagai gantinya.
`ADD CONSTRAINT ... NOT VALID` (Menambahkan kendala yang diaktifkan tetapi tidak memvalidasi data yang ada)	`ACCESS EXCLUSIVE` (Singkat)	Rendah (hanya memblokir secara singkat selama modifikasi metadata)	Rendah (hanya memblokir secara singkat selama modifikasi metadata)	Rendah (hanya memblokir secara singkat)	Tidak	Risiko: Data yang ada mungkin tidak memenuhi kendala. Rekomendasi: Direkomendasikan sebagai langkah pertama untuk menambahkan kendala ke tabel besar. Operasi ini cepat dan hampir tidak berdampak pada bisnis Anda. Pindai dan validasi data yang ada nanti selama jam-jam sepi.
`ADD CONSTRAINT ... DISABLE` (Menambahkan kendala yang dinonaktifkan dan segera memvalidasi data yang ada)	`SHARE UPDATE EXCLUSIVE`	Tidak ada (tidak memblokir `SELECT`)	Tinggi (memblokir `INSERT/UPDATE/DELETE`)	Tinggi (memblokir operasi DDL lainnya)	Ya	Risiko: Overhead operasional tinggi: Operasi ini melakukan pemindaian tabel penuh dan memblokir operasi tulis untuk waktu yang lama. Ini memengaruhi bisnis Anda. Perilaku menyesatkan: Operasi memvalidasi data historis, tetapi kendala tidak diaktifkan. Data baru yang ditulis ke tabel tidak diperiksa. Ini dapat segera menciptakan data kotor. Rekomendasi: Ini adalah operasi yang sangat spesifik. Gunakan untuk melakukan pemeriksaan satu kali untuk melihat apakah data tabel memenuhi aturan, tanpa segera menegakkan aturan tersebut. Karena operasi ini memblokir operasi tulis, jalankan selama jam-jam sepi.
`ENABLE CONSTRAINT` (Mengaktifkan kendala dan memvalidasi data yang ada)	`SHARE UPDATE EXCLUSIVE`	Tidak ada (tidak memblokir `SELECT`)	Tinggi (memblokir `INSERT/UPDATE/DELETE`)	Tinggi (memblokir operasi DDL lainnya)	Ya	Risiko: Operasi gagal jika data yang ada tidak sesuai. Pemindaian tabel penuh mengonsumsi sumber daya dan memblokir tulis. Rekomendasi: Jalankan operasi ini selama jam-jam sepi. Periksa data secara manual sebelum mengaktifkan kendala.
`ENABLE CONSTRAINT ... NOT VALID` (Mengaktifkan kendala tetapi tidak memvalidasi data yang ada)	`ACCESS EXCLUSIVE` (Singkat)	Rendah	Rendah	Rendah	Tidak	Risiko: Inkonsistensi dalam data yang ada diabaikan. Hanya data baru dan dimodifikasi yang divalidasi. Rekomendasi: Cocok untuk skenario di mana Anda ingin segera menegakkan kendala pada data baru tetapi tidak dapat atau tidak perlu membersihkan data historis saat ini. Ini setara dengan `ENABLE NOVALIDATE` di Oracle.
`DISABLE CONSTRAINT` (Menonaktifkan kendala)	`ACCESS EXCLUSIVE` (Singkat)	Rendah	Rendah	Rendah	Tidak	Risiko: Risiko integritas data. Data kotor yang tidak sesuai dapat ditulis saat kendala dinonaktifkan. Rekomendasi: Gunakan hanya untuk skenario sementara yang terkendali, seperti pemuatan data batch. Aktifkan kembali kendala sesegera mungkin setelah operasi.
`DROP CONSTRAINT` (Menghapus kendala)	`ACCESS EXCLUSIVE` (Singkat)	Rendah	Rendah	Rendah	Tidak	Risiko: Ini adalah operasi permanen. Definisi kendala hilang. Menghapus kendala `PRIMARY KEY` atau `UNIQUE` juga menghapus indeksnya. Rekomendasi: Gunakan dengan hati-hati. Pastikan aturan bisnis tidak lagi diperlukan. Untuk menonaktifkan pemeriksaan sementara, gunakan `DISABLE` sebagai gantinya.

Studi kasus: Menambahkan kendala dengan aman ke tabel besar

Menambahkan kendala langsung ke tabel besar menggunakan ADD CONSTRAINT memicu pemindaian tabel penuh segera dan memegang kunci ACCESS EXCLUSIVE. Proses ini memblokir semua operasi baca dan tulis untuk waktu yang lama, yang dapat sangat memengaruhi layanan di lingkungan produksi. Untuk meminimalkan dampak, gunakan metode dua fase berikut:

Contoh Kasus Penggunaan

Tambahkan kendala CHECK ke tabel products dengan ratusan juta baris untuk memastikan bahwa kolom price lebih besar dari 0.

Ikhtisar Prosedur

Tambahkan kendala yang diaktifkan tanpa memvalidasi data yang ada: Tambahkan definisi kendala dengan cepat tanpa memvalidasi data yang ada. Durasi kunci singkat.
Validasi data yang ada di latar belakang: Selama jam-jam sepi, pindai dan validasi data yang ada untuk memastikan bahwa kolom dalam tabel memenuhi kendala. Proses ini memungkinkan pembacaan bersamaan dan memiliki dampak minimal pada layanan Anda.

Prosedur

Tambahkan kendala yang diaktifkan tanpa memvalidasi data yang ada dengan cepat. Untuk melakukan ini, gunakan opsi NOT VALID dalam perintah ALTER TABLE Anda. Pendekatan ini hanya memodifikasi metadata, membuat operasi sangat cepat.

-- Buat tabel uji dan data
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price NUMERIC
);

-- Masukkan baris yang tidak sesuai
INSERT INTO products (name, price) VALUES ('Tas', -1); 

-- Masukkan baris yang sesuai
INSERT INTO products (name, price) VALUES ('Buku', 10); 

-- Tambahkan kendala yang diaktifkan tanpa memvalidasi data yang ada
ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0) NOT VALID;

(Opsional) Verifikasi bahwa kendala diberlakukan pada data baru.

-- Data ilegal baru diblokir
INSERT INTO products (name, price) VALUES ('Pena', -5);
-- Kesalahan:
-- ERROR: baris baru untuk relasi "products" melanggar kendala check "chk_price_positive"
-- DETAIL: Baris gagal berisi (3, Pena, -5).

-- Data ilegal yang diperbarui diblokir
UPDATE products SET price = -2 WHERE id = 1;
-- Kesalahan:
-- ERROR:  baris baru untuk relasi "products" melanggar kendala check "chk_price_positive"
-- DETAIL:  Baris gagal berisi (1, Tas, -2).

Validasi bahwa data historis memenuhi kendala. Gunakan SELECT untuk menemukan data yang tidak sesuai dan UPDATE untuk memperbaikinya.

-- Temukan data historis yang tidak sesuai
SELECT * FROM products WHERE NOT (price > 0);

-- Perbaiki data historis yang tidak sesuai
UPDATE products SET price = 10 WHERE id = 1;

Studi kasus: Optimalkan impor data massal

Saat mengimpor dataset besar dengan perintah seperti COPY, kendala yang diaktifkan dapat menciptakan hambatan kinerja dengan memvalidasi setiap baris. Untuk meningkatkan kecepatan impor secara dramatis, gunakan alur kerja berikut:

Nonaktifkan kendala tabel sebelum impor.
Lakukan impor data massal.
Aktifkan kembali kendala setelah impor selesai.

Contoh Kasus Penggunaan

Impor file CSV besar ke target_table.

Prosedur

Nonaktifkan semua kendala sementara sebelum impor data.

-- Anggap bahwa target_table sudah memiliki kunci unik, kunci asing, dan kendala check
ALTER TABLE target_table DISABLE CONSTRAINT uq_target;
ALTER TABLE target_table DISABLE CONSTRAINT fk_target;
ALTER TABLE target_table DISABLE CONSTRAINT chk_target;

Catatan

Secara umum tidak disarankan atau mungkin untuk menonaktifkan kunci utama. Fokuslah pada menonaktifkan kendala UNIQUE, FOREIGN KEY, dan CHECK.

Lakukan impor data.
Dengan pemeriksaan kendala dinonaktifkan, impor akan jauh lebih cepat.
```
COPY target_table FROM '/path/to/data.csv' WITH CSV;
```

Aktifkan kembali kendala dan validasi semua data.

Pilih salah satu metode berikut berdasarkan kepercayaan Anda terhadap data.

Opsi A: Aktifkan tanpa validasi (Paling cepat)
Gunakan ini jika Anda yakin data yang diimpor bersih atau jika Anda berencana untuk memvalidasinya nanti.
Catatan
ENABLE CONSTRAINT ... NOT VALID melewati validasi data yang baru diimpor dan hanya menegakkan kendala pada perubahan masa depan.
```
-- Aktifkan kendala unik
ALTER TABLE target_table ENABLE CONSTRAINT uq_target NOT VALID;

-- Aktifkan kendala kunci asing
ALTER TABLE target_table ENABLE CONSTRAINT fk_target NOT VALID;

-- Aktifkan kendala CHECK
ALTER TABLE target_table ENABLE CONSTRAINT chk_target NOT VALID;
```

Opsi B: Aktifkan dengan validasi (Lebih lambat, Lebih aman)
Gunakan ini untuk memastikan semua data (termasuk data yang diimpor) valid. Jalankan ini selama jam-jam sepi, karena melakukan pemindaian tabel penuh dan dapat memblokir tulis.

-- Aktifkan kendala unik. Ini memicu validasi semua data dalam tabel.
ALTER TABLE target_table ENABLE CONSTRAINT uq_target;
-- Jika validasi gagal, kesalahan dikembalikan: ERROR: could not enable unique constraint "uq_target"

-- Aktifkan kendala kunci asing. Ini memicu validasi semua data dalam tabel.
ALTER TABLE target_table ENABLE CONSTRAINT fk_target;
-- Jika validasi gagal, kesalahan dikembalikan: ERROR: insert or update on table "target_table" violates foreign key constraint "fk_target"

-- Aktifkan kendala CHECK. Ini memicu validasi semua data dalam tabel.
ALTER TABLE target_table ENABLE CONSTRAINT chk_target;
-- Jika validasi gagal, kesalahan dikembalikan: ERROR: check constraint "chk_target" is violated by some row

FAQ

Saat menjalankan ENABLE CONSTRAINT, muncul kesalahan ERROR: check constraint "..." is violated by some row. Langkah apa yang harus diambil?

Kesalahan ini berarti data yang ada di tabel melanggar kendala. Anda harus menemukan dan memperbaiki data ini sebelum dapat mengaktifkan kendala.

Contoh

Untuk kendala CHECK (price > 0):

Temukan data yang tidak sesuai.

 SELECT * FROM your_table WHERE NOT (price > 0);

Perbaiki atau hapus data tersebut.

UPDATE your_table SET price = ... WHERE ...;

DELETE FROM your_table WHERE NOT (price > 0);

Coba aktifkan kendala lagi.

ALTER TABLE your_table ENABLE CONSTRAINT your_constraint_name;

Ketika saya mengaktifkan kendala unik, kesalahan ERROR: could not enable unique constraint "..." dikembalikan. Mengapa?

Kesalahan ini menunjukkan bahwa ada nilai duplikat di kolom yang digunakan untuk menegakkan keunikan.

Contoh

Temukan nilai duplikat.

SELECT column_list, COUNT(*) FROM your_table GROUP BY column_list HAVING COUNT(*) > 1;

Atasi data duplikat sesuai kebutuhan. Misalnya, hapus baris duplikat dan simpan hanya satu.

DELETE FROM your_table WHERE id NOT IN (SELECT MIN(id) FROM your_table GROUP BY column_list);

Coba aktifkan kendala lagi.

ALTER TABLE your_table ENABLE CONSTRAINT your_unique_name;