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

tama9k9tama9k9 Community Edition User
edited May 2022 in General Discussion

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.

Answers

  • Bryan_HBryan_H Vertica Employee Administrator
    edited 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
    );

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file