Why AUTO_INCREMENT in Vertica doesn't work correctly and return something wrong?

tama9k9 · May 2022

Hi guys,

Currently I have a task which needs use auto_increment and I apply this for my task like the code below:
CREATE TABLE ip_data_newpartition (
ID IDENTITY(1,1),
"key" VARCHAR(512),
"ip" VARCHAR(512),
"_sys_updated_at" TIMESTAMP ENCODING RLE
);
I have followed this document
but it doesn't make sense. When I select the table ip_data_newpartition, 84 first records are numbered correctly but after the record with ID = 84, the table has some problem. It starts from the ID = '250001' instead of starts from the ID = 85. Moreover, after the record with ID = 250074, the table starts from the ID = 500001 instead of using the ID = 250075. I still don't understand why this problem can happen and how can I fix this? I'm sure that all the records are unique.

Please help me to fix this. Thank you so much for your help.
Hope your guys have a good day.

Bryan_H · May 2022

Each node creates a default cache of unique values so it's possible that an INSERT on node2 will return a value from a different unique range than node1. To avoid this, disable the sequence cache by setting the third optional parameter of IDENTITY to 0:
CREATE TABLE ip_data_newpartition (
ID IDENTITY(1,1,0),
"key" VARCHAR(512),
"ip" VARCHAR(512),
"_sys_updated_at" TIMESTAMP ENCODING RLE
);

We're Moving!

Create My New Community Account Now

Why AUTO_INCREMENT in Vertica doesn't work correctly and return something wrong?

Answers

Leave a Comment