自增序列Serial（Beta）的資料類型和樣本 - Hologres

即時數倉Hologres相容Postgres生態，支援在建表時將欄位的類型定義為Serial和Bigserial來定義自增的欄位。

資料類型簡介

Hologres在建表時可以將欄位的資料類型定義為Serial和Bigserial。其中，Serial表示INT4類型的自增欄位，Bigserial表示INT8類型的自增欄位。

配置Serial類型的自增序列。如下以Serial類型為例，對錶格中的colname欄位實現自增，Bigserial類型同樣適用。

CREATE TABLE tablename (
    colname serial
);

參數說明

參數	說明	儲存大小	取值範圍
Serial	INT4類型的自增欄位	4位元組	-2147483648～2147483647
Bigserial	INT8類型的自增欄位	8位元組	-9223372036854775808～9223372036854775807

使用限制

Hologres不能指定Serial和Bigserial的額外參數，包括遞增步長及初始值參數，遞增步長為1，初始值預設設定為1。
Hologres暫不支援Smallserial類型的資料。
首次建立Serial和Bigserial類型的表，需要Superuser在當前DB建立。例如Superuser執行create table test(a serial); ，然後其餘使用者就能按照商務邏輯建立有關Serial和Bigserial類型的表。建立Serial和Bigserial類型的表是DB層級的，如果切換資料庫， Superuser需要再次執行該操作。
在Flink或者Data Integration寫入情境，僅Flink的JDBC模式和Data Integrationinsert into模式支援寫入Serial和Bigserial類型，DataHub暫不支援寫入。
對於基於主鍵的insert on conflict寫入情境，Serial類型不會嚴格保證欄位的連續性增長，如需嚴格保序，請手動設定Serial的起始值。
Serial寫入會有額外鎖的開銷，不建議單條寫入時增加Serial類型，對寫入效能有降低，可以攢批寫入。同時如果SQL符合Fixed Plan特徵，可以開啟如下GUC參數，使Serial欄位可以走Fixed Plan實現更高的寫入效能，詳情請參見Fixed Plan加速SQL執行。
```
--資料庫層級開啟GUC, 支援含有Serial類型列的Fixed Plan寫入
alter database <user_db> set hg_experimental_enable_fixed_dispatcher_autofill_series=on;
```
僅Hologres V0.10及以上版本支援修改Serial參數，且僅支援restart with參數的修改。
Serial寫入會有額外全域鎖的開銷，對寫入效能影響較大，在效能敏感情境謹慎使用。

樣本一：使用SQL語句實現自增序列

如下以Serial類型為例，使用SQL語句實現自增序列，Bigserial類型同樣適用。

--建立表，包含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;

樣本二：使用JDBC串連Hologres，實現自增序列

如下以Serial類型為例，使用JDBC串連Hologres實現自增序列，Bigserial類型同樣適用。

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);
        }
    }
}

樣本三：修改Serial參數

當您在建立表時使用Serial參數時，建立完成會自動產生一個名為schema_name.tablename_columnname_seq的Sequence，您可以通過修改Sequence參數控制Serial的產生方式。具體操作方法如下所示：

執行如下代碼查看產生的Sequence，其中，table_schematable_name和column_name的取值需要修改為實際的名稱。本樣本以樣本一建立的表資訊為例進行說明。
```
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為Sequence名稱。
在擷取到Sequence名稱之後，您可以執行如下樣本語句，對Serial的restart with參數進行修改。
```
alter sequence ods.test_tb_id_seq restart with 100
```
其中，ods.test_tb_id_seq名稱和具體數量您可以按照實際業務內容進行修改。修改完成後，您可以給表中插入資料進行驗證。

常見問題

問題1：建立帶Serial類型的表報錯。
- 問題現象
  建立帶Serial類型的表時，出現如下報錯提示。
```
ERROR：permission denied schema hologres
```
- 問題原因
  superuser未建立一個Serial類型的樣本表。
- 解決方案
  第一次在資料庫建立Serial類型的表時，需要superuser先建立一個樣本表，例如superuser執行create table test(a serial); ，其餘使用者就能按照商務邏輯建立有關Serial和Bigserial類型的表。
問題2：在一個事務裡刪除並重建帶有Serial的表時報錯。
- 問題現象
  在一個事務裡刪除並重建帶有Serial的表時，出現如下報錯提示。
```
failed: error: duplicate key value violates unique constraint "hg_table_properties_pkey"
```
- 問題原因
  不支援在一個事務裡刪除並重建帶有Serial的表。
- 解決方案
  刪除事務（BEGIN;COMMIT）。