Skip to main content

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

  • November 21, 2014
  • 3 replies
  • 24 views
Prativa_Nayak
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

    3 replies

    Sajan_Sangraula
    Forum|alt.badge.img+1
    • Sajan_Sangraula
    • Participating Frequently
    • November 21, 2014
    can you please post the insert script that causes this problem?
    David_Rydzewski
    • David_Rydzewski
    • Participating Frequently
    • November 21, 2014
    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.
    Prativa_Nayak
    • Prativa_Nayak
    • Author
    • New Participant
    • November 21, 2014
    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
    );
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement