can I flush the cache of a sequence without logout a session

Hi Vincent

Cached sequences will always start with a multiple of the cache size on each node. So, in your case, it will start with 0 on one node, with 1000 on a second node, and with 2000 on a third node. This makes sure that the nodes don't need to communicate with each other every time a <sequence>.nextval is generated. In your case, steps 1 and 2 ran on nodes where the sequence started at a higher multiple of 1000 than in step 3.

This is how cached sequences work in Vertica.

If you absolutely need a higher number for that column when the row was inserted later, you had better use a different way to populate it.

This could be a relatively performant way: use the microseconds elapsed between an anchor timestamp (start of this year in my example), and the current timestamp:

SQL>select 
 timestampdiff(microsecond
              ,'2014-01-01 00:00:00'::timestamp
              ,current_timestamp);
timestampdiff
  25,873,495,176,734

Sequences, even if they are called like that, do not generate clear predecessor/successor sequences, in Vertica, if they are cached. They are a means to generate a value that is guaranteed to be unique, but they will never be helpful to generate any semantics in what they generate (in your case, which row was generated later than another).

select max(ID) from T1

ROW_NUMBER()

Happy playing and good luck -

marco