👨🏻‍💼 🔑 🐗 Antipattern PostgreSQL: ID Unik 🔝 🈯️ 🛵

Cukup sering, pengembang perlu membuat beberapa pengenal unik untuk rekaman di tabel PostgreSQL - baik saat memasukkan rekaman maupun saat membacanya.

Meja counter

Tampaknya - apa yang lebih mudah? Kami menyiapkan piring terpisah, di dalamnya - entri dengan meja. Kita perlu mendapatkan pengenal baru - baca dari sana untuk menulis nilai baru - lakukan UPDATE...

Jangan lakukan itu ! Karena besok Anda harus menyelesaikan masalah:

kunci tumpang tindih yang terus-menerus ketika UPDATE

melihat PostgreSQL Antipatterns: melawan gerombolan "mati"
degradasi bertahap dari kecepatan akses ke data tabel penghitung,

lihat PostgreSQL Antipatterns: memperbarui tabel besar saat dimuat
... dan kebutuhan untuk membersihkannya dengan transaksi aktif yang akan mengganggu Anda,

lihat DBA: ketika VACUUM lewat, kami membersihkan tabel secara manual

Objek SEQUENCE

Untuk tugas semacam itu, PostgreSQL menyediakan entitas terpisah - SEQUENCE. Ini nontransaksional, yaitu tidak menyebabkan kunci , tetapi dua transaksi "paralel" pasti akan menerima nilai yang berbeda .

Untuk mendapatkan ID berikutnya dari sebuah urutan, cukup gunakan fungsinya nextval:

SELECT nextval('seq_name'::regclass);

Terkadang Anda perlu mendapatkan beberapa ID sekaligus - untuk rekaman streaming melalui COPY, misalnya. Menggunakan untuk ini pada setval(currval() + N)dasarnya salah ! Untuk alasan sederhana bahwa antara panggilan ke fungsi "inner" ( currval) dan "outer" ( setval), transaksi bersamaan dapat mengubah nilai urutan saat ini. Cara yang benar adalah memanggil nextvalbeberapa kali yang diperlukan:

SELECT
  nextval('seq_name'::regclass)
FROM
  generate_series(1, N);

Pseudo serial

Sangat tidak nyaman bekerja dengan urutan dalam mode "manual". Tapi tugas tipikal kita adalah memastikan penyisipan record baru dengan sequence-ID baru! Khusus untuk tujuan ini, PostgreSQL ditemukan serial, yang, saat membuat tabel, "mengembang" menjadi seperti . Tidak perlu mengingat nama urutan yang dibuat secara otomatis yang ditautkan ke bidang, ada fungsi untuk ini . Fungsi yang sama dapat digunakan dalam substitusi Anda sendiri - misalnya, jika ada kebutuhan untuk membuat urutan yang sama untuk beberapa tabel sekaligus. Namun, karena bekerja dengan urutan adalah nontransaksional, jika pengenal darinya diterima oleh transaksi rollback, maka urutan ID dalam entri tabel yang disimpan akan "bocor"id integer NOT NULL DEFAULT nextval('tbl_id_seq')

pg_get_serial_sequence(table_name, column_name)DEFAULT

...

Kolom DIHASILKAN

Dimulai dengan PostgreSQL 10 , dimungkinkan untuk mendeklarasikan kolom identitas ( GENERATED AS IDENTITY) yang sesuai dengan standar SQL: 2003. Dalam varian, GENERATED BY DEFAULTperilakunya setara serial, tetapi dengan GENERATED ALWAYSsegala sesuatu yang lebih menarik:

CREATE TABLE tbl(
  id
    integer
      GENERATED ALWAYS AS IDENTITY
);

INSERT INTO tbl(id) VALUES(DEFAULT);
--   :     10 .
INSERT INTO tbl(id) VALUES(1);
-- ERROR:  cannot insert into column "id"
-- DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT:  Use OVERRIDING SYSTEM VALUE to override.

Ya, untuk memasukkan nilai tertentu "di seberang" kolom seperti itu, Anda harus melakukan upaya ekstra dengan OVERRIDING SYSTEM VALUE:

INSERT INTO tbl(id) OVERRIDING SYSTEM VALUE VALUES(1);
--   :     11 .

Perhatikan bahwa sekarang kita memiliki dua nilai identik dalam tabel id = 1- yaitu, DIHASILKAN tidak memberlakukan kondisi dan indeks UNIK tambahan , tetapi juga murni deklarasi serial.

Secara umum, pada versi PostgreSQL modern, penggunaan serial sudah tidak digunakan lagi, dengan pengganti yang lebih disukai untuk GENERATED. Kecuali, mungkin, situasi dukungan untuk aplikasi lintas-versi yang bekerja dengan PG di bawah 10.

UUID yang dihasilkan

Semuanya baik-baik saja selama Anda bekerja dalam satu contoh database. Tetapi bila ada beberapa di antaranya, tidak ada cara yang memadai untuk menyinkronkan urutan (namun, ini tidak mencegah Anda dari "tidak cukup" menyinkronkannya , jika Anda benar-benar menginginkannya). Di sinilah jenis UUIDdan fungsi untuk menghasilkan nilai untuk itu datang untuk menyelamatkan . Saya biasanya menggunakannya uuid_generate_v4()sebagai yang paling "kasual".

Bidang sistem tersembunyi

tableoid / ctid

Kadang-kadang, saat mengambil record dari tabel, Anda perlu mengatasi record "fisik" tertentu, atau mencari tahu dari bagian tertentu mana record tertentu diperoleh saat mengakses tabel "induk" menggunakan pewarisan .

Dalam kasus ini, bidang sistem tersembunyi yang ada di setiap rekaman akan membantu kami :

tableoidmenyimpan oid-id dari tabel - yaitu, tableoid::regclass::textmemberikan nama bagian tabel tertentu
ctid - Alamat "fisik" rekaman dalam format (<>,<>)

Misalnya, ctiddapat digunakan untuk operasi dengan tabel tanpa kunci utama , tetapi tableoiduntuk implementasi jenis kunci asing tertentu.

oid

Hingga 11 PostgreSQL dimungkinkan untuk dideklarasikan saat Anda membuat tabel atribut WITH OIDS:

CREATE TABLE tbl(id serial) WITH OIDS;

Setiap entri dalam tabel ini mendapat bidang tersembunyi tambahan oiddengan nilai unik global dalam database - seperti yang diatur untuk tabel sistem seperti pg_class, pg_namespace...

Saat Anda memasukkan catatan dalam tabel, nilai yang dihasilkan segera dikembalikan ke hasil kueri:

INSERT INTO tbl(id) VALUES(DEFAULT);

  :   OID 16400   11 .

Bidang seperti itu tidak terlihat untuk kueri tabel "normal":

SELECT * FROM tbl;

id
--
 1

Itu, seperti bidang sistem lainnya, harus diminta secara eksplisit:

SELECT tableoid, ctid, xmin, xmax, cmin, cmax, oid, * FROM tbl;

tableoid | ctid  | xmin | xmax | cmin | cmax | oid   | id
---------------------------------------------------------
   16596 | (0,1) |  572 |    0 |    0 |    0 | 16400 |  1

Benar, nilai oidadalah hanya 32 bit , sehingga sangat mudah untuk mendapatkan overflow, setelah oidbahkan tidak akan mungkin untuk membuat setiap meja (perlu yang baru !). Oleh karena itu, sejak PostgreSQL 12, itu WITH OIDStidak lagi didukung .

Time clock_timestamp "wajar"

Terkadang, ketika kueri atau prosedur berjalan untuk waktu yang lama, Anda ingin mengikat waktu "saat ini" ke rekaman. Kegagalan menunggu siapa saja yang mencoba menggunakan suatu fungsi untuk melakukan ini now()- ini akan mengembalikan nilai yang sama di seluruh transaksi .

Untuk mendapatkan waktu "sekarang", ada fungsi clock_timestamp()(dan sekelompok saudara lainnya). Perbedaan antara perilaku fungsi-fungsi ini dapat dilihat pada contoh kueri sederhana:

SELECT
  now()
, clock_timestamp()
FROM
  generate_series(1, 4);

              now              |        clock_timestamp
-------------------------------+-------------------------------
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626758+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626763+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626764+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626765+03

Antipattern PostgreSQL: ID Unik