트랜잭션 관리 - Amazon Redshift
기본 모드 저장 프로시저 트랜잭션 관리NONATOMIC 모드 저장 프로시저 트랜잭션 관리

트랜잭션 관리

기본 트랜잭션 관리 동작 또는 NONATOMIC 동작을 사용하여 저장 프로시저를 만들 수 있습니다.

기본 모드 저장 프로시저 트랜잭션 관리

기본 트랜잭션 모드 자동 커밋 동작은 별도로 실행되는 각각의 SQL 명령이 개별적으로 커밋되게 합니다. 저장 프로시저 호출은 단일 SQL 명령으로 처리됩니다. 프로시저 내부의 SQL 문은 호출이 시작되면 암시적으로 시작되고 호출이 끝나면 종료되는 트랜잭션 블록에 있는 것처럼 동작합니다. 다른 프로시저에 대한 중첩 호출은 다른 SQL 문처럼 취급되고 호출자와 동일한 트랜잭션의 컨텍스트 내에서 작동합니다. 자동 커밋 동작에 대한 자세한 내용은 직렬화 가능 격리 섹션을 참조하세요.

그러나 사용자 지정 트랜잭션 블록(BEGIN...COMMIT에 의해 정의됨) 내에서 저장 프로시저를 호출한다고 가정합니다. 이 경우 저장 프로시저의 모든 문은 사용자 지정 트랜잭션의 컨텍스트에서 실행됩니다. 프로시저는 종료 시 암시적으로 커밋하지 않습니다. 호출자는 프로시저 커밋 또는 롤백을 제어합니다.

저장 프로시저를 실행하는 동안 오류가 발생하면 현재 트랜잭션의 모든 변경 사항이 롤백됩니다.

저장 프로시저에서 다음과 같은 트랜잭션 제어 문을 사용할 수 있습니다.

  • COMMIT - 현재 트랜잭션에서 수행된 모든 작업을 커밋하고 새 트랜잭션을 암시적으로 시작합니다. 자세한 내용은 COMMIT 섹션을 참조하세요.

  • ROLLBACK - 현재 트랜잭션에서 수행된 작업을 롤백하고 새 트랜잭션을 암시적으로 시작합니다. 자세한 내용은 ROLLBACK 섹션을 참조하세요.

TRUNCATE는 저장 프로시저 내에서 발행할 수 있는 또 다른 문으로 트랜잭션 관리에 영향을 줍니다. Amazon Redshift에서 TRUNCATE는 커밋을 암시적으로 실행합니다. 이 동작은 저장 프로시저의 컨텍스트에서 동일하게 유지됩니다. 저장 프로시저 내에서 TRUNCATE 문이 실행되면 현재 트랜잭션을 커밋하고 새 트랜잭션을 시작합니다. 자세한 내용은 TRUNCATE 섹션을 참조하세요.

COMMIT, ROLLBACK 또는 TRUNCATE 문을 따르는 모든 문은 새 트랜잭션의 컨텍스트에서 실행됩니다. COMMIT, ROLLBACK 또는 TRUNCATE 문이 나타나거나 저장 프로시저가 종료될 때까지 실행됩니다.

저장 프로시저 내에서 COMMIT, ROLLBACK 또는 TRUNCATE 문을 사용하는 경우, 다음 제약이 적용됩니다.

  • 트랜잭션 블록 내에서 저장 프로시저가 호출된 경우, COMMIT, ROLLBACK 문을 TRUNCATE 문을 실행할 수 없습니다. 이 제약은 저장 프로시저의 자체 본문 및 중첩된 모든 프로시저 호출 내에 적용됩니다.

  • 저장 프로시저가 SET config 옵션으로 생성된 경우, COMMIT, ROLLBACK 문을 TRUNCATE 문을 실행할 수 없습니다. 이 제약은 저장 프로시저의 자체 본문 및 중첩된 모든 프로시저 호출 내에 적용됩니다.

  • COMMIT, ROLLBACK 또는 TRUNCATE 문이 처리되면 (명시적으로 또는 암시적으로) 열린 모든 커서가 자동으로 닫힙니다. 명시적 및 암시적 커서에 대한 제약은 저장 프로시저 지원 고려 사항 섹션을 참조하세요.

또한 동적 SQL을 사용하여 COMMIT 또는 ROLLBACK을 실행할 수 없습니다. 그러나 동적 SQL을 사용하여 TRUNCATE를 실행할 수 있습니다. 자세한 내용은 Dynamic SQL 섹션을 참조하세요.

저장 프로시저를 작업할 때 PL/pgSQL의 BEGIN 및 END 문은 그룹화에만 사용된다는 점을 고려하십시오. 트랜잭션을 시작하거나 종료하지 않습니다. 자세한 내용은 블록 섹션을 참조하세요.

다음 예제에서는 명시적 트랜잭션 블록 내에서 저장 프로시저를 호출할 때 트랜잭션 동작을 보여 줍니다. 저장 프로시저 외부에서 실행된 두 삽입 문과 내부에서 실행된 한 삽입 문은 모두 동일한 트랜잭션(3382)의 일부입니다. 사용자가 명시적 커밋을 실행하면 트랜잭션이 커밋됩니다.

CREATE OR REPLACE PROCEDURE sp_insert_table_a(a int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test_table_a values (a);
END;
$$;

Begin;
  insert into test_table_a values (1);
  Call sp_insert_table_a(2);
  insert into test_table_a values (3);
Commit; 

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  | pid |  type   |               stmt_text
--------+------+-----+---------+----------------------------------------
    103 | 3382 | 599 | UTILITY | Begin;
    103 | 3382 | 599 | QUERY   | insert into test_table_a values (1);
    103 | 3382 | 599 | UTILITY | Call sp_insert_table_a(2);
    103 | 3382 | 599 | QUERY   | INSERT INTO test_table_a values ( $1 )
    103 | 3382 | 599 | QUERY   | insert into test_table_a values (3);
    103 | 3382 | 599 | UTILITY | COMMIT

반대로 동일한 문이 명시적 트랜잭션 블록 외부에서 실행되고 세션이 자동 커밋을 ON으로 설정한 경우를 예로 들겠습니다. 이 경우 각 문은 자체 트랜잭션에서 실행됩니다.

insert into test_table_a values (1);
Call sp_insert_table_a(2);
insert into test_table_a values (3);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  | pid |  type   |                                                                    stmt_text
--------+------+-----+---------+-------------------------------------------------------------------------------------------------------------------------------------------------
    103 | 3388 | 599 | QUERY   | insert into test_table_a values (1);
    103 | 3388 | 599 | UTILITY | COMMIT
    103 | 3389 | 599 | UTILITY | Call sp_insert_table_a(2);
    103 | 3389 | 599 | QUERY   | INSERT INTO test_table_a values ( $1 )
    103 | 3389 | 599 | UTILITY | COMMIT
    103 | 3390 | 599 | QUERY   | insert into test_table_a values (3);
    103 | 3390 | 599 | UTILITY | COMMIT

다음 예제에서는 test_table_a에 삽입한 후 TRUNCATE 문을 실행합니다. TRUNCATE 문은 현재 트랜잭션(3335)을 커밋하고 새 트랜잭션(3336)을 시작하는 암시적 커밋을 실행합니다. 프로시저가 종료되면 새 트랜잭션이 커밋됩니다.

CREATE OR REPLACE PROCEDURE sp_truncate_proc(a int, b int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test_table_a values (a);
  TRUNCATE test_table_b;
  INSERT INTO test_table_b values (b);
END;
$$;

Call sp_truncate_proc(1,2);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |  pid  |  type   |                                                                                             stmt_text
--------+------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    103 | 3335 | 23636 | UTILITY | Call sp_truncate_proc(1,2);
    103 | 3335 | 23636 | QUERY   | INSERT INTO test_table_a values ( $1 )
    103 | 3335 | 23636 | UTILITY | TRUNCATE test_table_b
    103 | 3335 | 23636 | UTILITY | COMMIT
    103 | 3336 | 23636 | QUERY   | INSERT INTO test_table_b values ( $1 )
    103 | 3336 | 23636 | UTILITY | COMMIT

다음 예제에서는 중첩 호출에서 TRUNCATE를 실행합니다. TRUNCATE는 트랜잭션(3344)의 외부 및 내부 프로시저에서 지금까지 완료된 모든 작업을 커밋합니다. 새 트랜잭션(3345)을 시작합니다. 외부 프로시저가 종료되면 새 트랜잭션이 커밋됩니다.

CREATE OR REPLACE PROCEDURE sp_inner(c int, d int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO inner_table values (c);
  TRUNCATE outer_table;
  INSERT INTO inner_table values (d);
END;
$$;

CREATE OR REPLACE PROCEDURE sp_outer(a int, b int, c int, d int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO outer_table values (a);
  Call sp_inner(c, d);
  INSERT INTO outer_table values (b);
END;
$$;

Call sp_outer(1, 2, 3, 4);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |  pid  |  type   |                                                                                              stmt_text
--------+------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    103 | 3344 | 23636 | UTILITY | Call sp_outer(1, 2, 3, 4);
    103 | 3344 | 23636 | QUERY   | INSERT INTO outer_table values ( $1 )
    103 | 3344 | 23636 | UTILITY | CALL sp_inner( $1 , $2 )
    103 | 3344 | 23636 | QUERY   | INSERT INTO inner_table values ( $1 )
    103 | 3344 | 23636 | UTILITY | TRUNCATE outer_table
    103 | 3344 | 23636 | UTILITY | COMMIT
    103 | 3345 | 23636 | QUERY   | INSERT INTO inner_table values ( $1 )
    103 | 3345 | 23636 | QUERY   | INSERT INTO outer_table values ( $1 )
    103 | 3345 | 23636 | UTILITY | COMMIT

다음 예제에서는 TRUNCATE 문이 커밋되었을 때 커서 cur1이 닫혔다는 것을 보여 줍니다.

CREATE OR REPLACE PROCEDURE sp_open_cursor_truncate()
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  cur1 cursor for select * from test_table_a order by 1;
BEGIN
  open cur1;
  TRUNCATE table test_table_b;
  Loop
    fetch cur1 into rec;
    raise info '%', rec.c1;
    exit when not found;
  End Loop;
END
$$;

call sp_open_cursor_truncate();
ERROR: cursor "cur1" does not exist
CONTEXT: PL/pgSQL function "sp_open_cursor_truncate" line 8 at fetch

다음 예제에서는 TRUNCATE 문을 실행하고 명시적 트랜잭션 블록 내에서 호출할 수 없습니다.

CREATE OR REPLACE PROCEDURE sp_truncate_atomic() LANGUAGE plpgsql
AS $$
BEGIN
  TRUNCATE test_table_b;
END;
$$;

Begin;
  Call sp_truncate_atomic();
ERROR: TRUNCATE cannot be invoked from a procedure that is executing in an atomic context.
HINT: Try calling the procedure as a top-level call i.e. not from within an explicit transaction block. 
Or, if this procedure (or one of its ancestors in the call chain) was created with SET config options, recreate the procedure without them.
CONTEXT: SQL statement "TRUNCATE test_table_b"
PL/pgSQL function "sp_truncate_atomic" line 2 at SQL statement

다음 예에서는 슈퍼 사용자 또는 테이블 소유자가 아닌 사용자가 테이블에 TRUNCATE 문을 실행할 수 있음을 보여줍니다. 사용자는 Security Definer 저장 프로시저를 사용하여 이 작업을 수행합니다. 이 예에서는 다음 작업을 보여줍니다.

  • user1이 테이블 test_tbl을 생성합니다.

  • user1이 저장 프로시저 sp_truncate_test_tbl을 생성합니다.

  • user1은 저장 프로시저에 대한 EXECUTE 권한을 user2에게 부여합니다.

  • user2는 저장 프로시저를 실행하여 테이블 test_tbl을 잘라냅니다. 이 예에서는 TRUNCATE 명령 전후의 행 수를 보여줍니다.

set session_authorization to user1;
create table test_tbl(id int, name varchar(20));
insert into test_tbl values (1,'john'), (2, 'mary');
CREATE OR REPLACE PROCEDURE sp_truncate_test_tbl() LANGUAGE plpgsql
AS $$
DECLARE
  tbl_rows int;
BEGIN
  select count(*) into tbl_rows from test_tbl;
  RAISE INFO 'RowCount before Truncate: %', tbl_rows;
  TRUNCATE test_tbl;
  select count(*) into tbl_rows from test_tbl;
  RAISE INFO 'RowCount after Truncate: %', tbl_rows;
END;
$$ SECURITY DEFINER;
grant execute on procedure sp_truncate_test_tbl() to user2;
reset session_authorization;


set session_authorization to user2;
call sp_truncate_test_tbl();
INFO:  RowCount before Truncate: 2
INFO:  RowCount after Truncate: 0
CALL
reset session_authorization;

다음 예에서는 COMMIT을 두 번 실행합니다. 첫 번째 COMMIT는 트랜잭션 10363에서 수행된 모든 작업을 커밋하고 트랜잭션 10364를 암시적으로 시작합니다. 트랜잭션 10364는 두 번째 COMMIT 문으로 커밋됩니다.

CREATE OR REPLACE PROCEDURE sp_commit(a int, b int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test_table values (a);
  COMMIT;
  INSERT INTO test_table values (b);
  COMMIT;
END;
$$;

call sp_commit(1,2);

select userid, xid, pid, type, trim(text) as stmt_text
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;
 userid |  xid  | pid  |  type   |                                                                                    stmt_text
--------+-------+------+---------+-----------------------------------------------------------------------------------------------------------------
    100 | 10363 | 3089 | UTILITY | call sp_commit(1,2);
    100 | 10363 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10363 | 3089 | UTILITY | COMMIT
    100 | 10364 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10364 | 3089 | UTILITY | COMMIT

다음 예제에서는 sum_vals가 2보다 큰 경우 ROLLBACK 문을 실행합니다. 첫 번째 ROLLBACK 문은 트랜잭션 10377에서 수행된 모든 작업을 롤백하고 새 트랜잭션 10378을 시작합니다. 프로시저가 종료되면 트랜잭션 10378이 커밋됩니다.

CREATE OR REPLACE PROCEDURE sp_rollback(a int, b int) LANGUAGE plpgsql
AS $$
DECLARE
  sum_vals int;
BEGIN
  INSERT INTO test_table values (a);
  SELECT sum(c1) into sum_vals from test_table;
  IF sum_vals > 2 THEN
    ROLLBACK;
  END IF;
  
  INSERT INTO test_table values (b);
END;
$$;

call sp_rollback(1, 2);

select userid, xid, pid, type, trim(text) as stmt_text
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

userid |  xid  | pid  |  type   |                                                                                    stmt_text
--------+-------+------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    100 | 10377 | 3089 | UTILITY | call sp_rollback(1, 2);
    100 | 10377 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10377 | 3089 | QUERY   | SELECT sum(c1) from test_table
    100 | 10377 | 3089 | QUERY   | Undoing 1 transactions on table 133646 with current xid 10377 : 10377
    100 | 10378 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10378 | 3089 | UTILITY | COMMIT

NONATOMIC 모드 저장 프로시저 트랜잭션 관리

NONATOMIC 모드에서 생성된 저장 프로시저는 기본 모드에서 생성된 프로시저와는 다른 트랜잭션 제어 동작을 가집니다. 저장 프로시저 외부에 있는 SQL 명령의 자동 커밋 동작과 유사하게, NONATOMIC 프로시저 내의 각 SQL 문은 자체 트랜잭션에서 실행되고 자동으로 커밋됩니다. 사용자가 NONATOMIC 저장 프로시저 내에서 명시적 트랜잭션 블록을 시작하는 경우 블록 내의 SQL 문은 자동으로 커밋되지 않습니다. 트랜잭션 블록은 트랜잭션 블록 내 문의 커밋 또는 롤백을 제어합니다.

NONATOMIC 저장 프로시저에서는 START TRANSACTION 문을 사용하여 프로시저 내에서 명시적 트랜잭션 블록을 열 수 있습니다. 하지만 이미 열려 있는 트랜잭션 블록이 있는 경우 Amazon Redshift는 하위 트랜잭션을 지원하지 않으므로 이 명령문은 아무런 동작도 하지 않습니다. 이전 트랜잭션이 계속됩니다.

NONATOMIC 프로시저에서 cursor FOR 루프를 사용하는 경우 쿼리 결과를 반복하기 전에 명시적 트랜잭션 블록을 열어야 합니다. 그러지 않으면 루프 내의 SQL 문이 자동으로 커밋될 때 커서가 닫힙니다.

NONATOMIC 모드 동작을 사용할 때 고려해야 할 몇 가지 사항은 다음과 같습니다.

  • 열린 트랜잭션 블록이 없고 세션에 자동 커밋이 켜짐으로 설정된 경우 저장 프로시저 내의 각 SQL 문이 자동으로 커밋됩니다.

  • 저장 프로시저가 트랜잭션 블록 내에서 호출되는 경우 COMMIT/ROLLBACK/TRUNCATE 문을 실행하여 트랜잭션을 종료할 수 있습니다. 이는 기본 모드에서는 불가능합니다.

  • START TRANSACTION 문을 실행하여 저장 프로시저 내에서 트랜잭션 블록을 시작할 수 있습니다.

다음 예제는 NONATOMIC 저장 프로시저를 사용할 때의 트랜잭션 동작을 보여줍니다. 다음 예제의 모든 세션에서는 자동 커밋이 켜짐으로 설정되어 있습니다.

다음 예제에서 NONATOMIC 저장 프로시저에는 2개의 INSERT 문이 있습니다. 트랜잭션 블록 외부에서 프로시저를 호출하면 프로시저 내의 모든 INSERT 문이 자동으로 커밋됩니다.

CREATE TABLE test_table_a(v int); 
CREATE TABLE test_table_b(v int); 

CREATE OR REPLACE PROCEDURE sp_nonatomic_insert_table_a(a int, b int) NONATOMIC AS
$$
BEGIN
    INSERT INTO test_table_a values (a);
    INSERT INTO test_table_b values (b);
END;
$$ 
LANGUAGE plpgsql;

Call sp_nonatomic_insert_table_a(1,2);

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |               stmt_text                
--------+------+------------+---------+----------------------------------------
      1 | 1792 | 1073807554 | UTILITY | Call sp_nonatomic_insert_table_a(1,2);
      1 | 1792 | 1073807554 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1792 | 1073807554 | UTILITY | COMMIT
      1 | 1793 | 1073807554 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1793 | 1073807554 | UTILITY | COMMIT
(5 rows)

그러나 BEGIN..COMMIT 블록 내에서 프로시저를 호출하면 모든 문이 동일한 트랜잭션의 일부가 됩니다(xid=1799).

Begin;
  INSERT INTO test_table_a values (10);
  Call sp_nonatomic_insert_table_a(20,30);
  INSERT INTO test_table_b values (40);
Commit; 

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |                stmt_text                 
--------+------+------------+---------+------------------------------------------
      1 | 1799 | 1073914035 | UTILITY | Begin;
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_a values (10);
      1 | 1799 | 1073914035 | UTILITY | Call sp_nonatomic_insert_table_a(20,30);
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_b values (40);
      1 | 1799 | 1073914035 | UTILITY | COMMIT
(7 rows)

이 예제에서는 START TRANSACTION...COMMIT 사이에 두 개의 INSERT 문이 있습니다. 프로시저가 트랜잭션 블록 외부에서 호출되면 두 INSERT 문은 동일한 트랜잭션에 있게 됩니다(xid=1866).

CREATE OR REPLACE PROCEDURE sp_nonatomic_txn_block(a int, b int) NONATOMIC AS
$$
BEGIN
    START TRANSACTION;
    INSERT INTO test_table_a values (a);
    INSERT INTO test_table_b values (b);
    COMMIT;
END;
$$ 
LANGUAGE plpgsql;

Call sp_nonatomic_txn_block(1,2);

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |               stmt_text                
--------+------+------------+---------+----------------------------------------
      1 | 1865 | 1073823998 | UTILITY | Call sp_nonatomic_txn_block(1,2);
      1 | 1866 | 1073823998 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1866 | 1073823998 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1866 | 1073823998 | UTILITY | COMMIT
(4 rows)

BEGIN...COMMIT 블록 내에서 프로시저를 호출하면 이미 열려 있는 트랜잭션이 있기 때문에 프로시저 내의 START TRANSACTION은 아무런 동작도 하지 않습니다. 프로시저 내의 COMMIT은 현재 트랜잭션(xid=1876)을 커밋하고 새 트랜잭션을 시작합니다.

Begin;
  INSERT INTO test_table_a values (10);
  Call sp_nonatomic_txn_block(20,30);
  INSERT INTO test_table_b values (40);
Commit; 

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |               stmt_text                
--------+------+------------+---------+----------------------------------------
      1 | 1876 | 1073832133 | UTILITY | Begin;
      1 | 1876 | 1073832133 | QUERY   | INSERT INTO test_table_a values (10);
      1 | 1876 | 1073832133 | UTILITY | Call sp_nonatomic_txn_block(20,30);
      1 | 1876 | 1073832133 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1876 | 1073832133 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1876 | 1073832133 | UTILITY | COMMIT
      1 | 1878 | 1073832133 | QUERY   | INSERT INTO test_table_b values (40);
      1 | 1878 | 1073832133 | UTILITY | COMMIT
(8 rows)

이 예제에서는 커서 루프를 사용하는 방법을 보여줍니다. 테이블 test_table_a에는 세 개의 값이 있습니다. 목표는 세 값을 반복하여 test_table_b 테이블에 삽입하는 것입니다. 다음과 같은 방식으로 NONATOMIC 저장 프로시저를 만들면 첫 번째 루프에서 INSERT 문을 실행한 후 커서 ‘cur1’이 존재하지 않는다는 오류가 발생합니다. 이는 INSERT의 자동 커밋이 열린 커서를 닫기 때문입니다.

insert into test_table_a values (1), (2), (3);

CREATE OR REPLACE PROCEDURE sp_nonatomic_cursor() NONATOMIC
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  cur1 cursor for select * from test_table_a order by 1;
BEGIN
  open cur1;
  Loop
    fetch cur1 into rec;
    exit when not found;
    raise info '%', rec.v;
    insert into test_table_b values (rec.v);
  End Loop;
END
$$;

CALL sp_nonatomic_cursor();

INFO:  1
ERROR:  cursor "cur1" does not exist
CONTEXT:  PL/pgSQL function "sp_nonatomic_cursor" line 7 at fetch

커서 루프가 작동하도록 하려면 START TRANSACTION...COMMIT 사이에 루프를 배치하세요.

insert into test_table_a values (1), (2), (3);

CREATE OR REPLACE PROCEDURE sp_nonatomic_cursor() NONATOMIC
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  cur1 cursor for select * from test_table_a order by 1;
BEGIN
  START TRANSACTION;
  open cur1;
  Loop
    fetch cur1 into rec;
    exit when not found;
    raise info '%', rec.v;
    insert into test_table_b values (rec.v);
  End Loop;
  COMMIT;
END
$$;

CALL sp_nonatomic_cursor();

INFO:  1
INFO:  2
INFO:  3
CALL