トランザクションの管理
ストアドプロシージャは、デフォルトのトランザクション管理動作または非アトミック動作で作成できます。
デフォルトモードのストアドプロシージャのトランザクション管理
デフォルトのトランザクションモードの自動コミット動作では、個別に実行された SQL コマンドが個別にコミットされます。ストアドプロシージャへの呼び出しは、単一の SQL コマンドとして扱われます。プロシージャ内の SQL ステートメントは、呼び出しの開始時および終了時に暗黙的に開始および終了するトランザクションブロックに存在するかのように動作します。別のプロシージャに対するネストされた呼び出しは、他の任意の SQL ステートメントのように扱われ、呼び出し元と同じトランザクションのコンテキスト内で動作します。自動コミット動作の詳細については、「直列化可能分離」を参照してください。
ただし、ユーザー指定のトランザクションブロック (BEGIN... COMMIT で定義される) 内からストアドプロシージャを呼び出すとします。この場合、ストアドプロシージャのすべてのステートメントはユーザー指定のトランザクションのコンテキストで実行されます。プロシージャは終了時に暗黙でコミットしません。呼び出し元がプロシージャのコミットまたはロールバックを制御します。
ストアドプロシージャの実行中にエラーが発生した場合は、現在のトランザクションで行われたすべての変更がロールバックされます。
ストアドプロシージャでは、次のトランザクション制御ステートメントを使用できます。
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 を実行することができます。詳細については、「動的 SQL」を参照してください。
ストアドプロシージャを使用する場合、PL/pgSQL の BEGIN および END ステートメントはグループ化のみを目的としている点を考慮してください。トランザクションを開始または終了することはありません。詳細については、「ブロック」を参照してください。
次の例は、ストアドプロシージャを明示的なトランザクションブロック内から呼び出した場合のトランザクション動作を示しています。ストアドプロシージャの外部から発行された 2 つの挿入ステートメントと内部から発行された 1 つの挿入ステートメントは、すべて同じトランザクション (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
それに対して、明示的なトランザクションブロックの外部から同じステートメントが発行され、セッションの自動コミットがオンに設定されている場合の例を示します。この場合、各ステートメントは独自のトランザクションで実行されます。
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
次の例では、TRUNCATE ステートメントを test_table_a 内に挿入後に発行しています。。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ストアドプロシージャを使用して、これを行います。この例では以下のアクションを示します。
ユーザー 1 はテーブル
test_tblを作成します。ユーザー 1 はストアドプロシージャ
sp_truncate_test_tblを作成します。ユーザー 1 は、ストアドプロシージャに対する
EXECUTE権限をユーザー 2 に付与します。ユーザー 2 は、ストアドプロシージャを実行してテーブル
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 を 2 回発行しています。最初の COMMIT では、トランザクション 10363 で行われたすべての作業をコミットし、トランザクション 10364 を暗黙的に開始します。トランザクション 10364 は、2 番目の 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 モードで作成されたストアドプロシージャは、デフォルトモードで作成されたプロシージャとはトランザクション制御動作が異なります。ストアドプロシージャ外の SQL コマンドの自動コミット動作と同様に、NONATOMIC プロシージャの内側の各 SQL ステートメントは、独自のトランザクションで実行され、自動的にコミットします。ユーザーが NONATOMIC ストアドプロシージャ内で明示的なトランザクションブロックを開始した場合、ブロック内の SQL ステートメントは自動的にコミットされません。トランザクションブロックは、その中のステートメントのコミットまたはロールバックを制御します。
NONATOMIC ストアドプロシージャでは、START TRANSACTION ステートメントを使用してプロシージャ内の明示的なトランザクションブロックを開くことができます。ただし、既に開いているトランザクションブロックがある場合、Amazon Redshift はサブトランザクションをサポートしていないため、このステートメントは何もしません。前のトランザクションは続行されます。
NONATOMIC プロシージャ内でカーソルの FOR ループを操作する場合は、クエリの結果を繰り返し処理する前に、必ず明示的なトランザクションブロックを開いてください。それ以外の場合は、ループ内の SQL ステートメントが自動的にコミットされたときにカーソルが閉じられます。
NONATOMIC モードの動作を使用する際の考慮事項は次のとおりです。
ストアドプロシージャ内の各 SQL ステートメントは、開いているトランザクションブロックがなく、セッションの自動コミットがオンに設定されている場合、自動的にコミットされます。
ストアドプロシージャがトランザクションブロック内から呼び出された場合、COMMIT、ROLLBACK、TRUNCATE ステートメントを発行してトランザクションを終了できます。これはデフォルトモードでは不可能です。
START TRANSACTION ステートメントを発行すると、ストアドプロシージャ内のトランザクションブロックを開始できます。
次の例は、NONATOMIC ストアドプロシージャを使用する場合のトランザクション動作を示しています。以下のすべての例のセッションでは、オートコミットが ON に設定されています。
次の例では、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)
この例では、トランザクションの開始とコミットの間に 2 つの INSERT ステートメントがあります。プロシージャがトランザクションブロックの外部で呼び出されると、2 つの 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)
プロシージャが BBEGIN...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 には 3 つの値があります。目的は、3 つの値を繰り返し処理して、テーブル 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
カーソルをループさせるには、トランザクションの開始とコミットの間にカーソルを置いてください。
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
