【PostgreSQL】FetchSizeを設定してもフェッチ処理が動作しない

今回は、JDBC（PostgreSQL）を使用して検索するときに「FetchSize」を設定してもフェッチ処理が動作しない問題についての原因と解決方法を紹介していきます。

スポンサーリンク

検証環境

検証に使用した環境／ライブラリを次に記載します。

  • Java
    • バージョン：11
  • PostgreSQL（JDBC）
    • バージョン：9.4.1212
  • PostgreSQL（サーバ）
    • バージョン：12.4

原因

JDBC（PostgreSQL）を使用してフェッチ処理を実行するためには、「FetchSize」の設定と次の条件をすべて満たす必要があります。内容は公式サイトからの引用になります。

  1. The connection to the server must be using the V3 protocol. This is the default for (and is only supported by) server versions 7.4 and later.
  2. The Connection must not be in autocommit mode. The backend closes cursors at the end of transactions, so in autocommit mode the backend will have closed the cursor before anything can be fetched from it.
  3. The Statement must be created with a ResultSet type of ResultSet.TYPE_FORWARD_ONLY. This is the default, so no code will need to be rewritten to take advantage of this, but it also means that you cannot scroll backwards or otherwise jump around in the ResultSet.
  4. The query given must be a single statement, not multiple statements strung together with semicolons.

引用元：https://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor

条件をまとめると次のようになります。

  1. 接続するときのプロトコルは、「v3」にする。
  2. 自動コミットは、「無効」にする。
  3. 結果セットは、「ResultSet.TYPE_FORWARD_ONLY」にする。
  4. 実行するSQLは、「単一クエリ」にする。

解決方法

プロトコルバージョンを設定する

接続するときのプロトコルバージョンは、「v3」を設定します。

JDBCのバージョンが「7.4」以降の場合は、デフォルトの設定は「v3」となるため設定は不要になります。

プロトコルバージョンを設定するプログラム例は、次のようになります。

...
Properties properties = new Properties();
properties.setProperty("user", "user");
properties.setProperty("password", "password");
properties.setProperty("protocolVersion", "3");
Connection connection =
 DriverManager.getConnection("jdbc:postgresql://localhost:5432/", properties);
...

「Properties」に「protocolVersion = 2」を設定した情報を「DriverManager.getConnection」メソッドに設定している場合は、プロトコルバージョンが「v2」になることがあり動作しない可能性があります。

自動コミットモードを設定する

接続するときの自動コミットモードは、「無効」を設定します。

取得した「Connection」の「setAutoCommit」メソッドに「false」に設定することで無効にすることができます。デフォルトの設定は「true」となるため設定が必要になります。

自動コミットモードを設定するプログラム例は、次のようになります。

...
Properties properties = new Properties();
properties.setProperty("user", "user");
properties.setProperty("password", "password");
Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/", properties);
connection.setAutoCommit(false);
...

「Connection」の「setAutoCommit」メソッドに「false」を設定します。

フレームワークを使用している場合は、トランザクションを開始することで「setAutoCommit」メソッドに「false」が設定されるようになることがあります。フレームワークの機能でトランザクションを制御している場合は、個別に自動コミットモードを変更するのは難しくなることがあります。

結果セットの型を設定する

結果セット（ResultSet）を作成するときの型は、「ResultSet.TYPE_FORWARD_ONLY」を設定します。

デフォルトの設定は「ResultSet.TYPE_FORWARD_ONLY」となるため設定は不要になります。

作成するときの型を設定するプログラム例は、次のようになります。

...
Properties properties = new Properties();
properties.setProperty("user", "user");
properties.setProperty("password", "password");
Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/", properties);
Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
...

「Connection」の「createStatement」メソッドの1番目の引数が型を設定する値になります。

Connection (Java SE 11 & JDK 11 )
docs.oracle.com

実行するクエリ（SQL）を設定する

実行するSQLは「単一クエリ」を設定します。単一クエリは、実行するSQLに「;（セミコロン）」を指定して複数SQLとならないようにする必要があります。

単一クエリ、複数クエリについてのSQL例は、次のようになります。

単一クエリ

単一クエリのSQL例は、次のようになります。

SELECT * FROM example

複数クエリ

複数クエリのSQL例は、次のようになります。

SELECT * FROM example;SELECT * FROM example

「;（セミコロン）」で区切るようなSQLは設定できないことになります。

再現方法

再現方法は、大量データを取得してメモリ不足（OutOfMemoryError）の発生有無で確認することができます。この条件でフェッチ処理が動作しているかどうかを確認できる理由は、次の動作になることが考えられるためです。

  1. フェッチ処理が動作している場合は、複数回に分けて取得する動作になるためメモリ使用量は少なくなります（メモリ不足にならない）。
  2. フェッチ処理が動作していない場合は、まとめて取得する動作になるためメモリ使用量は多くなります（メモリ不足になる）。

データ準備

データ準備のため大量のデータを登録しておきます。検証するために使用したテーブル、データ登録するためのSQLは、次のようになります。

-- テーブル登録
CREATE TABLE example (
  id int PRIMARY KEY,
  name varchar ( 128 ) UNIQUE NOT NULL
);
-- データ登録
INSERT INTO example (id, name) SELECT i, format('name_%s', i) FROM generate_series(1,100000) as i;

プログラム例（フェッチ処理動作しない）

フェッチ処理が動作しないプログラム例は、次のようになります。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {
  public static void main(String[] args) {
    try {
      Connection connection =
          DriverManager.getConnection("jdbc:postgresql://localhost:5432/", "username", "password");
      Statement statement = connection.createStatement();
      statement.setFetchSize(100);
      ResultSet rs = statement.executeQuery("select * from example");
      while (rs.next()) {
        // 取得した情報により処理を実施します。
      }
      rs.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

「Statement」の「setFetchSize」メソッドを使用して「FetchSize」を設定しています。

プログラム例でのフェッチ処理を実行するための必要条件は、次のようになります。

  1. プロトコルバージョンは、デフォルトの「v3」になります。
  2. 自動コミットモードは、デフォルトの「有効」になります。
  3. 結果セットの型は、デフォルトの「ResultSet.TYPE_FORWARD_ONLY」になります。
  4. 実行するクエリは、「単一クエリ」になります。

「自動コミットモード」が「有効」のためフェッチ処理の条件を満たさない動作になります。

再現条件

作成したプログラムは、次の条件で実行します。

  1. 対象テーブルに10万件データを登録します。
  2. 実行するときのVM引数に「-XX:MaxHeapSize=8m」を設定します。最大ヒープサイズを少なくしてメモリ不足が発生しやすい状況にします。

プログラム例の処理を実行するとメモリ不足（OutOfMemoryError）のエラーが発生することを確認することができると思います。

プログラム例（フェッチ処理動作する）

フェッチ処理が動作するプログラム例は、次のようになります。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {
  public static void main(String[] args) {
    try {
      Connection connection =
          DriverManager.getConnection("jdbc:postgresql://localhost:5432/", "username", "password");
      connection.setAutoCommit(false);
      Statement statement = connection.createStatement();
      statement.setFetchSize(100);
      ResultSet rs = statement.executeQuery("select * from example");
      while (rs.next()) {
        // 取得した情報により処理を実施します。
      }
      rs.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

「Connection」の「setAutoCommit」メソッドに「false」を指定します。自動コミットモードが「無効」にフェッチ処理を実行する条件をすべて満たすことができました。

修正したプログラムを実行するとメモリ不足（OutOfMemoryError）のエラーが発生しないことを確認することができると思います。

まとめ

JDBC（PostgreSQL）で「FetchSize」を設定しても動作しない問題と解決方法を紹介しました。また、実際にフェッチ処理が動作していることを確認するための再現方法を紹介しました。

参考資料

「PostgreSQL」の取得と結果処理について

https://jdbc.postgresql.org/documentation/query/
jdbc.postgresql.org

「PostgreSQL」の接続パラメータについて

https://jdbc.postgresql.org/documentation/use/
jdbc.postgresql.org