Penanganan kesalahan default yang berbeda di Oracle dan PostgreSQL
Saya membandingkan perilaku default Oracle dan PostgreSQL setelah mengalami kesalahan dalam kode PL / SQL (PL / pgSQL). Untuk tujuan ini, saya menulis kode Oracle dan PostgreSQL analog yang ditunjukkan di bawah ini.
Kode Oracle ( db <> fiddle ):
CREATE TABLE table1 (col1 int);
CREATE PROCEDURE raise_error AS
BEGIN
INSERT INTO table1 VALUES (1/0);
END;
/
INSERT INTO table1 VALUES (1);
CALL raise_error();
COMMIT;
SELECT * FROM table1;
Kode PostgreSQL ( db <> fiddle ):
CREATE TABLE table1 (col1 int);
CREATE PROCEDURE raise_error() AS $$ BEGIN INSERT INTO table1 VALUES (1/0); END; $$ LANGUAGE plpgsql;
BEGIN TRANSACTION; -- disable auto-commit
INSERT INTO table1 VALUES (1);
CALL raise_error();
COMMIT;
SELECT * FROM table1;
Catatan: Di PostgreSQL saya juga menjalankan BEGIN TRANSACTION
pernyataan untuk menonaktifkan komit otomatis, karena Oracle tidak memiliki komit otomatis, dan saya ingin kedua kode serupa.
Hasil SELECT * FROM table1
kueri adalah satu baris di Oracle, dan tidak ada baris di PostgreSQL.
Seperti yang Anda lihat, kode analog di Oracle dan PostgreSQL memberikan hasil yang berbeda. Apa alasan perbedaan dalam penanganan kesalahan default ini?
Jawaban
Oracle dan PostgreSQL memang berperilaku berbeda di sini.
Oracle memiliki sesuatu yang saya sebut "rollback tingkat pernyataan": jika pernyataan yang berjalan di dalam transaksi menyebabkan kesalahan, hanya efek dari pernyataan itu yang dibatalkan, dan transaksi berlanjut.
Di PostgreSQL, kesalahan apa pun di dalam transaksi membatalkan seluruh transaksi, jadi Anda hanya dapat membatalkan transaksi, dan itu tidak memiliki efek sama sekali. Ini lebih dalam semangat "semua atau tidak sama sekali", tetapi sejauh yang saya lihat, standar SQL tidak spesifik tentang ini, jadi kedua perilaku dapat diperdebatkan.
Namun, Anda dapat menggunakan titik simpanan yang sesuai standar di PostgreSQL untuk "memulihkan" dari kesalahan dalam transaksi:
START TRANSACTION;
INSERT INTO table1 VALUES (1);
/* set a savepoint we can revert to */
SAVEPOINT x;
CALL raise_error();
ROLLBACK TO SAVEPOINT x;
/* now the INSERT can be committed */
COMMIT;
Namun berhati-hatilah agar Anda tidak menggunakan terlalu banyak savepoint (tidak lebih dari 64) per transaksi, jika tidak, performa dapat menurun.
Di Oracle, Anda menggunakan dua transaksi terpisah, yang pertama berhasil tetapi yang kedua gagal. Di PostgreSQL, Anda secara eksplisit memberitahukannya untuk hanya menggunakan satu transaksi dan menangani pernyataan bersama-sama.
Di Oracle, jika Anda menggunakan blok anonim PL / SQL untuk mengelompokkan pernyataan menjadi satu transaksi:
BEGIN
INSERT INTO table1 VALUES (1);
raise_error();
END;
/
Dan, setara di PostgreSQL:
DO
$$ BEGIN INSERT INTO table1 VALUES (1); CALL raise_error(); END; $$ LANGUAGE plpgsql;
Maka tidak akan ada baris dalam tabel karena pengecualian dari prosedur akan mengembalikan seluruh transaksi.
Atau, di Oracle, Anda dapat melakukan:
INSERT INTO table1 VALUES (1);
DECLARE
divide_by_zero EXCEPTION;
PRAGMA EXCEPTION_INIT( divide_by_zero, -1476 );
BEGIN
raise_error();
EXCEPTION
WHEN DIVIDE_BY_ZERO THEN
ROLLBACK;
END;
/
Yang akan memiliki efek yang sama untuk mengembalikan kedua transaksi ke komit terakhir.
db <> biola Oracle PostgreSQL