alter sequence

Charles_S_

Hello,

Given a table and sequence:
CREATE SEQUENCE TEST_ID_SEQ;

CREATE TABLE TEST (
  ID integer default NEXTVAL('TEST_ID_SEQ') ,  
  NAME varchar(50)
);


My objective is to:

#1. 
Programmatically stream +5000 records into the TEST table which has a sequence as its ID using JDBC VerticaCopyStream API:

COPY TEST (c1 VARCHAR FILLER, NAME AS c1) FROM STDIN DIRECT DELIMITER '|' NULL AS 'null'

**Notes:
- ID column not specified in the copy statement
- For the copy statement, Connection auto-commit is set to false
- Stream is executed, closed, and Connection is explicitly committed once streaming has finished



#2. 
Alter sequence, bump its current value to 10,000 (using spring jdbc template execute method):
ALTER SEQUENCE TEST_ID_SEQ RESTART WITH 10000

**Note:
- For this alter statement, Connection auto-commit left unchanged (default is true)


#3. 
Programmatically stream more data into the TEST table, expectation is that TEST_ID_SEQ nextval has been set to 10,000:


COPY TEST (c1 VARCHAR FILLER, NAME AS c1) FROM STDIN DIRECT DELIMITER '|' NULL AS 'null'


The goal is to have a gap in ID values between the first set of data and the second.

I've written the java code to do steps 1-3, however while testing I am not seeing the expected effect in the alter sequence statement in step #2..  The next set of data being streamed in step #3 just continuously takes on the next ID value where step #1 left off.

How can I resolve this issue?  Seems the alter sequence statement between the copy streams had no effect...

Thanks!

Shobhit_Garg

Hi Charles,

You need to define CACHE during creation of sequence.

CACHE - Specifies how many sequence numbers are preallocated and stored in memory for faster access. The default is 250,000 with a minimum value of 1. Specifying a cache value of 1 indicates that only one value can be generated at a time, since no cache is assigned.

Notes:
If you use the CACHE clause when creating a sequence, each session has its own cache on each HP Vertica node.
Sequences that specify a cache size that is insufficient for the number of sequence values could cause a performance degradation.

So you need to define CACHE as 1.

CREATE SEQUENCE TEST_ID_SEQ cache 1;

By default the CACHE is 250,000
I hope this will resolve your issue.

Thanks,
Shobhit