Options
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
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
0
Comments
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.
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
);