Oracle 및 PostgreSQL의 다른 기본 오류 처리

Dec 17 2020

PL / SQL (PL / pgSQL) 코드에서 오류가 발생한 후 Oracle과 PostgreSQL의 기본 동작을 비교합니다. 이를 위해 아래에 표시된 유사한 Oracle 및 PostgreSQL 코드를 작성했습니다.

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;

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;

참고 : PostgreSQL에서는 BEGIN TRANSACTIONOracle에 자동 커밋이없고 두 코드가 유사하기를 원하기 때문에 자동 커밋을 비활성화 하는 명령문을 추가로 실행합니다 .

SELECT * FROM table1쿼리 결과는 Oracle에서는 한 행이고 PostgreSQL에서는 행이 없습니다.

보시다시피 Oracle과 PostgreSQL의 유사한 코드는 다른 결과를 제공합니다. 기본 오류 처리에서 이러한 차이점의 이유는 무엇입니까?

답변

2 LaurenzAlbe Dec 17 2020 at 21:40

Oracle과 PostgreSQL은 실제로 여기에서 다르게 작동합니다.

Oracle에는 "문 수준 롤백"이라고하는 것이 있습니다. 트랜잭션 내에서 실행되는 문이 오류를 발생 시키면 해당 문의 효과 만 롤백되고 트랜잭션이 계속됩니다.

PostgreSQL에서는 트랜잭션 내부의 모든 오류가 전체 트랜잭션을 중단하므로 트랜잭션을 롤백 할 수만 있고 아무런 영향도 미치지 않습니다. 이것은 "전부 또는 전무"의 정신에 더 가깝지만, 제가 볼 수있는 한 SQL 표준은 이것에 대해 구체적이지 않으므로 두 가지 동작 모두에 대해 논쟁 할 수 있습니다.

그러나 PostgreSQL의 표준 준수 저장 점을 사용하여 트랜잭션 오류를 "복구"할 수 있습니다.

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;

그러나 트랜잭션 당 너무 많은 저장 점 (64 개 이하)을 사용하지 마십시오 . 그렇지 않으면 성능이 저하 될 수 있습니다.

1 MT0 Dec 17 2020 at 21:24

Oracle에서는 두 개의 개별 트랜잭션을 사용하고 있는데 첫 번째는 성공하지만 두 번째는 실패합니다. PostgreSQL에서는 하나의 트랜잭션 만 사용하고 명령문을 함께 처리하도록 명시 적으로 지시합니다.

Oracle에서 PL / SQL 익명 블록을 사용하여 명령문을 단일 트랜잭션으로 그룹화하는 경우 :

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

그리고 PostgreSQL에서도 동일하게 :

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

그러면 프로 시저의 예외가 전체 트랜잭션을 롤백하므로 테이블에 행이 없습니다.


또는 Oracle에서는 다음을 수행 할 수 있습니다.

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

두 트랜잭션을 마지막 커밋으로 롤백하는 것과 동일한 효과가 있습니다.

db <> fiddle Oracle PostgreSQL