Xử lý lỗi mặc định khác nhau trong Oracle và PostgreSQL

Question

Tôi so sánh hành vi mặc định của Oracle và PostgreSQL sau khi gặp lỗi trong mã PL / SQL (PL / pgSQL). Vì mục đích này, tôi đã viết một mã Oracle và PostgreSQL tương tự như hình bên dưới.

Mã 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;

Mã 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;

Lưu ý: Trong PostgreSQL, tôi cũng chạy BEGIN TRANSACTIONcâu lệnh để tắt tính năng tự động cam kết, vì Oracle không có tính năng tự động cam kết và tôi muốn cả hai mã tương tự nhau.

Kết quả của SELECT * FROM table1truy vấn là một hàng trong Oracle và không có hàng nào trong PostgreSQL.

Như bạn có thể thấy, mã tương tự trong Oracle và PostgreSQL cho kết quả khác nhau. Lý do của sự khác biệt này trong việc xử lý lỗi mặc định là gì?

Answer 1

Oracle và PostgreSQL thực sự hoạt động khác nhau ở đây.

Oracle có một cái gì đó mà tôi sẽ gọi là “khôi phục mức câu lệnh”: nếu một câu lệnh chạy bên trong một giao dịch gây ra lỗi, thì chỉ những ảnh hưởng của câu lệnh đó mới được khôi phục và giao dịch tiếp tục.

Trong PostgreSQL, bất kỳ lỗi nào bên trong một giao dịch sẽ hủy bỏ toàn bộ giao dịch, vì vậy bạn chỉ có thể khôi phục giao dịch và nó không có tác dụng gì cả. Điều này theo tinh thần “tất cả hoặc không có gì”, nhưng theo như tôi thấy, tiêu chuẩn SQL không cụ thể về điều này, vì vậy cả hai hành vi có thể được tranh luận.

Tuy nhiên, bạn có thể sử dụng các điểm lưu phù hợp tiêu chuẩn trong PostgreSQL để "khôi phục" từ một lỗi trong giao dịch:

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;

Nhưng hãy lưu ý rằng bạn không sử dụng quá nhiều điểm lưu (không quá 64) cho mỗi giao dịch, nếu không hiệu suất có thể bị ảnh hưởng.

Answer 2

Trong Oracle, bạn đang sử dụng hai giao dịch riêng biệt, giao dịch đầu tiên thành công nhưng giao dịch thứ hai không thành công. Trong PostgreSQL, bạn đang yêu cầu nó chỉ sử dụng một giao dịch và xử lý các câu lệnh cùng nhau.

Trong Oracle, nếu bạn sử dụng khối ẩn danh PL / SQL để nhóm các câu lệnh thành một giao dịch duy nhất:

BEGIN
  INSERT INTO table1 VALUES (1);
  raise_error();
END;
/

Và, tương tự trong PostgreSQL:

DO
$$ BEGIN INSERT INTO table1 VALUES (1); CALL raise_error(); END; $$ LANGUAGE plpgsql;

Sau đó, sẽ không có hàng nào trong bảng vì ngoại lệ từ quy trình sẽ khôi phục toàn bộ giao dịch.

---

Hoặc, trong Oracle, bạn có thể làm:

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;
/

Điều này sẽ có tác dụng giống nhau khi lùi cả hai giao dịch về lần cam kết cuối cùng.

db <> fiddle Oracle PostgreSQL