Hologres：PostgreSQL SERIAL

Ikhtisar tipe data

Hologres memungkinkan Anda membuat bidang auto-increment dalam tabel menggunakan tipe data SERIAL atau BIGSERIAL. Tipe data SERIAL menunjukkan bahwa tipe data dari bidang auto-increment adalah INT4. Tipe data BIGSERIAL menunjukkan bahwa tipe data dari bidang auto-increment adalah INT8.

CREATE TABLE tablename (
    colname serial
);

Batasan

• Anda tidak dapat menentukan parameter tambahan untuk tipe data SERIAL dan BIGSERIAL di Hologres, termasuk ukuran langkah increment dan nilai default. Secara default, ukuran langkah increment adalah 1 dan nilai awalnya adalah 1.

• Tipe data SMALLSERIAL tidak didukung di Hologres.

• Saat pertama kali membuat tabel dengan bidang bertipe SERIAL atau BIGSERIAL, superuser harus membuat tabel tersebut dalam database. Misalnya, setelah superuser menjalankan pernyataan create table test(a serial);, pengguna lain dapat membuat tabel dengan bidang bertipe SERIAL dan BIGSERIAL sesuai kebutuhan bisnis mereka. Tabel dengan bidang bertipe SERIAL dan BIGSERIAL merupakan tabel tingkat database. Jika Anda beralih ke database lain, superuser harus menjalankan pernyataan tersebut di database baru.

• Saat Flink digunakan untuk penulisan atau integrasi data, hanya mode Java Database Connectivity (JDBC) dan mode integrasi data (insert into) yang mendukung tipe serial dan bigserial. DataHub tidak mendukung penulisan ke tipe-tipe tersebut.

• Untuk operasi insert on conflict berdasarkan kunci primer, tipe serial tidak menjamin increment yang benar-benar berurutan. Jika Anda memerlukan urutan yang ketat, atur secara manual nilai awal dari sequence.

• Penulisan serial menimbulkan overhead kunci tambahan yang dapat mengurangi performa penulisan. Untuk menguranginya, tulis data secara batch alih-alih melakukan insert baris tunggal. Jika pernyataan SQL Anda kompatibel dengan Fixed Plan, aktifkan parameter GUC berikut. Hal ini memungkinkan pernyataan yang melibatkan kolom serial menggunakan Fixed Plan untuk meningkatkan performa penulisan. Untuk informasi selengkapnya, lihat Mempercepat eksekusi pernyataan SQL dengan menggunakan fixed plan.

  -- Aktifkan parameter GUC di tingkat database untuk mendukung penulisan Fixed Plan pada tabel yang berisi kolom serial.
  alter database <user_db> set hg_experimental_enable_fixed_dispatcher_autofill_series=on;

• Modifikasi parameter serial dibatasi pada Hologres V0.10 dan versi selanjutnya serta hanya mendukung klausa opsional restart with.

• Penulisan serial menimbulkan overhead kunci global tambahan yang berdampak signifikan terhadap performa penulisan. Gunakan fitur ini dengan hati-hati dalam skenario yang sensitif terhadap performa.

Contoh 1: Eksekusi pernyataan SQL untuk membuat bidang auto-increment

// Buat tabel yang berisi bidang id dan f1. 
create table if not exists test_tb(id serial primary key, f1 text);

// Masukkan data ke bidang f1 dengan menjalankan pernyataan INSERT. 
insert into test_tb(f1) values('1');
insert into test_tb(f1) values('2');
insert into test_tb(f1) values('3');

// Kueri data dalam tabel test_tb dan urutkan data berdasarkan bidang id secara ascending. 
select * from test_tb order by id asc;

Contoh 2: Hubungkan ke Hologres melalui JDBC dan buat bidang auto-increment

package test;

import java.sql.*;

public class HoloSerial {

// Buat tabel yang berisi bidang id dan 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);");
        }
    }

// Masukkan data ke bidang 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);
            }
        }

// Kueri data dalam tabel test_tb dan urutkan data berdasarkan bidang id secara ascending. 
        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);
                }
            }
        }
    }

// Hubungkan ke Hologres melalui JDBC. 
    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);
        }
    }
}

Contoh 3: Modifikasi parameter tipe data SERIAL

Setelah Anda membuat tabel menggunakan parameter tipe data SERIAL, urutan bernama schema_name.tablename_columnname_seq akan dibuat secara otomatis. Anda dapat mengeksekusi pernyataan ALTER SEQUENCE untuk memodifikasi parameter tipe data SERIAL. Berikut adalah prosedurnya:

1. Jalankan pernyataan berikut untuk mengkueri sequence yang dihasilkan. Ganti table_schema, table_name, dan column_name dengan nilai aktual sesuai kebutuhan bisnis Anda. Contoh berikut menggunakan tabel dari Contoh 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';

   Gambar berikut menunjukkan hasil kueri.Dalam hasil kueri, bagian yang diapit tanda kutip tunggal (' '), ods.test_tb_id_seq, menunjukkan nama sequence.

2. Setelah memperoleh nama sequence, jalankan pernyataan berikut untuk memodifikasi parameter tipe data SERIAL menggunakan klausa opsional restart with.

   alter sequence ods.test_tb_id_seq restart with 100

   Ganti ods.test_tb_id_seq dan angka dalam pernyataan tersebut dengan nilai lain sesuai kebutuhan bisnis Anda. Setelah memodifikasi parameter, masukkan data ke dalam tabel untuk memverifikasi hasilnya.

FAQ

• Pertanyaan 1: Mengapa saya mendapatkan error "permission denied" saat membuat tabel dengan tipe serial?

  • Pesan error:

    ERROR: permission denied schema hologres

  • Penyebab

    Superuser belum membuat tabel yang menggunakan tipe serial.

  • Solusi

    Superuser harus terlebih dahulu membuat tabel yang menggunakan tipe serial atau bigserial (misalnya, CREATE TABLE test(a serial);). Tindakan ini menginisialisasi objek sequence yang diperlukan, sehingga pengguna lain dapat membuat tabel dengan tipe-tipe tersebut.

• Pertanyaan 2: Mengapa saya mendapatkan error saat menghapus dan membuat ulang tabel dengan tipe serial dalam transaksi yang sama?

  • Pesan error:

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

  • Penyebab

    Menghapus dan langsung membuat ulang tabel yang menggunakan tipe serial (atau bigserial) dalam satu transaksi tidak didukung.

  • Solusi

    Jangan lakukan operasi DELETE dan CREATE TABLE untuk tabel bertipe serial dalam transaksi yang sama. Pisahkan operasi tersebut ke dalam transaksi yang berbeda.