src/test/regress/expected/sequence_gp.out

--
-- Test Overflow with NO CYCLE
--
CREATE TABLE tmp_table (a int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO tmp_table VALUES (0),(1),(2),(3);
-- Test execution of nextval on master with CACHE 1
CREATE SEQUENCE tmp_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2 START 1 CACHE 1 NO CYCLE;
SELECT nextval('tmp_seq');
 nextval 
---------
       1
(1 row)

SELECT nextval('tmp_seq');
 nextval 
---------
       2
(1 row)

-- Fails because it reaches MAXVALUE
SELECT nextval('tmp_seq');
ERROR:  nextval: reached maximum value of sequence "tmp_seq" (2)
DROP SEQUENCE tmp_seq;
-- Test that ORCA and Planner return the same results although they produce different execution plans.
CREATE SEQUENCE tmp_seq INCREMENT 1 MINVALUE 1 MAXVALUE 4 START 1 CACHE 1 NO CYCLE;
SELECT val from (SELECT nextval('tmp_seq'), a as val FROM tmp_table ORDER BY a) as val ORDER BY val;
 val 
-----
   0
   1
   2
   3
(4 rows)

DROP SEQUENCE tmp_seq;
-- Test execution of nextval on master with CACHE > 1
CREATE SEQUENCE tmp_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2 START 1 CACHE 20 NO CYCLE;
SELECT nextval('tmp_seq');
 nextval 
---------
       1
(1 row)

SELECT nextval('tmp_seq');
 nextval 
---------
       2
(1 row)

-- Fails because it reaches MAXVALUE
SELECT nextval('tmp_seq');
ERROR:  nextval: reached maximum value of sequence "tmp_seq" (2)
DROP SEQUENCE tmp_seq;
-- Test execution of nextval on master (when optimizer = on) and segments (when optimizer=off) with CACHE > 1
CREATE TABLE tmp_table_cache (a int, b int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
-- forcing the values to go on one segment only, to predict the sequence values.
INSERT INTO tmp_table_cache VALUES (1,0),(1,1),(1,2),(1,3);
CREATE SEQUENCE tmp_seq INCREMENT 1 MINVALUE 1 MAXVALUE 4 START 1 CACHE 3 NO CYCLE;
SELECT nextval('tmp_seq'), * FROM tmp_table_cache;
 nextval | a | b 
---------+---+---
       1 | 1 | 0
       2 | 1 | 1
       3 | 1 | 2
       4 | 1 | 3
(4 rows)

SELECT * from tmp_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
          4 |       0 | t
(1 row)

SELECT seqrelid::regclass, seqtypid::regtype, seqstart, seqincrement, seqmax, seqmin, seqcache, seqcycle FROM pg_sequence WHERE seqrelid='tmp_seq'::regclass;
 seqrelid | seqtypid | seqstart | seqincrement | seqmax | seqmin | seqcache | seqcycle 
----------+----------+----------+--------------+--------+--------+----------+----------
 tmp_seq  | bigint   |        1 |            1 |      4 |      1 |        3 | f
(1 row)

DROP SEQUENCE tmp_seq;
CREATE SEQUENCE tmp_seq INCREMENT 1 MINVALUE 1 MAXVALUE 3 START 1 CACHE 3 NO CYCLE;
SELECT nextval('tmp_seq'), a FROM tmp_table ORDER BY a;
ERROR:  nextval: reached maximum value of sequence "tmp_seq" (3)
DROP SEQUENCE tmp_seq;
DROP TABLE tmp_table;
CREATE TABLE mytable (size INTEGER, gid bigserial NOT NULL);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'size' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
ALTER SEQUENCE mytable_gid_seq RESTART WITH 9223372036854775805;
/* Consume rest of serial sequence column values */
INSERT INTO mytable VALUES (1), (2), (3);
SELECT gid FROM mytable;
         gid         
---------------------
 9223372036854775805
 9223372036854775806
 9223372036854775807
(3 rows)

INSERT INTO mytable VALUES(4);
ERROR:  nextval: reached maximum value of sequence "mytable_gid_seq" (9223372036854775807)
SELECT gid FROM mytable;
         gid         
---------------------
 9223372036854775805
 9223372036854775806
 9223372036854775807
(3 rows)

INSERT INTO mytable SELECT * FROM generate_series(1, 10)i;
ERROR:  nextval: reached maximum value of sequence "mytable_gid_seq" (9223372036854775807)
SELECT gid FROM mytable;
         gid         
---------------------
 9223372036854775805
 9223372036854775806
 9223372036854775807
(3 rows)

SELECT * FROM mytable_gid_seq;
     last_value      | log_cnt | is_called 
---------------------+---------+-----------
 9223372036854775807 |       0 | t
(1 row)

SELECT seqrelid::regclass, seqtypid::regtype, seqstart, seqincrement, seqmax, seqmin, seqcache, seqcycle FROM pg_sequence WHERE seqrelid='mytable_gid_seq'::regclass;
    seqrelid     | seqtypid | seqstart | seqincrement |       seqmax        | seqmin | seqcache | seqcycle 
-----------------+----------+----------+--------------+---------------------+--------+----------+----------
 mytable_gid_seq | bigint   |        1 |            1 | 9223372036854775807 |      1 |        1 | f
(1 row)

CREATE TABLE out_of_range_insert (size INTEGER, gid serial NOT NULL);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'size' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
ALTER SEQUENCE out_of_range_insert_gid_seq RESTART WITH 2147483646;
INSERT INTO out_of_range_insert VALUES (1), (2), (3);
ERROR:  nextval: reached maximum value of sequence "out_of_range_insert_gid_seq" (2147483647)
SELECT * FROM out_of_range_insert ORDER BY gid;
 size | gid 
------+-----
(0 rows)

SELECT * FROM out_of_range_insert_gid_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
 2147483647 |       0 | t
(1 row)

SELECT seqrelid::regclass, seqtypid::regtype, seqstart, seqincrement, seqmax, seqmin, seqcache, seqcycle FROM pg_sequence WHERE seqrelid='out_of_range_insert_gid_seq'::regclass;
          seqrelid           | seqtypid | seqstart | seqincrement |   seqmax   | seqmin | seqcache | seqcycle 
-----------------------------+----------+----------+--------------+------------+--------+----------+----------
 out_of_range_insert_gid_seq | integer  |        1 |            1 | 2147483647 |      1 |        1 | f
(1 row)

CREATE SEQUENCE descending_sequence INCREMENT -1 MINVALUE 1 MAXVALUE 9223372036854775806 START 9223372036854775806 CACHE 1;
SELECT nextval('descending_sequence');
       nextval       
---------------------
 9223372036854775806
(1 row)

CREATE TABLE descending_sequence_insert(a bigint, b bigint);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO descending_sequence_insert SELECT i, nextval('descending_sequence') FROM generate_series(1, 10)i;
SELECT * FROM descending_sequence_insert ORDER BY b DESC;
 a  |          b          
----+---------------------
  1 | 9223372036854775805
  2 | 9223372036854775804
  3 | 9223372036854775803
  4 | 9223372036854775802
  5 | 9223372036854775801
  6 | 9223372036854775800
  7 | 9223372036854775799
  8 | 9223372036854775798
  9 | 9223372036854775797
 10 | 9223372036854775796
(10 rows)

SELECT * FROM descending_sequence;
     last_value      | log_cnt | is_called 
---------------------+---------+-----------
 9223372036854775796 |      22 | t
(1 row)

SELECT seqrelid::regclass, seqtypid::regtype, seqstart, seqincrement, seqmax, seqmin, seqcache, seqcycle FROM pg_sequence WHERE seqrelid='descending_sequence'::regclass;
      seqrelid       | seqtypid |      seqstart       | seqincrement |       seqmax        | seqmin | seqcache | seqcycle 
---------------------+----------+---------------------+--------------+---------------------+--------+----------+----------
 descending_sequence | bigint   | 9223372036854775806 |           -1 | 9223372036854775806 |      1 |        1 | f
(1 row)

-- Test that we don't produce duplicate sequence values
DROP SEQUENCE IF EXISTS check_no_duplicates;
NOTICE:  sequence "check_no_duplicates" does not exist, skipping
CREATE SEQUENCE check_no_duplicates;
SELECT nextval('check_no_duplicates') FROM gp_dist_random('gp_id');
 nextval 
---------
       1
      21
      41
(3 rows)

SELECT nextval('check_no_duplicates');
 nextval 
---------
      61
(1 row)

SELECT nextval('check_no_duplicates') FROM gp_dist_random('gp_id');
 nextval 
---------
       2
      22
      42
(3 rows)