はじめに

前回に引き続き、ストアドプロシージャの開発に入門していく。
今回はカーソルについて学ぶ。

環境

Windows 11 Professional
PostgreSQL 17
Docker Desktop 4.37.0 (178034)

カーソルとは

• 43.7. カーソル | PostgreSQL 16.4文書
  https://www.postgresql.jp/document/16/html/plpgsql-cursors.html

上記を参照する。
以下は簡単にまとめた内容となる。

PostgreSQLのカーソルとは

PostgreSQLのカーソルは、クエリ結果の行を1行ずつ処理するための仕組み。
大量のデータを少しずつ扱いたい場合や、1行ごとに特定の処理を実行する場合に便利である。
カーソルを利用することで、効率的かつ柔軟にデータ処理を行える。

カーソルの主な操作

カーソルを使用する基本的な手順は以下の通り

1. カーソルの宣言
   カーソルを宣言して準備する。
2. カーソルのオープン
   クエリを指定してカーソルを開く。
3. データの取得
   カーソルから1行ずつデータを取得する。
4. カーソルのクローズ
   処理が完了したらカーソルを閉じる。

カーソルの種類

PostgreSQLのカーソルには2種類がある

1. シンプルカーソル（REFCURSOR）
   ストアドプロシージャやPL/pgSQLで使われるカーソル。明示的にオープン・クローズが必要。
2. 暗黙的カーソル
   FOR ... IN ループで自動的に作られるカーソル。通常は明示的なカーソル宣言が不要な場合に使う。

参考になるドキュメント

• サポートされている PL/pgSQL ステートメント
  https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_PLpgSQL-statements.html

準備

今回もサンプルを用意したので下記のGitHubリポジトリに置いておいた。

• katsuobushiFPGA / postgresql-procedure-dev
  https://github.com/katsuobushiFPGA/postgresql-procedure-dev

カーソルを使ったサンプル

以下は、カーソルを使ってtodosテーブルのデータを1行ずつ処理するストアドプロシージャの例。

テーブル定義

CREATE TABLE todos (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  due_date DATE,
  is_completed BOOLEAN DEFAULT FALSE
);

ストアドプロシージャ

CREATE OR REPLACE PROCEDURE process_todos_with_cursor()
LANGUAGE plpgsql
AS $$
DECLARE
  todo_cursor REFCURSOR;         -- カーソルの宣言
  todo_record todos%ROWTYPE;     -- todosテーブルの1行を格納する変数
BEGIN
  -- カーソルを開く
  OPEN todo_cursor FOR
    SELECT * FROM todos; -- todosテーブルのすべての行を取得

  -- カーソルから1行ずつ取得して処理
  LOOP
    FETCH todo_cursor INTO todo_record; -- カーソルから次の行を取得
    EXIT WHEN NOT FOUND;               -- データがない場合はループを終了

    -- 各行に対する処理
    RAISE NOTICE 'Processing todo: ID = %, Title = %', todo_record.id, todo_record.title;
  END LOOP;

  -- カーソルを閉じる
  CLOSE todo_cursor;
END;
$$;

実行例

カーソルを使うストアドプロシージャを実行するには以下のコマンドを使用

-- そのままCALLで呼び出し。
CALL process_todos_with_cursor();

-- ストアドのステートメントを使うなら
DO $$
  BEGIN
    CALL process_todos_with_cursor();
  END;
$$;

実行時、各行の情報がログに出力される。
今回の場合以下の結果が出力された。

NOTICE:  Processing todo: ID = 2, Title = Finish project report
NOTICE:  Processing todo: ID = 3, Title = Call plumber
NOTICE:  Processing todo: ID = 4, Title = Book flight tickets
NOTICE:  Processing todo: ID = 5, Title = Schedule dentist appointment
NOTICE:  Processing todo: ID = 6, Title = Prepare presentation
NOTICE:  Processing todo: ID = 7, Title = Renew car insurance
NOTICE:  Processing todo: ID = 8, Title = Organize garage
NOTICE:  Processing todo: ID = 9, Title = Plan birthday party
NOTICE:  Processing todo: ID = 10, Title = Read new book

サンプル解説

1. カーソルの宣言

todo_cursor REFCURSOR;

• カーソルはREFCURSOR型として宣言する。これはカーソル名を格納するための変数。

2. カーソルのオープン

OPEN todo_cursor FOR SELECT * FROM todos;

• OPEN文でカーソルを開く。クエリを指定することで、その結果をカーソルに関連付ける。

3. データの取得

FETCH todo_cursor INTO todo_record;

• FETCH文を使ってカーソルから1行を取得し、todo_recordに格納する。

4. ループの終了条件

EXIT WHEN NOT FOUND;

• カーソルから取得できるデータがなくなった場合、自動的にNOT FOUND状態になる。この条件を利用してループを終了する。

5. カーソルのクローズ

CLOSE todo_cursor;

• 処理が終わったらカーソルを閉じる。クローズを忘れるとリソースが無駄に消費される可能性がある。

注意点

1. カーソルのパフォーマンス

   • 小規模なデータセットではカーソルを使う必要はない。大規模なデータや、行ごとの細かい処理が必要な場合に適している。

2. カーソルの明示的な管理

   • カーソルを明示的に使用する場合、OPEN と CLOSE を確実に実行する。リソースリークを防ぐために重要。

3. 例外処理

   • カーソルを使う際、エラーハンドリングを適切に設定することで、途中で失敗してもリソースが解放されるようにする。

例えば、今回のケースではEXCEPTION句がないのでリソース解放されない。
そのため、EXCEPTION句を追加した修正版ストアドプロシージャを下記に記載する。

CREATE OR REPLACE PROCEDURE process_todos_with_cursor()
LANGUAGE plpgsql
AS $$
DECLARE
  todo_cursor REFCURSOR;         -- カーソルの宣言
  todo_record todos%ROWTYPE;     -- todosテーブルの1行を格納する変数
BEGIN
  -- カーソルを開く
  OPEN todo_cursor FOR
    SELECT * FROM todos; -- todosテーブルのすべての行を取得

  -- カーソルから1行ずつ取得して処理
  LOOP
    FETCH todo_cursor INTO todo_record; -- カーソルから次の行を取得
    EXIT WHEN NOT FOUND;               -- データがない場合はループを終了

    -- 各行に対する処理
    RAISE NOTICE 'Processing todo: ID = %, Title = %', todo_record.id, todo_record.title;
  END LOOP;

  -- カーソルを閉じる
  CLOSE todo_cursor;

EXCEPTION
  WHEN OTHERS THEN
    -- 例外が発生した場合にカーソルを閉じる
    IF todo_cursor IS NOT NULL THEN
      BEGIN
        CLOSE todo_cursor;
      EXCEPTION
        WHEN OTHERS THEN
          -- CLOSE中にエラーが発生しても無視
          RAISE WARNING 'Failed to close cursor: %', SQLERRM;
      END;
    END IF;
    -- 例外を再スローする
    RAISE;
END;
$$;

変更点

1. EXCEPTIONセクションの追加
   プロシージャ内で例外が発生した場合に、適切に処理を行うためのセクション。

2. CLOSE処理の安全性を確保
   カーソルがNULLでない場合にのみCLOSEを実行。また、CLOSEそのものが失敗してもプロシージャ全体を壊さないように保護している。

3. 例外の再スロー
   例外を処理した後、RAISEで再スローすることで呼び出し元に通知。必要に応じて再スローを省略することも可能。

動作例

1. 正常処理時
   カーソルを開いてデータを1行ずつ処理し、最後にカーソルを閉じる。

2. 例外発生時
   途中でエラーが発生しても、EXCEPTIONセクション内でカーソルを閉じてから例外を再スローするため、リソースリークが発生しない。

暗黙的カーソルとの比較

カーソルを明示的に使用する場合と暗黙的カーソルを使用する場合の違いを以下に示す

明示的カーソル

DECLARE
  my_cursor REFCURSOR;
  record todos%ROWTYPE;
BEGIN
  OPEN my_cursor FOR SELECT * FROM todos;
  LOOP
    FETCH my_cursor INTO record;
    EXIT WHEN NOT FOUND;
    -- 行の処理
  END LOOP;
  CLOSE my_cursor;
END;

暗黙的カーソル

FOR record IN
  SELECT * FROM todos
LOOP
  -- 行の処理
END LOOP;

暗黙的カーソルはFOR ... INで自動管理されるため、簡潔に記述できる。

PostgreSQLのカーソルについて

PostgreSQLのカーソルは、大規模なデータセットの処理や、行ごとに特定の処理を実行する場面で非常に有用。
ただし、すべてのケースでカーソルを使用する必要はない。
必要に応じて暗黙的カーソルや一括処理との使い分けをすることがポイント。

おまけ: カーソルを引数にもつストアドプロシージャについて

カーソルを引数に持つストアドプロシージャについて、呼び出し元でカーソルをOPENにした状態で渡したときについて調べてみた。
※OPENで渡したときに、どこでCLOSEするのが良いのかなど。

PostgreSQLで、カーソルを引数に持つストアドプロシージャ（PROCEDURE）を使用する場合、呼び出し元でカーソルをOPENにした状態で渡すと、ストアドプロシージャ内でそのカーソルを使用できる。
注意点や動作については以下に記載する。

1. カーソルのスコープ

• PostgreSQLでは、カーソルはセッションレベルで管理されるため、一度OPENしたカーソルは、そのセッション内で有効であり、別のストアドプロシージャや関数でも使用可能となっている。

• 呼び出し元でOPENされたカーソルをストアドプロシージャに渡した場合、ストアドプロシージャ内ではそのカーソルに対してFETCHやCLOSEを実行できる。

2. 呼び出し例

カーソルを引数に持つストアドプロシージャの例

CREATE OR REPLACE PROCEDURE process_cursor(p_cursor REFCURSOR)
LANGUAGE plpgsql
AS $$
DECLARE
  row_record todos%ROWTYPE; -- カーソルで取得する行を格納する変数
BEGIN
  -- 渡されたカーソルから1行ずつ取得して処理
  LOOP
    FETCH p_cursor INTO row_record; -- カーソルから次の行を取得
    EXIT WHEN NOT FOUND;            -- データがない場合はループを終了

    -- 各行に対する処理
    RAISE NOTICE 'Processing ID = %, Title = %', row_record.id, row_record.title;
  END LOOP;

  -- カーソルは呼び出し元で閉じる必要がある
END;
$$;

呼び出し元での利用例

DO $$
DECLARE
  my_cursor REFCURSOR;
BEGIN
  -- カーソルを開く
  OPEN my_cursor FOR SELECT * FROM todos WHERE is_completed = false;

  -- ストアドプロシージャにカーソルを渡す
  CALL process_cursor(my_cursor);

  -- カーソルを閉じる（呼び出し元で管理する場合）
  CLOSE my_cursor;
END;
$$;

3. 注意点

カーソルの管理責任

• ストアドプロシージャが受け取ったカーソルを閉じるか、呼び出し元が閉じるかを明確にする必要がある。
  上記の例では、呼び出し元がカーソルを管理している。

渡されたカーソルが閉じられている場合

• ストアドプロシージャ内でFETCHを試みるとエラーになる（cursor "p_cursor" does not exist）

ストアドプロシージャ内でカーソルを閉じた場合

• 呼び出し元でカーソルを再利用しようとするとエラーになる。たとえば、以下のようなエラーが発生する

ERROR: cursor "my_cursor" does not exist

カーソル名の衝突

• 渡されたカーソル名が他のカーソル名と衝突しないように注意が必要。同じセッション内でカーソル名が重複するとエラーとなる。

トランザクションとの関係

• カーソルは、デフォルトで現在のトランザクションが終了するまで有効。もしストアドプロシージャがトランザクションを終了させる（COMMIT/ROLLBACK）場合、カーソルは無効となる。

4. ベストプラクティスとして

• カーソル管理の明確化: 呼び出し元とストアドプロシージャのどちらがカーソルを閉じるかを明示的に決めておく。

• エラー処理の追加: 渡されたカーソルが正しくOPENされているか、エラーが発生した場合にどうするかを事前に対策する。

• 明示的なカーソル名の指定: カーソル名を明示的に指定しておくと、他のカーソルと衝突するリスクを減らせる。

おまけ2: 引数のカーソルにCLOSEしたものを渡した場合

結論としては、そもそもCLOSEしたものを渡すこと自体に意味がない。
もちろんだが、呼び出し先のストアドプロシージャの方で、OPEN前提の設計になっていればエラーとなり、エラーハンドリングが必要。
そうでない場合は、新しくカーソルを開く必要があるので、引数のカーソルを利用しない形になるため。

1. カーソルをCLOSEして渡す意味がない理由について

• カーソルは閉じられると無効になる
  PostgreSQLでは、一度CLOSEされたカーソルは再利用できない。
  そのため、閉じられたカーソルを渡しても、ストアドプロシージャ内で使用することはできず、新しいカーソルを開く必要がある。

• 呼び出し元で管理したほうが明確
  カーソルのライフサイクル（OPEN, FETCH, CLOSE）は、呼び出し元で完全に管理するほうが、責任の所在が明確となる。
  渡されたカーソルをストアドプロシージャ内で再びOPENするのであれば、そもそもカーソルを渡す必要がない。

2. ストアドプロシージャ内で新たにOPENするケースの問題点

ストアドプロシージャ内で新しいカーソルを開く場合、以下のデメリットがある

• 引数としてのカーソルの必要性が薄れる
  新しいクエリでカーソルを開くなら、引数でカーソルを渡す必要がない。代わりに必要なパラメータ（例：テーブル名や条件）を渡すほうが合理的。

• 可読性や管理が複雑化する
  ストアドプロシージャが複雑になり、カーソル操作の責任が分散されることで、バグが発生しやすくなる。

3. 推奨される設計

カーソルのOPEN/CLOSE管理は呼び出し元で責任を持つのがベストプラクティスとなっている。ストアドプロシージャは以下のように役割を分担する

• 呼び出し元の役割
  必要なカーソルを開き、処理を実行し、最後に閉じる。カーソルを使う処理のライフサイクルを管理する。

• ストアドプロシージャの役割
  渡されたカーソルを使って処理する。必要に応じて新しいカーソルを開く場合でも、引数にカーソルを使うのではなく、必要な情報（クエリ条件など）を受け取る設計にする。

4. 改善案

例として、カーソルを引数にしない場合を下記に記載する。
カーソルを引数にせず、必要な情報を引数として受け取り、ストアドプロシージャ内で新しいカーソルを管理する方法で記述する。

改善例

CREATE OR REPLACE PROCEDURE process_todos_with_query(query text)
LANGUAGE plpgsql
AS $$
DECLARE
  todo_cursor REFCURSOR;
  todo_record todos%ROWTYPE;
BEGIN
  -- カーソルを開く
  OPEN todo_cursor FOR EXECUTE query;

  -- カーソルからデータを取得して処理
  LOOP
    FETCH todo_cursor INTO todo_record;
    EXIT WHEN NOT FOUND;

    RAISE NOTICE 'Processing ID: %, Title: %', todo_record.id, todo_record.title;
  END LOOP;

  -- カーソルを閉じる
  CLOSE todo_cursor;
END;
$$;

呼び出し元

DO $$
BEGIN
  -- クエリを渡してプロシージャを呼び出す
  CALL process_todos_with_query('SELECT * FROM todos WHERE is_completed = false');
END;
$$;

この方法だと以下のメリットができる。

• カーソルの状態を考慮する必要がない
  カーソルのOPENやCLOSEはストアドプロシージャ内で一貫して管理される。

• 役割分担が明確
  呼び出し元はクエリを渡すだけでよく、カーソル操作を気にする必要がない。

• 再利用性が高い
  ストアドプロシージャは柔軟に使える汎用的な形となる。

5. まとめ

• CLOSEされたカーソルを渡すべきでない理由は、そのカーソルが無効であり、ストアドプロシージャで使えないため。
• ストアドプロシージャで新しいカーソルを開くなら、そもそもカーソルを引数にせず、必要なデータや条件を引数にする設計のほうが効率的。
• カーソルのOPEN/CLOSEの責任をどこに持たせるかを明確にし、役割分担を徹底することでコードの可読性と保守性が向上する。

参考

• 43.7. カーソル | PostgreSQL 16.4文書
  https://www.postgresql.jp/document/16/html/plpgsql-cursors.html

• サポートされている PL/pgSQL ステートメント
  https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_PLpgSQL-statements.html

• ChatGPT
  https://chatgpt.com/share/675e7835-9e5c-8001-bc18-855eb0fc370c

おわりに

カーソルについて少し学んだ。
次は型の部分を勉強しておきたい。