--

-- CREATE SEQUENCE

--

-- various error cases

CREATE UNLOGGED SEQUENCE sequence_testx;

ERROR: unlogged sequences are not supported

CREATE SEQUENCE sequence_testx INCREMENT BY 0;

ERROR: INCREMENT must not be zero

CREATE SEQUENCE sequence_testx INCREMENT BY -1 MINVALUE 20;

ERROR: MINVALUE (20) must be less than MAXVALUE (-1)

CREATE SEQUENCE sequence_testx INCREMENT BY 1 MAXVALUE -20;

ERROR: MINVALUE (1) must be less than MAXVALUE (-20)

CREATE SEQUENCE sequence_testx INCREMENT BY -1 START 10;

ERROR: START value (10) cannot be greater than MAXVALUE (-1)

CREATE SEQUENCE sequence_testx INCREMENT BY 1 START -10;

ERROR: START value (-10) cannot be less than MINVALUE (1)

CREATE SEQUENCE sequence_testx CACHE 0;

ERROR: CACHE (0) must be greater than zero

-- OWNED BY errors

CREATE SEQUENCE sequence_testx OWNED BY nobody; -- nonsense word

ERROR: invalid OWNED BY option

HINT: Specify OWNED BY table.column or OWNED BY NONE.

CREATE SEQUENCE sequence_testx OWNED BY pg_tables.tablename; -- not a table

ERROR: referenced relation "pg_tables" is not a table or foreign table

CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname; -- not same schema

ERROR: sequence must be in same schema as table it is linked to

CREATE TABLE sequence_test_table (a int);

CREATE SEQUENCE sequence_testx OWNED BY sequence_test_table.b; -- wrong column

ERROR: column "b" of relation "sequence_test_table" does not exist

DROP TABLE sequence_test_table;

---

--- test creation of SERIAL column

---

-- Alter sequence

--

ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24

INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;

INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;

NOTICE: relation "sequence_test2" does not exist, skipping

ALTER SEQUENCE pg_class CYCLE; -- error, not a sequence

ERROR: "pg_class" is not a sequence

CREATE SEQUENCE sequence_test2 START WITH 32;

CREATE SEQUENCE sequence_test4 INCREMENT BY -1;

SELECT nextval('sequence_test2');

nextval

---------

32

(1 row)

SELECT nextval('sequence_test4');

nextval

---------

-1

(1 row)

ALTER SEQUENCE sequence_test2 RESTART;

SELECT nextval('sequence_test2');

nextval

---------

32

(1 row)

ALTER SEQUENCE sequence_test2 RESTART WITH 0; -- error

ERROR: RESTART value (0) cannot be less than MINVALUE (1)

ALTER SEQUENCE sequence_test4 RESTART WITH 40; -- error

ERROR: RESTART value (40) cannot be greater than MAXVALUE (-1)

-- test CYCLE and NO CYCLE

ALTER SEQUENCE sequence_test2 RESTART WITH 24

INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;

INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;

SELECT nextval('sequence_test2');

nextval

---------

36

(1 row)

SELECT nextval('sequence_test2');

SELECT nextval('sequence_test2'); -- cycled

nextval

---------

5

(1 row)

ALTER SEQUENCE sequence_test2 RESTART;

ALTER SEQUENCE sequence_test2 RESTART WITH 24

NO CYCLE;

SELECT nextval('sequence_test2');

nextval

---------

24

(1 row)

SELECT nextval('sequence_test2');

nextval

---------

28

(1 row)

SELECT nextval('sequence_test2');

nextval

---------

36

(1 row)

SELECT nextval('sequence_test2'); -- error

ERROR: nextval: reached maximum value of sequence "sequence_test2" (36)

ALTER SEQUENCE sequence_test2 RESTART WITH -24 START WITH -24

INCREMENT BY -4 MINVALUE -36 MAXVALUE -5 CYCLE;

SELECT nextval('sequence_test2');

nextval

---------

5

-24

(1 row)

SELECT nextval('sequence_test2');

nextval

---------

-28

(1 row)

SELECT nextval('sequence_test2');

nextval

---------

-32

(1 row)

SELECT nextval('sequence_test2');

nextval

---------

-36

(1 row)

SELECT nextval('sequence_test2'); -- cycled

nextval

---------

-5

(1 row)

ALTER SEQUENCE sequence_test2 RESTART WITH -24

NO CYCLE;

SELECT nextval('sequence_test2');

nextval

---------

-24

(1 row)

SELECT nextval('sequence_test2');

nextval

---------

-28

(1 row)

SELECT nextval('sequence_test2');

nextval

---------

-32

(1 row)

SELECT nextval('sequence_test2');

nextval

---------

-36

(1 row)

SELECT nextval('sequence_test2'); -- error

ERROR: nextval: reached minimum value of sequence "sequence_test2" (-36)

-- reset

ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 32 START WITH 32

INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;

SELECT setval('sequence_test2', -100); -- error

ERROR: setval: value -100 is out of bounds for sequence "sequence_test2" (5..36)

SELECT setval('sequence_test2', 100); -- error

ERROR: setval: value 100 is out of bounds for sequence "sequence_test2" (5..36)

SELECT setval('sequence_test2', 5);

setval

--------

5

(1 row)

CREATE SEQUENCE sequence_test3; -- not read from, to test is_called

-- Information schema

SELECT * FROM information_schema.sequences WHERE sequence_name IN

('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',

'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')

SELECT * FROM information_schema.sequences

WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest'])

ORDER BY sequence_name ASC;

sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option

------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------

regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES

regression | public | sequence_test3 | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

regression | public | serialtest2_f2_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

regression | public | serialtest2_f3_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

regression | public | serialtest2_f4_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

regression | public | serialtest2_f5_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

regression | public | serialtest2_f6_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

(7 rows)

sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option

------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------

regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES

regression | public | sequence_test3 | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

regression | public | sequence_test4 | bigint | 64 | 2 | 0 | -1 | -9223372036854775808 | -1 | -1 | NO

regression | public | serialtest2_f2_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

regression | public | serialtest2_f3_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

regression | public | serialtest2_f4_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

regression | public | serialtest2_f5_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

regression | public | serialtest2_f6_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

regression | public | serialtest_f2_foo | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

(9 rows)

SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value

FROM pg_sequences

WHERE sequencename IN

('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',

'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')

WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])

ORDER BY sequencename ASC;

schemaname | sequencename | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value

------------+--------------------+-------------+-----------+---------------------+--------------+-------+------------+------------

public | sequence_test2 | 32 | 5 | 36 | 4 | t | 1 | 5

public | sequence_test3 | 1 | 1 | 9223372036854775807 | 1 | f | 1 |

public | serialtest2_f2_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2

public | serialtest2_f3_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2

public | serialtest2_f4_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2

public | serialtest2_f5_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2

public | serialtest2_f6_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2

(7 rows)

schemaname | sequencename | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value

------------+--------------------+-------------+----------------------+---------------------+--------------+-------+------------+------------

public | sequence_test2 | 32 | 5 | 36 | 4 | t | 1 | 5

public | sequence_test3 | 1 | 1 | 9223372036854775807 | 1 | f | 1 |

public | sequence_test4 | -1 | -9223372036854775808 | -1 | -1 | f | 1 | -1

public | serialtest2_f2_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2

public | serialtest2_f3_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2

public | serialtest2_f4_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2

public | serialtest2_f5_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2

public | serialtest2_f6_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2

public | serialtest_f2_foo | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 3

(9 rows)

SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass);

start_value | minimum_value | maximum_value | increment | cycle_option | cache_size

-------------+----------------------+---------------+-----------+--------------+------------

-1 | -9223372036854775808 | -1 | -1 | f | 1

(1 row)

-- Test comments

COMMENT ON SEQUENCE asdf IS 'won''t work';

SELECT lastval();

ERROR: lastval is not yet defined in this session

CREATE USER regress_seq_user;

-- Test sequences in read-only transactions

CREATE TEMPORARY SEQUENCE sequence_test_temp1;

START TRANSACTION READ ONLY;

SELECT nextval('sequence_test_temp1'); -- ok

nextval

---------

1

(1 row)

SELECT nextval('sequence_test2'); -- error

ERROR: cannot execute nextval() in a read-only transaction

ROLLBACK;

START TRANSACTION READ ONLY;

SELECT setval('sequence_test_temp1', 1); -- ok

setval

--------

1

(1 row)

SELECT setval('sequence_test2', 1); -- error

ERROR: cannot execute setval() in a read-only transaction

ROLLBACK;

-- privileges tests

-- nextval

BEGIN;

1

(1 row)

ROLLBACK;

-- setval

BEGIN;

SET LOCAL SESSION AUTHORIZATION regress_seq_user;

CREATE SEQUENCE seq3;

REVOKE ALL ON seq3 FROM regress_seq_user;

SAVEPOINT save;

SELECT setval('seq3', 5);

ERROR: permission denied for sequence seq3

ROLLBACK TO save;

GRANT UPDATE ON seq3 TO regress_seq_user;

SELECT setval('seq3', 5);

setval

--------

5

(1 row)

SELECT nextval('seq3');

nextval

---------

6

(1 row)

ROLLBACK;

-- ALTER SEQUENCE

BEGIN;

SET LOCAL SESSION AUTHORIZATION regress_seq_user;

ALTER SEQUENCE sequence_test2 START WITH 1;

ERROR: must be owner of relation sequence_test2

ROLLBACK;

-- Sequences should get wiped out as well:

DROP TABLE serialTest, serialTest2;