みなさん、こんばんは。小寺です。
Redshiftでストアドプロシージャで自動コミットの機能がサポートされました。
Redshiftのストアドプロシージャとは
ストアドプロシージャは、通常、データ変換やデータ検証のロジック、ビジネス固有のロジックをカプセル化するために使用します。
複数の SQL ステップをストアドプロシージャにまとめることで、アプリケーションとデータベースの接続を簡単にできます。
アップデート内容
今まではプロシージャ内でステートメントの自動コミットがサポートされておらず、明示的なコミットが必要でした。
今回のアップデートでプロシージャ内のステートメントを自動的にコミットできるようになりました。
NONATOMIC モードというのものが追加になっています。利用シーンとしては、ストアド プロシージャ内の例外をよりスムーズに処理したいアプリケーション向けです。
PL/pgSQL ステートメント RAISE を使用して、必要に応じてプログラムで例外を発生させ、アプリケーションフロー上の中断も防げるようになりました。
ストアド プロシージャを NONATOMICで利用するときの注意点
ストアド プロシージャ呼び出しをネストする場合は、すべてのプロシージャを同じトランザクション モードで作成する必要があります。
また、NONATOMIC モードでプロシージャを作成する場合、SECURITY DEFINER オプションと SETconfiguration_parameter オプションはサポートされません。
オープンされたカーソルは、暗黙的なコミットが処理されるときに自動的にクローズされます。なので、カーソル ループを開始する前に明示的なトランザクションを実行し、ループの反復 SQL が暗黙的にコミットされないように処理する必要があります。
実際の動作例
NONATOMIC ストアドプロシージャに 2 つの INSERT ステートメントがあるとします。
プロシージャがトランザクション ブロックの外で呼び出される場合、プロシージャ内のすべての INSERT ステートメントが自動的にコミットされます。
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)
ただし、プロシージャが BEGIN..COMMIT ブロック内から呼び出される場合、ステートメントは全て同じトランザクション (xid=1799) の一部として処理されます。
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)
