Each session has its own sequence cache on each Vertica node

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1775
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Each session has its own sequence cache on each Vertica node

Post by JimKnicely » Wed Oct 16, 2013 3:50 pm

The Vertica documentation says this about sequences: "If you use the CACHE clause when creating a sequence, each session has its own cache on each Vertica node."

It's neat see see that by default, on a three node cluster, initially node 1 will have a cache of sequences ranging from 1-250,000, node 2 will have a cache of sequences ranging from 250,001 – 500,000 and node 3 will have a cache of sequences ranging from 500,001 to 750,000!

Example:

On Node 1:

Code: Select all

dbadmin=> create table jim (node varchar(10), seq int);
CREATE TABLE

dbadmin=> create sequence jim_seq;
CREATE SEQUENCE

dbadmin=> insert into jim select 'node1', jim_seq.nextval;
 OUTPUT 
--------
      1
(1 row)

dbadmin=> select * from jim;
 node  | seq 
-------+-----
 node1 |   1
(1 row)

dbadmin=> commit;
COMMIT
On Node 2:

Code: Select all

dbadmin=> insert into jim select 'node2', jim_seq.nextval;
 OUTPUT 
--------
      1
(1 row)

dbadmin=> select * from jim;
 node  |  seq   
-------+--------
 node2 | 250001
 node1 |      1
(2 rows)

dbadmin=> commit;
COMMIT
On Node 3:

Code: Select all

dbadmin=> insert into jim select 'node3', jim_seq.nextval;
 OUTPUT 
--------
      1
(1 row)

dbadmin=> select * from jim;
 node  |  seq   
-------+--------
 node2 | 250001
 node3 | 500001
 node1 |      1
(3 rows)

dbadmin=> commit;
COMMIT
Now if I log out of my session on node 1 and then log back in, the next sequence value is 750,001!

Code: Select all

dbadmin=> insert into jim select 'node1', jim_seq.nextval;
 OUTPUT 
--------
      1
(1 row)

dbadmin=> select * from jim;
 node  |  seq   
-------+--------
 node2 | 250001
 node3 | 500001
 node1 |      1
 node1 | 750001
(4 rows)

dbadmin=> commit;
COMMIT
And if I log out of my session on node 2 and then log back in, the next sequence value is 1,000,001!

Code: Select all

dbadmin=> insert into jim select 'node2', jim_seq.nextval;
 OUTPUT 
--------
      1
(1 row)

dbadmin=> select * from jim;
 node  |   seq   
-------+---------
 node2 |  250001
 node3 |  500001
 node1 |       1
 node1 |  750001
 node2 | 1000001
(5 rows)

dbadmin=> commit;
COMMIT
Finally if I log out of my session on node 3 and then log back in, the next sequence value is 1,250,001!

Code: Select all

dbadmin=> insert into jim select 'node3', jim_seq.nextval;
 OUTPUT 
--------
      1
(1 row)

dbadmin=> select * from jim;
 node  |   seq   
-------+---------
 node1 |       1
 node1 |  750001
 node3 | 1250001
 node2 |  250001
 node3 |  500001
 node2 | 1000001
(6 rows)

dbadmin=> commit;
COMMIT
I think Vertica expects us to be inserting at least 250,000 rows per session, which makes sense for large fact tables :D
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”