Temuduga Kejuruteraan Data: Mengapa PostgreSQL 18 MERGE Boleh Gagal pada Baris Sumber Pendua?

Soalan dan senario

Jadual snapshot pelanggan harian, staging_customer, digabungkan ke dalam customer_dim. Dalam satu kelompok (batch), customer_id yang sama muncul dua kali: sekali daripada CRM dan sekali daripada pembetulan manual. Sasaran tidak mempunyai kunci pendua, namun kenyataan tersebut menimbulkan pelanggaran kardinaliti (cardinality violation). Terangkan bagaimana PostgreSQL 18 membentuk baris perubahan calon, mengapa satu baris sasaran tidak boleh diubah suai lagi oleh baris sumber lain, dan bagaimana RETURNING merge_action() boleh menghasilkan keputusan yang boleh diaudit.

Perkara yang diuji oleh penemuduga

• Membezakan baris sumber pendua, kunci sasaran pendua, dan syarat ON yang terlalu luas.
• Menerangkan bahawa MERGE hanya melaksanakan cabang WHEN pertama yang sepadan bagi setiap baris perubahan calon.
• Membuat pilihan yang wajar antara menyahduplikasi, menolak kelompok, dan mengekalkan pembetulan terkini.
• Menggunakan RETURNING untuk bukti audit peringkat baris tanpa menganggapnya sebagai log transaksi.
• Mengendalikan konkurensi, larian semula, keistimewaan (privileges), dan amaran kualiti data.

Soalan penjelasan sebelum menjawab

• Adakah customer_id merupakan kunci perniagaan, atau adakah penyewa (tenant) dan tarikh berkuat kuasa perlu membentuk kunci komposit?
• Adakah kedua-dua rekod sumber mempunyai versi, masa peristiwa (event time), atau keutamaan pembetulan yang boleh dipercayai? Jawapan itu menentukan penyahduplikasian.
• Patutkah kelompok yang tidak sah ditolak secara atomik, atau bolehkah baris yang telah diselesaikan ditulis terlebih dahulu? Ini mengubah reka bentuk transaksi dan main semula (replay).
• Adakah audit memerlukan nilai lama, nilai baharu, medan sumber, dan tindakan, atau hanya bilangan baris?
• Bolehkah rekod sumber tiba lewat merentasi kelompok, dan adakah sasaran membenarkan pemadaman lembut (soft deletion)?

Rangka jawapan 30 saat

"Saya terlebih dahulu membuktikan bahawa syarat ON memetakan setiap kunci sasaran kepada paling banyak satu baris sumber. PostgreSQL MERGE membina baris perubahan calon dan kemudian melaksanakan satu tindakan bagi setiap baris mengikut susunan WHEN; pelbagai baris sumber yang mencapai satu baris sasaran akan menyebabkan ralat kardinaliti dan transaksi gagal. Saya akan menyahduplikasi secara deterministik mengikut versi atau masa peristiwa, dan menolak kelompok apabila konflik tidak dapat diselesaikan. RETURNING merge_action() merekodkan sisipan, kemas kini, dan pemadaman, manakala jadual kawalan kelompok, kekangan unik, dan kunci kedap idempoten (idempotency key) menjadikan larian semula selamat."

Jawapan mendalam langkah demi langkah

Buktikan kardinaliti pemadanan terlebih dahulu

Jalankan pertanyaan kualiti data dengan kunci yang sama persis digunakan oleh ON dan cari kunci sasaran yang mempunyai berbilang baris sumber. Jangan sekadar menggunakan DISTINCT pada sumber: dua baris dengan kunci yang sama masih boleh menerangkan fakta yang bercanggah. Jika kunci perniagaan ialah (tenant_id, customer_id), gunakan kedua-dua lajur dalam MERGE dan dalam pertanyaan kualiti.

Jadikan penyahduplikasian deterministik

Utamakan nombor versi. Tanpa nombor versi, gunakan masa peristiwa, keutamaan sumber yang dipercayai, dan pemecah seri (tie-breaker) yang stabil. Fungsi tetingkap boleh memilih satu pemenang:

WITH ranked AS (
  SELECT s.*, row_number() OVER (
    PARTITION BY tenant_id, customer_id
    ORDER BY version DESC, event_at DESC, source_priority DESC, ingest_id DESC
  ) AS rn
  FROM staging_customer AS s
)
SELECT * FROM ranked WHERE rn = 1;

Jika tiada sumber yang boleh dibuktikan lebih baharu, tulis konflik tersebut ke jadual kuarantin dan tolak kelompok itu. Jangan sekali-kali bergantung pada pangkalan data untuk memilih baris secara sewenang-wenangnya.

Reka bentuk cabang WHEN dan output audit

Syarat WHEN dinilai mengikut susunan bertulis, dan cabang benar yang pertama akan dijalankan. Letakkan syarat perlindungan terlebih dahulu, seperti mengemas kini hanya apabila versi masuk lebih tinggi, kemudian kendalikan sisipan NOT MATCHED dan sebarang pembersihan NOT MATCHED BY SOURCE yang disengajakan. PostgreSQL 18 RETURNING boleh mendedahkan lajur sumber, nilai sasaran lama dan baharu, serta merge_action(), tetapi ia melaporkan baris yang diubah oleh kenyataan ini; ia tidak menggantikan jadual kawalan kelompok.

Kawal transaksi dan konkurensi

Kekalkan MERGE kelompok, sisipan audit, dan kemas kini status kelompok dalam satu transaksi. Berikan kunci kedap idempoten pada setiap kelompok input dan kesan kelompok yang berjaya sebelum main semula. Patuhi peraturan pengasingan pangkalan data untuk larian serentak, dan jamin paling banyak satu baris sumber calon bagi setiap baris sasaran sebelum pelaksanaan berbanding menemui pendua pada masa larian.

Contoh jawapan berkualiti tinggi

"Ralat ini tidak dijelaskan oleh kunci sasaran pendua semata-mata; fakta pentingnya ialah syarat ON menghubungkan satu baris sasaran kepada berbilang baris sumber. Saya akan memeriksa kardinaliti sumber menggunakan kunci penyewa dan pelanggan yang sama, kemudian menyusun mengikut versi, masa peristiwa, keutamaan sumber, dan ID penyerapan (ingest ID). Konflik yang tidak dapat diselesaikan akan dihantar ke kuarantin dan menggagalkan kelompok. Disebabkan MERGE melaksanakan cabang WHEN pertama yang sepadan, saya meletakkan kawalan versi lebih tinggi di hadapan. PostgreSQL 18 RETURNING merge_action() merekodkan sisipan, kemas kini, atau pemadaman beserta nilai lama dan baharu; baris audit dan kawalan kelompok kekal dalam transaksi yang sama supaya larian semula, amaran, dan main semula mempunyai bukti."

Kesilapan lazim

• Kesilapan: Menggunakan DISTINCT pada sumber → Mengapa ia gagal: Fakta berbeza boleh digabungkan secara salah → Pembetulan: Tentukan pemenang dengan versi dan keutamaan perniagaan, serta kuarantinkan konflik.
• Kesilapan: Menganggap MERGE memilih baris sumber secara rawak → Mengapa ia gagal: Berbilang pengubahsuaian pada satu baris sasaran menimbulkan ralat kardinaliti → Pembetulan: Buktikan pemadanan satu-ke-satu sebelum pelaksanaan.
• Kesilapan: Menganggap kiraan RETURNING sebagai kejayaan kelompok → Mengapa ia gagal: Keadaan sifar perubahan, kegagalan, dan main semula tidak direkodkan → Pembetulan: Gunakan jadual kawalan kelompok dan keadaan transaksi yang berasingan.
• Kesilapan: Hanya menguji satu bebenang (thread) → Mengapa ia gagal: Pengasingan, data lewat, dan main semula tidak disahkan → Pembetulan: Uji konkurensi, larian semula, dan kelompok lewat.

Soalan susulan dan respons

Bagaimana jika satu baris sumber pendua ialah penanda padam (delete marker)?

Letakkan pemadaman dan kemas kini ke dalam susunan versi yang sama dan benarkan hanya versi terkini menang. Jika pemadaman tidak mempunyai versi yang boleh dibandingkan, kuarantinkan konflik tersebut daripada membiarkan satu kelompok secara senyap menentukan keadaan pelanggan.

Bolehkah RETURNING merekodkan baris sumber yang tidak sepadan dengan apa-apa?

Ia mengembalikan baris yang terjejas oleh INSERT, UPDATE, atau DELETE; ia bukan laporan kualiti tidak sepadan di bahagian sumber. Jalankan statistik anti-join secara berasingan, atau simpan calon dan tindakan yang dimaksudkan dalam jadual pra-audit sebelum melaksanakan MERGE.

Mengapa tidak menggunakan INSERT ... ON CONFLICT secara terus?

Untuk sisip-atau-kemas-kini kunci unik yang mudah, ON CONFLICT mungkin lebih ringkas. Pilih MERGE apabila pemadanan sumber, pembersihan sumber yang tiada, atau berbilang cabang bersyarat diperlukan. Jangan anggap semantik konkurensi dan keistimewaannya boleh ditukar ganti.

Apakah yang berubah apabila sasaran mempunyai pencetus (triggers)?

Sahkan bahawa pencetus tidak mengubah kunci padanan atau menjadikan baris sasaran yang sama sebagai calon baharu dalam kenyataan tersebut. Bezakan tindakan MERGE daripada kesan sampingan pencetus dalam data audit, dan uji undur balik (rollback) dalam persekitaran integrasi.

Rujukan

• PostgreSQL Documentation 18: MERGE
• PostgreSQL Documentation 18: Merge Support Functions
• Greg Low: SQL Interview: 35 T-SQL Merge Statement Clauses
• Simplyblock: PostgreSQL MERGE tutorial

Petua menjawab

Buktikan kardinaliti satu-ke-satu ON terlebih dahulu, terangkan susunan WHEN dan pelanggaran kardinaliti, kemudian berikan penyahduplikasian deterministik, kawalan transaksi, audit RETURNING merge_action(), dan pengendalian main semula.

Pengajaran satu ayat

Jawapan MERGE yang kukuh menghubungkan kardinaliti sumber, susunan cabang, dan output audit ke dalam satu kontrak data yang boleh dimainkan semula.