Hologres:PostgreSQL SERIAL

データ型の概要

Hologres では、SERIAL または BIGSERIAL データ型を使用して、テーブルに自動インクリメントフィールドを作成できます。 SERIAL データ型は、自動インクリメントフィールドのデータ型が INT4 であることを示します。 BIGSERIAL データ型は、自動インクリメントフィールドのデータ型が INT8 であることを示します。

CREATE TABLE tablename (
    colname serial
);

制限事項

• Hologres では、増分ステップサイズやデフォルト値など、SERIAL および BIGSERIAL データ型の追加パラメーターを指定することはできません。デフォルトでは、増分ステップサイズの値は 1 で、デフォルト値は 1 です。

• Hologres では、データ型が SMALLSERIAL のデータは使用できません。

• データ型が SERIAL および BIGSERIAL のフィールドを持つテーブルを初めて作成するには、スーパーユーザーがデータベースにテーブルを作成する必要があります。たとえば、スーパーユーザーが create table test(a serial); 文を実行すると、他のユーザーはビジネスロジックに基づいて、データ型が SERIAL および BIGSERIAL のフィールドを持つテーブルを作成できます。データ型が SERIAL および BIGSERIAL のフィールドを持つテーブルは、データベースレベルのテーブルです。別のデータベースに切り替える場合、スーパーユーザーは新しいデータベースで上記の文を実行する必要があります。

• Flink を使用してデータを書き込んだり統合したりする場合、Java Database Connectivity (JDBC) モードとデータ統合 (insert into) モードのみが serial および bigserial 型をサポートします。DataHub はこれらの型への書き込みをサポートしていません。

• プライマリキーに基づく insert on conflict の場合、serial 型は厳密に連続した増分を保証しません。厳密な順序が必要な場合は、シーケンスの開始値を手動で設定してください。

• Serial の書き込みは追加のロックオーバーヘッドを発生させ、書き込みパフォーマンスを低下させる可能性があります。これを軽減するには、単一行の挿入ではなく、バッチでデータを書き込みます。SQL 文が Fixed Plan と互換性がある場合は、次の GUC パラメーターを有効にします。これにより、serial フィールドを含む文で Fixed Plan を使用して、書き込みパフォーマンスを向上させることができます。詳細については、「固定プランを使用して SQL 文の実行を高速化する」をご参照ください。

  -- データベースレベルで GUC パラメーターを有効にして、serial 列を含むテーブルの Fixed Plan 書き込みをサポートします。
  alter database <user_db> set hg_experimental_enable_fixed_dispatcher_autofill_series=on;

• serial パラメーターの変更は、Hologres V0.10 以降および restart with に制限されています。

• Serial の書き込みは追加のグローバルロックオーバーヘッドを発生させ、書き込みパフォーマンスに大きな影響を与えます。パフォーマンスが重視されるシナリオでは、この機能の使用に注意してください。

例 1：SQL ステートメントを実行して自動インクリメントフィールドを作成する

// id フィールドと f1 フィールドを含むテーブルを作成します。
create table if not exists test_tb(id serial primary key, f1 text);

// INSERT 文を実行して、f1 フィールドにデータを挿入します。
insert into test_tb(f1) values('1');
insert into test_tb(f1) values('2');
insert into test_tb(f1) values('3');

// test_tb テーブルのデータをクエリし、id フィールドで昇順にソートします。
select * from test_tb order by id asc;

例 2：JDBC 経由で Hologres に接続し、自動インクリメントフィールドを作成する

package test;

import java.sql.*;

public class HoloSerial {

// id フィールドと f1 フィールドを含むテーブルを作成します。
    private static void Init(Connection conn) throws Exception {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("drop table if exists test_tb;");
            stmt.execute("create table if not exists test_tb(id serial primary key, f1 text);");
        }
    }

// f1 フィールドにデータを挿入します。
    private static void TestSerial(Connection conn) throws Exception {
        try (PreparedStatement stmt = conn.prepareStatement("insert into test_tb(f1) values(?)")) {
            for (int i = 0; i < 100; ++i) {
                stmt.setString(1, String.valueOf(i + 1));
                int affected_rows = stmt.executeUpdate();
                System.out.println("affected rows => " + affected_rows);
            }
        }

// test_tb テーブルのデータをクエリし、id フィールドで昇順にソートします。
        try (PreparedStatement stmt = conn.prepareStatement("select * from test_tb order by id asc")) {
            try(ResultSet rs = stmt.executeQuery()) {
                while(rs.next()) {
                    String res = rs.getObject(1).toString() + "\t" + rs.getObject(2).toString();
                    System.out.println(res);
                }
            }
        }
    }

// JDBC 経由で Hologres に接続します。
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver").newInstance();
        String host = "127.0.0.1:13737";
        String db = "postgres";
        String user = "xx";
        String password = "xx";
        String url = "jdbc:postgresql://" + host + "/" + db;
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            Init(conn);

            TestSerial(conn);
        }
    }
}

例 3：SERIAL データ型のパラメーターを変更する

SERIAL データ型のパラメーターを使用してテーブルを作成すると、schema_name.tablename_columnname_seq という名前のシーケンスが自動的に生成されます。 ALTER SEQUENCE ステートメントを実行して、SERIAL データ型のパラメーターを変更できます。手順は以下のとおりです。

1. 次の文を実行して、生成されたシーケンスをクエリします。文中の table_schema、table_name、column_name は、ビジネス要件に基づいて実際の値に置き換える必要があります。次のサンプル文では、例 1 で作成したテーブルを使用します。

   select table_name,column_name,column_default  from information_schema.columns where table_schema='ods' and table_name = 'test_tb' and column_name = 'id';

   次の図にクエリ結果を示します。クエリ結果では、一重引用符 (' ') で囲まれた部分である ods.test_tb_id_seq がシーケンス名を示します。

2. シーケンス名を取得したら、次の文を実行して、restart with オプション句で SERIAL データ型のパラメーターを変更します。

   alter sequence ods.test_tb_id_seq restart with 100

   文中の ods.test_tb_id_seq と数値を、ビジネス要件に基づいて他の値に置き換えることができます。パラメーターを変更した後、テーブルにデータを挿入して結果を確認します。

よくある質問

• 質問 1: serial 型のテーブルを作成する際に「permission denied」エラーが発生するのはなぜですか？

  • エラーメッセージ:

    ERROR: permission denied schema hologres

  • 原因

    スーパーユーザーが serial 型を使用するテーブルを作成していません。

  • 解決策

    スーパーユーザーは、まず serial または bigserial 型を使用するテーブル (例: CREATE TABLE test(a serial);) を作成する必要があります。この操作により、必要なシーケンスオブジェクトが初期化され、他のユーザーがこれらの型でテーブルを作成できるようになります。

• 質問 2: 同じトランザクション内で serial 型のテーブルを削除して再作成するとエラーが発生するのはなぜですか？

  • エラーメッセージ:

    failed: error: duplicate key value violates unique constraint "hg_table_properties_pkey"

  • 原因

    単一のトランザクション内で serial (または bigserial) 型を使用するテーブルを削除してすぐに再作成することはサポートされていません。

  • 解決策

    DELETE と CREATE TABLE の操作を、serial 型のテーブルに対して同じトランザクション内で実行しないでください。これらの操作を別々のトランザクションに分けてください。