Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Problem with Sequence Number (Exceeds Max Value) while loading data

Hi

I have a Vertica 3 node cluster setup. I have created one of my target tables in one of the nodes. Have not done anything else with the table in terms of distributing it across nodes, creating projections etc etc. Now while inserting records into the target table (I have around 24 million rows in my source table) from the source table, for the KEY column of target I am using Sequence Number. During the load, its breaking half way through with the error "ERROR 4704:  Sequence exceeded max value".
I have tried with options
   1. Creating sequence with a smaller cache of around 500
   2. Creating sequence with no cache
   3. Creating sequence with default cache
And in all cases the error comes at some point of time and the behavior is not consistent i.e. it does not always break at the same number.

Every time before loading the target table I drop and recreate the sequence. So there is no confusion of the sequence existing before and value exceeding the max value.
After I got the error I checked the sequences table to check the currval of the sequence and it was way less from the max default value it can go up to. 

After my insert script breaks if I do nextval of the sequence number and it works. So I am not sure where exactly this whole thing is going wrong.

Is there any specific way in which sequence number should be used and I am missing that? Need help here.

Thanks
Prativa

Comments

  • can you please post the insert script that causes this problem?
  • What version of Vertica is this? I ran into a similar issue after upgrading from 7.0.1-1 to 7.1.0-1 (Ubuntu 12.04)

    After upgrade, this works:

    select myseq.nextval;

    But I get the exceeds max value error when I execute things like:

    select myseq.nextval from table1;
    select myseq.nextval from table1 limit 10;
    select myseq.nextval from table1 limit 1;

    This just happened to me yesterday, so I do not know the solution either.
  • Vertica version is "vertica-7.1.0-2.x86_64".

    Script used for insertion is 

    INSERT /*+ direct */ INTO SUBSCRIBER_TEMP_TB(
    RECORD_ID,
    ACTION,
    SUBSCRIBER_KEY,
    BATCH_LOAD_DATE,
    RUN_ID
    )
    (
    SELECT
    STG.RECORD_ID ,
    (
    CASE WHEN SD.SUBSCRIBER_ID IS NULL THEN 'NI'
    ELSE
    (
    CASE WHEN date(STG.SUBSCRIBER_MOD_DATE)!=DATE('2014-11-15') AND STG.SUBSCRIBER_ID=SD.SUBSCRIBER_ID AND SD.SUBSCRIBER_EFF_END_DATE IS NULL THEN 'NC' ELSE
    (CASE WHEN date(STG.SUBSCRIBER_MOD_DATE)!=DATE('2014-11-15') AND (STG.MSISDN!=SD.MSISDN OR STG.IMSI!=SD.IMSI OR STG.SUBSCRIBER_TYPE!=SD.SUBSCRIBER_TYPE OR STG.SUBSCRIBER_STATUS!=SD.SUBSCRIBER_STATUS OR STG.OCS_SUBSCRIBER_KEY!=SD.Ocs_SUBSCRIBER_KEY OR STG.MAIN_PRODUCT_KEY!=SD.MAIN_PRODUCT_KEY) THEN 'SCD' ELSE 'NONSCD' END) END) END) ,
    (CASE WHEN  SD.SUBSCRIBER_ID  IS NULL THEN NEXTVAL('SEQ_SUB_KEY_TEST') ELSE SD.SUBSCRIBER_KEY END),
    STG.SUBSCRIBER_EFF_START_DATE,
    STG.RUN_ID
    FROM SUBSCRIBER_DIMENSION_STG STG LEFT OUTER JOIN (SELECT * FROM SUBSCRIBER_DIMENSION s WHERE S.SUBSCRIBER_EFF_END_DATE IS NULL) AS SD
    ON STG.SUBSCRIBER_ID=SD.SUBSCRIBER_ID
    );

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.