Temu duga kejuruteraan data: Bagaimanakah kekangan temporal PostgreSQL menghalang tempoh sah bertindih?

Gesaan dan skop

Rekod harga, pajakan, jadual dan kebenaran menggabungkan kekunci perniagaan dengan tempoh sah. Penemu duga mahu pangkalan data menolak tempoh bertindih untuk satu produk atau penyewa, termasuk penulisan serentak. Soalan ini menguji pemodelan temporal, semantik kekangan dan pelan migrasi yang selamat berbanding pertanyaan yang hanya mencari konflik.

Perkara yang dinilai oleh penemu duga

• Sama ada anda membezakan kekunci utama atau unik temporal WITHOUT OVERLAPS daripada kekunci unik B-tree biasa.
• Sama ada anda mentakrifkan titik akhir julat, julat kosong, tingkah laku NULL, tarikh diskret dan cap masa berterusan.
• Sama ada anda memahami bahawa kekunci asing PERIOD memeriksa liputan masa, bukan sekadar kekunci perniagaan yang sepadan.
• Sama ada anda boleh merancang pembersihan sejarah, kesan kunci (lock), pengunduran (rollback) dan pengesahan serentak.
• Sama ada kekangan pangkalan data, mesej aplikasi dan metrik audit mempunyai tanggungjawab yang berasingan.

Semantik dan sempadan

PostgreSQL mentakrifkan kekangan temporal ke atas lajur julat. WITHOUT OVERLAPS boleh digunakan dalam kekangan kekunci utama dan unik; bagi bahagian kekunci biasa yang sama, julat yang berkaitan tidak boleh bertindih. Lajur julat secara tersirat adalah bukan batal (non-null), dan julat kosong atau pelbagai julat tidak membentuk kekunci temporal yang sah. Invarian pangkalan data ini tidak boleh digantikan dengan jujukan aplikasi "semak, kemudian sisip".

PERIOD digunakan untuk kekunci asing temporal. Kekunci perniagaan dan tempoh anak mesti diliputi oleh satu atau lebih baris induk; membuktikan bahawa baris induk dengan kekunci perniagaan yang sama wujud adalah tidak mencukupi. Pemadaman induk atau pemendekan tempoh yang diliputi mesti mematuhi tindakan kekunci asing dan susunan transaksi.

Langkah pemodelan

Pilih selang separa terbuka atau tertutup dan gunakan konvensyen yang sama pada setiap laluan penulisan. Kesahan tarikh biasanya menggunakan [start, end); kesahan cap masa mesti menyatakan zon masa dan kejituan. Letakkan lajur kekunci perniagaan biasa sebelum lajur julat, dengan memilih daterange, tsrange atau tstzrange mengikut kesesuaian.

Cipta kekangan unik temporal pada rekod induk dan kekunci asing PERIOD pada rekod yang diliputi. Aplikasi boleh menyediakan mesej yang mesra pengguna, tetapi pangkalan data yang menentukan sama ada komit berjaya. Sebelum memigrasikan data sejarah, gunakan laporan atau semakan gaya pengecualian untuk mengenal pasti pertindihan, kemudian tentukan sama ada setiap konflik digabungkan, dipecahkan atau ditamatkan.

Contoh SQL

Contoh ini menghalang harga bertindih untuk satu plan_id dan memerlukan peraturan diliputi sepenuhnya oleh pelan harga:

CREATE TABLE price_plan (
  plan_id bigint,
  valid_during daterange NOT NULL,
  amount numeric(12, 2) NOT NULL,
  PRIMARY KEY (plan_id, valid_during WITHOUT OVERLAPS)
);

CREATE TABLE plan_rule (
  plan_id bigint,
  valid_during daterange NOT NULL,
  rule_code text NOT NULL,
  CONSTRAINT plan_rule_plan_period_fk
    FOREIGN KEY (plan_id, PERIOD valid_during)
    REFERENCES price_plan (plan_id, PERIOD valid_during)
);

Sahkan sintaks dan tingkah laku dalam jadual bayang (shadow table) sebelum pengeluaran, dan sahkan bahawa pemacu klien memaparkan konflik pangkalan data dengan cara yang stabil. Contoh ini menunjukkan invarian teras; mata wang, kejituan amaun dan lajur audit masih mengikut keperluan produk.

Penulisan serentak dan migrasi

Sebelum menambah kekangan, kira julat yang berkonflik dan susun mengikut kekunci perniagaan; jangan padam baris sejarah yang "kelihatan pendua" tanpa keputusan perniagaan. Untuk jadual yang besar, anggarkan masa pembinaan indeks, masa menunggu kunci dan kelengahan replikasi. Gunakan pembersihan berkelompok, tetingkap trafik rendah dan penanda kemajuan yang boleh diperhatikan.

Dua sisipan serentak untuk tempoh bertindih mesti diselaraskan oleh pangkalan data semasa komit. Aplikasi harus menukar konflik keunikan kepada ralat perniagaan yang boleh dicuba semula atau diterangkan; prakiraan yang berjaya tidak menjamin sisipan yang seterusnya. Kekalkan lajur lama dan laluan penulisan untuk pengunduran sehingga pengesahan bayang, penyelarasan dwi-tulis dan latihan pemulihan lulus.

Kesilapan lazim

• Hanya mencipta indeks (plan_id, start_at) unik, yang masih membenarkan tempoh bertindih.
• Gagal mentakrifkan peraturan titik akhir dan mencampurkan tarikh yang berakhir pada 2026-02-01 dengan permulaan tempoh seterusnya.
• Memperlakukan kekunci asing PERIOD sebagai kekunci asing biasa yang hanya memeriksa kekunci perniagaan.
• Menambah kekangan secara terus pada jadual pengeluaran yang besar tanpa memeriksa sejarah, kunci atau kelengahan replikasi.
• Membiarkan percubaan semula aplikasi menyembunyikan konflik kekangan dan menghasilkan harga pendua atau liputan separa.

Soalan susulan

Bagaimanakah anda mengendalikan pertindihan sedia ada?

Hasilkan laporan konflik yang dikumpulkan mengikut kekunci perniagaan dan disusun mengikut julat. Minta pemilik perniagaan memilih semantik gabung, pisah atau tamat; baiki baris, mainkan semula penulisan dalam jadual bayang, buktikan bahawa laporan adalah kosong, dan hanya selepas itu tambahkan kekangan.

Mengapa tidak menggunakan kekangan pengecualian atau pencetus?

Kekangan pengecualian boleh menyatakan saling eksklusif selang, tetapi WITHOUT OVERLAPS secara langsung menyatakan semantik kekunci utama atau unik temporal dan bergabung dengan kekunci asing PERIOD. Pencetus boleh terlepas laluan keserentakan, rekursi atau replikasi; gunakannya hanya untuk kesan sampingan rentas jadual tambahan sambil mengekalkan invarian teras dalam kekangan.

Bagaimanakah anda membuktikan migrasi mengekalkan masa perniagaan?

Bandingkan kiraan selang, sampel sempadan, kadar konflik yang ditolak dan pelan pertanyaan sebelum dan selepas. Jalankan pemeriksaan liputan pada replika bacaan dan dalam persekitaran pemulihan. Semasa pelancaran, selaraskan dengan logik lama; jika penulisan yang sah ditolak, undurkan pertukaran kekangan tanpa memadamkan sejarah.