Bagaimana Anda Menggunakan OLD dan NEW PostgreSQL 18 dalam RETURNING?

Masalah dan konteks

PostgreSQL 18 memungkinkan RETURNING secara eksplisit mengekspos nilai baris lama dan baru untuk INSERT, UPDATE, DELETE, dan MERGE. Hasilnya dihasilkan oleh pernyataan pengubah data yang sama, yang dapat menghindari query lanjutan yang berlomba (race) dengan writer lain. Sisi lama atau baru dapat bernilai NULL ketika sisi tersebut tidak ada untuk suatu operasi.

Asumsikan event audit memerlukan nilai tepat yang diubah oleh satu pernyataan, percobaan ulang (retry) harus idempoten, dan aplikasi tidak mampu melakukan pembacaan kedua antara mutasi dan pemancaran audit.

Apa yang dievaluasi oleh pewawancara

Pewawancara mencari atomisitas tingkat pernyataan, semantik operasi yang benar, serta rencana untuk batasan transaksi dan percobaan ulang. Jawaban yang kuat membedakan INSERT, UPDATE, DELETE, ON CONFLICT, dan MERGE, serta menjelaskan di mana pengiriman audit seharusnya berada relatif terhadap commit.

Jawaban biasa melakukan SELECT kembali pada baris setelah penulisan. Jawaban yang kuat menggunakan RETURNING sebagai output mutasi, mempertahankan identitas operasi, dan menghindari publikasi event yang nantinya di-rollback oleh transaksi.

Pertanyaan untuk diklarifikasi terlebih dahulu

Apakah event audit harus dipancarkan hanya setelah commit, atau apakah baris outbox sudah cukup?
Bisakah satu pernyataan mengubah banyak baris, dan bagaimana ID event ditetapkan?
Apa arti “old” untuk konflik upsert dan untuk setiap tindakan MERGE?
Bagaimana percobaan ulang menghindari duplikasi event audit?
Apakah ada kolom yang harus disunting/disamarkan (redacted) sebelum meninggalkan database?

Jika pengiriman downstream harus mengikuti commit, masukkan baris outbox dalam transaksi yang sama dan publikasikan secara asinkron. Jika audit hanya untuk laporan SQL internal, RETURNING dapat dikonsumsi langsung oleh pemanggil.

Jawaban 30 detik

“Saya akan membuat pernyataan mutasi mengembalikan operasi eksplisit, nilai lama, nilai baru, dan kunci event yang stabil. Untuk penulisan banyak baris, saya akan memasukkan hasil tersebut ke dalam outbox dalam transaksi yang sama, lalu mempublikasikannya setelah commit. Saya akan menguji sisi NULL untuk insert dan delete, mendefinisikan semantik upsert dan MERGE, menyamarkan kolom sensitif, dan membuat percobaan ulang menjadi idempoten menggunakan kunci event.”

Desain langkah demi langkah

Definisikan kontrak event. Sertakan identitas tabel, primary key, operasi, proyeksi lama, proyeksi baru, ID transaksi atau request, dan kunci idempotensi.
Gunakan alias eksplisit. Tulis RETURNING WITH (OLD AS old_row, NEW AS new_row) atau sintaksis terdokumentasi yang setara alih-alih mengandalkan RETURNING *.
Tangani semantik operasi. Insert biasanya tidak memiliki baris lama; delete biasanya tidak memiliki baris baru. Upsert dan MERGE memerlukan nilai operasi khusus untuk setiap cabang.
Persistensikan secara atomik. Masukkan record yang dikembalikan ke dalam outbox dalam transaksi yang sama. Pembacaan terpisah atau publikasi eksternal sebelum commit dapat mengamati state yang kemudian di-rollback.
Lindungi data dan percobaan ulang. Samarkan field, lakukan hashing pada nilai sensitif jika sesuai, dan terapkan kunci event yang unik agar pernyataan yang diulang tidak menduplikasi pengiriman.
Verifikasi konkurensi. Jalankan writer konkuren, konflik, rollback, pernyataan multi-baris, dan cabang MERGE parsial. Bandingkan baris audit dengan state tabel yang telah di-commit.

Alternatifnya meliputi trigger untuk penegakan terpusat, logical decoding untuk penangkapan di seluruh database, atau application events untuk semantik domain. RETURNING paling kuat ketika mutasi sudah memegang perubahan tingkat baris yang tepat.

Contoh jawaban

“Pembaruan aplikasi mengembalikan operasi eksplisit, primary key, proyeksi lama, proyeksi baru, dan kunci event. Untuk konflik upsert, saya melabeli hasilnya sebagai update; untuk MERGE, setiap cabang menyediakan operasinya sendiri. Transaksi memasukkan setiap baris yang dikembalikan ke dalam outbox dan melakukan commit sekali. Worker mempublikasikannya setelah commit dengan kunci event unik dan mencoba ulang secara aman. Delete memiliki proyeksi baru yang null, insert memiliki proyeksi lama yang null, dan kolom sensitif dihapus sebelum event meninggalkan database.”

Kesalahan umum

Kesalahan: Meng-query ulang setelah mutasi → Mengapa gagal: writer lain dapat mengubah baris tersebut → Solusi: konsumsi RETURNING dari pernyataan yang sama.
Kesalahan: Mempublikasikan sebelum commit → Mengapa gagal: event audit dapat mendeskripsikan data yang di-rollback → Solusi: gunakan transactional outbox.
Kesalahan: Memperlakukan setiap upsert sebagai insert → Mengapa gagal: pembaruan akibat konflik memerlukan semantik yang berbeda → Solusi: pancarkan operasi eksplisit.
Kesalahan: Mengembalikan semua kolom secara membabi buta → Mengapa gagal: rahasia atau data pribadi bocor → Solusi: gunakan proyeksi allow-list dan penyuntingan data sensitif.

Pertanyaan lanjutan dan tanggapan

Apa yang dimuat oleh OLD untuk sebuah INSERT?

Biasanya tidak ada baris sebelumnya, sehingga sisi lama bernilai NULL. Kontrak event harus memodelkan ketiadaan tersebut daripada mengarang baris default.

Bagaimana Anda menangkap MERGE multi-baris?

Konsumsi satu hasil RETURNING per baris yang terpengaruh, sertakan operasi cabang, dan masukkan setiap event ke dalam transaksi outbox yang sama.

Bagaimana jika operasi insert ke outbox gagal?

Transaksi harus gagal dan me-rollback mutasi. Jangan mengakui (acknowledge) penulisan bisnis sambil membuang catatan auditnya secara diam-diam.

Kapan Anda lebih memilih logical decoding?

Gunakan logical decoding untuk penangkapan perubahan luas di seluruh database atau sistem yang tidak dapat memodifikasi pernyataan. Pilih RETURNING ketika aplikasi memerlukan proyeksi sadar domain dan semantik per-pernyataan yang presisi.