Inna domyślna obsługa błędów w Oracle i PostgreSQL

Question

Porównuję domyślne zachowanie Oracle i PostgreSQL po napotkaniu błędu w kodzie PL / SQL (PL / pgSQL). W tym celu napisałem analogiczny kod Oracle i PostgreSQL pokazany poniżej.

Kod Oracle ( db <> skrzypce ):

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;

Kod PostgreSQL ( skrzypce db <> ):

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;

Uwaga: W PostgreSQL dodatkowo uruchamiam BEGIN TRANSACTIONinstrukcję wyłączającą automatyczne zatwierdzanie, ponieważ Oracle nie ma automatycznego zatwierdzania i chcę, aby oba kody były analogiczne.

Wynikiem SELECT * FROM table1zapytania jest jeden wiersz w Oracle i brak wierszy w PostgreSQL.

Jak widać, analogiczny kod w Oracle i PostgreSQL daje różne wyniki. Jaki jest powód tej różnicy w domyślnej obsłudze błędów?

Answer 1

Oracle i PostgreSQL rzeczywiście zachowują się tutaj inaczej.

Oracle ma coś, co nazwałbym „wycofywaniem na poziomie instrukcji”: jeśli instrukcja uruchomiona w transakcji powoduje błąd, tylko skutki tej instrukcji są wycofywane, a transakcja jest kontynuowana.

W PostgreSQL każdy błąd wewnątrz transakcji powoduje przerwanie całej transakcji, więc możesz tylko wycofać transakcję i nie ma to żadnego wpływu. Jest to bardziej w duchu „wszystko albo nic”, ale z tego co widzę, standard SQL nie określa tego konkretnego, więc można dyskutować o obu zachowaniach.

Możesz jednak użyć standardowych, zgodnych punktów zapisu w PostgreSQL, aby „odzyskać” po błędzie w transakcji:

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;

Ale pamiętaj, że nie używasz zbyt wielu punktów zapisu (nie więcej niż 64) na transakcję, w przeciwnym razie wydajność może ucierpieć.

Answer 2

W Oracle korzystasz z dwóch oddzielnych transakcji, pierwsza kończy się sukcesem, a druga kończy się niepowodzeniem. W PostgreSQL wyraźnie mówisz mu, aby używał tylko jednej transakcji i razem obsługiwał instrukcje.

W Oracle, jeśli używasz anonimowego bloku PL / SQL do zgrupowania wyciągów w jedną transakcję:

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

I równoważnie w PostgreSQL:

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

Wtedy w tabeli nie będzie żadnych wierszy, ponieważ wyjątek od procedury spowoduje wycofanie całej transakcji.

---

Lub w Oracle możesz:

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

Co miałoby ten sam efekt, gdyby cofnąć obie transakcje do ostatniego zatwierdzenia.

db <> fiddle Oracle PostgreSQL