Sequences Broken in 7.1.1?

[janice]

Hello,

It seems to me that sequences are broken in 7.1.1.

Code: Select all

dbadmin=> \d test;
                                List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
 public | test  | col1   | int  |    8 |         | t        | t           |
(1 row)

dbadmin=> CREATE SEQUENCE test_seq;
CREATE SEQUENCE

dbadmin=> SELECT session_cache_count, increment_by, minimum, maximum, current_value FROM sequences WHERE sequence_name = 'test_seq';
 session_cache_count | increment_by | minimum |       maximum       | current_value
---------------------+--------------+---------+---------------------+---------------
              250000 |            1 |       1 | 9223372036854775807 |             0
(1 row)

dbadmin=> SELECT COUNT(*) FROM online_sales.online_sales_fact;
  COUNT
---------
 5000000
(1 row)

dbadmin=> INSERT INTO test SELECT test_seq.NEXTVAL FROM online_sales.online_sales_fact;
ERROR 4704:  Sequence exceeded max value

dbadmin=> SELECT session_cache_count, increment_by, minimum, maximum, current_value FROM sequences WHERE sequence_name = 'test_seq';
 session_cache_count | increment_by | minimum |       maximum       | current_value
---------------------+--------------+---------+---------------------+---------------
              250000 |            1 |       1 | 9223372036854775807 |       1500000
(1 row)

Why does it crap out at only 1,500,000 values? Is this a known issue? Or am I missing something here?

[janice]

If I set the CACHE to 10,000, I can insert 5,000,000 records once. But on a second run, the sequence exceeds it's max value...

Code: Select all

dbadmin=> CREATE SEQUENCE test_seq CACHE 10000;
CREATE SEQUENCE

dbadmin=> INSERT INTO test SELECT test_seq.NEXTVAL FROM online_sales.online_sales_fact;
 OUTPUT
---------
 5000000
(1 row)

dbadmin=> INSERT INTO test SELECT test_seq.NEXTVAL FROM online_sales.online_sales_fact;
ERROR 4704:  Sequence exceeded max value

[scutter]

Hi Janice,

This was fixed in the 7.1.1-3 hot fix. Best to pick up the very latest hot fix.

—Sharon

VER-35924 Data load / COPY Bulk loading data using the COPY command sometimes resulted in sequence exceeded max value error. This issue has been resolved.

[janice]

Thank you, Sharon. I just opened a case with Vertica to get the patch. I wonder how that bug got by QA

[janice]

I upgraded to 7.1.1-9 and now the sequence works as expected!

Code: Select all

dbadmin=> SELECT version();
              version
------------------------------------
 Vertica Analytic Database v7.1.1-9
(1 row)

dbadmin=> CREATE TABLE test (col1 INT);
CREATE TABLE

dbadmin=> CREATE SEQUENCE test_seq;
CREATE SEQUENCE

dbadmin=> INSERT INTO test SELECT test_seq.NEXTVAL FROM online_sales.online_sales_fact;
 OUTPUT
---------
 5000000
(1 row)

dbadmin=> INSERT INTO test SELECT test_seq.NEXTVAL FROM online_sales.online_sales_fact;
 OUTPUT
---------
 5000000
(1 row)

dbadmin=> INSERT INTO test SELECT test_seq.NEXTVAL FROM online_sales.online_sales_fact;
 OUTPUT
---------
 5000000
(1 row)

Cool!