COPY statement generates "ERROR 4704: Sequence exceeded max value" message

Hi David -

Your table has an ID column populated by default by a sequence
(id int not null default nextval('fake_requests_seq')  ).

It's your fake_requests_seq sequence that overruns.

Have you dropped the table several times without dropping /re-initializing the sequence? Or do you indeed have such a humongous data file with so many rows that the sequence overruns from scratch?

If your table is not empty, then run:

select max(id) from fake_requests;

Then, run:

select fake_requests_seq.nextval;

... and decide by yourself how many numbers the sequence has left for you ...

Come to think of it: it also depends on how many ???..gz files you have. I think I remember that the COPY command would run several parallel sessions, one per file. And your sequence cache is at 250,000. Each thread that is instantiated for a *gz. file will begin the sequence at another multiple of 250,000.

Good luck -

marco

• I am dropping and creating the table and the sequence on each attempt.
• I am using the default sequence cache setting of 250K.   However, I see the same error even when using a sequence cache setting of 10K.
• There are 5 files with 100K rows each for a total of 500K rows.
• Even trying to load just one 100K row file causes this error.

dbadmin=> \ds fake_requests_seq 

                                                 List of Sequences

   Schema   |     Sequence      | CurrentValue | IncrementBy | Minimum |       Maximum       | AllowCycle | Comment 

------------+-------------------+--------------+-------------+---------+---------------------+------------+---------

 benchmarks | fake_requests_seq |        10000 |           1 |       1 | 9223372036854775807 | f          |  

dbadmin=> select fake_requests_seq.nextval;

 nextval 

---------

    4346

I can provide copies of the code I am using to reproduce this if you're interested.

David

select clear_all_sequence_cache();

%> pwd
/vertica/dev/v_dev_node0001_catalog/Catalog
%> ls
Checkpoints  config.cat  globalsequence.cat  tiered_catalog.cat  Txnlogs
%> cat globalsequence.cat 
:GlobalSequence
nodeID:45035996273704960
tagLength:12
fence:52
nodeIDMask:18442240474082181120
ceiling:4503599627370495
currentSequenceValue:19405

%> cat sequencegenerator.cat 
:GlobalSequence
nodeID:45035996273704960
tagLength:12
fence:52
nodeIDMask:18442240474082181120
ceiling:4503599627370495
currentSequenceValue:295
.

that's a 2 month gap, not 6 months

that have restrictions on updating freshly released patches

As I mentioned earlier, customers can reach out to support to try to get the latest patch.