实时数仓Hologres兼容Postgres生态,支持在建表时将字段的类型定义为Serial和Bigserial来定义自增的字段。

数据类型简介

Hologres在建表时可以将字段的数据类型定义为SerialBigserial。其中,Serial表示INT4类型的自增字段,Bigserial表示INT8类型的自增字段。

配置Serial类型的自增序列。如下以Serial类型为例,对表格中的colname字段实现自增,Bigserial类型同样适用。
CREATE TABLE tablename (
    colname serial
);
参数说明
参数说明存储大小取值范围
SerialINT4类型的自增字段4字节-2147483648~2147483647
BigserialINT8类型的自增字段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或者数据集成写入场景,仅Flink的JDBC模式和数据集成insert 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的生成方式。具体操作方法如下所示:

  1. 执行如下代码查看生成的Sequence,其中,table_schematable_namecolumn_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名称。
  2. 在获取到Sequence名称之后,您可以执行如下示例语句,对Serial的restart with参数进行修改。
    alter sequence ods.test_tb_id_seq restart with 100
    其中,ods.test_tb_id_seq名称和具体数量您可以按照实际业务内容进行修改。修改完成后,您可以给表中插入数据进行验证。

常见问题

创建带Serial类型的表报错

  • 问题现象
    创建带Serial类型的表时,出现如下报错提示。
    ERROR:permission denied schema hologres
  • 问题原因

    superuser未创建一个Serial类型的示例表。

  • 解决方法

    第一次在数据库创建Serial类型的表时,需要superuser先创建一个示例表,例如superuser执行create table test(a serial); ,其余用户就能按照业务逻辑创建有关Seria和Bigserial类型的表。