| -- |
| -- 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_class_oid_index.oid; -- not a table |
| ERROR: referenced relation "pg_class_oid_index" 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; |
| -- sequence data types |
| CREATE SEQUENCE sequence_test5 AS integer; |
| CREATE SEQUENCE sequence_test6 AS smallint; |
| CREATE SEQUENCE sequence_test7 AS bigint; |
| CREATE SEQUENCE sequence_test8 AS integer MAXVALUE 100000; |
| CREATE SEQUENCE sequence_test9 AS integer INCREMENT BY -1; |
| CREATE SEQUENCE sequence_test10 AS integer MINVALUE -100000 START 1; |
| CREATE SEQUENCE sequence_test11 AS smallint; |
| CREATE SEQUENCE sequence_test12 AS smallint INCREMENT -1; |
| CREATE SEQUENCE sequence_test13 AS smallint MINVALUE -32768; |
| CREATE SEQUENCE sequence_test14 AS smallint MAXVALUE 32767 INCREMENT -1; |
| CREATE SEQUENCE sequence_testx AS text; |
| ERROR: sequence type must be smallint, integer, or bigint |
| CREATE SEQUENCE sequence_testx AS nosuchtype; |
| ERROR: type "nosuchtype" does not exist |
| LINE 1: CREATE SEQUENCE sequence_testx AS nosuchtype; |
| ^ |
| CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000; |
| ERROR: MAXVALUE (100000) is out of range for sequence data type smallint |
| CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000; |
| ERROR: MINVALUE (-100000) is out of range for sequence data type smallint |
| ALTER SEQUENCE sequence_test5 AS smallint; -- success, max will be adjusted |
| ALTER SEQUENCE sequence_test8 AS smallint; -- fail, max has to be adjusted |
| ERROR: MAXVALUE (100000) is out of range for sequence data type smallint |
| ALTER SEQUENCE sequence_test8 AS smallint MAXVALUE 20000; -- ok now |
| ALTER SEQUENCE sequence_test9 AS smallint; -- success, min will be adjusted |
| ALTER SEQUENCE sequence_test10 AS smallint; -- fail, min has to be adjusted |
| ERROR: MINVALUE (-100000) is out of range for sequence data type smallint |
| ALTER SEQUENCE sequence_test10 AS smallint MINVALUE -20000; -- ok now |
| ALTER SEQUENCE sequence_test11 AS int; -- max will be adjusted |
| ALTER SEQUENCE sequence_test12 AS int; -- min will be adjusted |
| ALTER SEQUENCE sequence_test13 AS int; -- min and max will be adjusted |
| ALTER SEQUENCE sequence_test14 AS int; -- min and max will be adjusted |
| --- |
| --- test creation of SERIAL column |
| --- |
| CREATE TABLE serialTest1 (f1 text, f2 serial); |
| INSERT INTO serialTest1 VALUES ('foo'); |
| INSERT INTO serialTest1 VALUES ('bar'); |
| INSERT INTO serialTest1 VALUES ('force', 100); |
| INSERT INTO serialTest1 VALUES ('wrong', NULL); |
| ERROR: null value in column "f2" of relation "serialtest1" violates not-null constraint |
| DETAIL: Failing row contains (wrong, null). |
| SELECT * FROM serialTest1; |
| f1 | f2 |
| -------+----- |
| foo | 1 |
| bar | 2 |
| force | 100 |
| (3 rows) |
| |
| SELECT pg_get_serial_sequence('serialTest1', 'f2'); |
| pg_get_serial_sequence |
| --------------------------- |
| public.serialtest1_f2_seq |
| (1 row) |
| |
| -- test smallserial / bigserial |
| CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2, |
| f5 bigserial, f6 serial8); |
| INSERT INTO serialTest2 (f1) |
| VALUES ('test_defaults'); |
| INSERT INTO serialTest2 (f1, f2, f3, f4, f5, f6) |
| VALUES ('test_max_vals', 2147483647, 32767, 32767, 9223372036854775807, |
| 9223372036854775807), |
| ('test_min_vals', -2147483648, -32768, -32768, -9223372036854775808, |
| -9223372036854775808); |
| -- All these INSERTs should fail: |
| INSERT INTO serialTest2 (f1, f3) |
| VALUES ('bogus', -32769); |
| ERROR: smallint out of range |
| INSERT INTO serialTest2 (f1, f4) |
| VALUES ('bogus', -32769); |
| ERROR: smallint out of range |
| INSERT INTO serialTest2 (f1, f3) |
| VALUES ('bogus', 32768); |
| ERROR: smallint out of range |
| INSERT INTO serialTest2 (f1, f4) |
| VALUES ('bogus', 32768); |
| ERROR: smallint out of range |
| INSERT INTO serialTest2 (f1, f5) |
| VALUES ('bogus', -9223372036854775809); |
| ERROR: bigint out of range |
| INSERT INTO serialTest2 (f1, f6) |
| VALUES ('bogus', -9223372036854775809); |
| ERROR: bigint out of range |
| INSERT INTO serialTest2 (f1, f5) |
| VALUES ('bogus', 9223372036854775808); |
| ERROR: bigint out of range |
| INSERT INTO serialTest2 (f1, f6) |
| VALUES ('bogus', 9223372036854775808); |
| ERROR: bigint out of range |
| SELECT * FROM serialTest2 ORDER BY f2 ASC; |
| f1 | f2 | f3 | f4 | f5 | f6 |
| ---------------+-------------+--------+--------+----------------------+---------------------- |
| test_min_vals | -2147483648 | -32768 | -32768 | -9223372036854775808 | -9223372036854775808 |
| test_defaults | 1 | 1 | 1 | 1 | 1 |
| test_max_vals | 2147483647 | 32767 | 32767 | 9223372036854775807 | 9223372036854775807 |
| (3 rows) |
| |
| SELECT nextval('serialTest2_f2_seq'); |
| nextval |
| --------- |
| 2 |
| (1 row) |
| |
| SELECT nextval('serialTest2_f3_seq'); |
| nextval |
| --------- |
| 2 |
| (1 row) |
| |
| SELECT nextval('serialTest2_f4_seq'); |
| nextval |
| --------- |
| 2 |
| (1 row) |
| |
| SELECT nextval('serialTest2_f5_seq'); |
| nextval |
| --------- |
| 2 |
| (1 row) |
| |
| SELECT nextval('serialTest2_f6_seq'); |
| nextval |
| --------- |
| 2 |
| (1 row) |
| |
| -- basic sequence operations using both text and oid references |
| CREATE SEQUENCE sequence_test; |
| CREATE SEQUENCE IF NOT EXISTS sequence_test; |
| NOTICE: relation "sequence_test" already exists, skipping |
| SELECT nextval('sequence_test'::text); |
| nextval |
| --------- |
| 1 |
| (1 row) |
| |
| SELECT nextval('sequence_test'::regclass); |
| nextval |
| --------- |
| 2 |
| (1 row) |
| |
| SELECT currval('sequence_test'::text); |
| currval |
| --------- |
| 2 |
| (1 row) |
| |
| SELECT currval('sequence_test'::regclass); |
| currval |
| --------- |
| 2 |
| (1 row) |
| |
| SELECT setval('sequence_test'::text, 32); |
| setval |
| -------- |
| 32 |
| (1 row) |
| |
| SELECT nextval('sequence_test'::regclass); |
| nextval |
| --------- |
| 33 |
| (1 row) |
| |
| SELECT setval('sequence_test'::text, 99, false); |
| setval |
| -------- |
| 99 |
| (1 row) |
| |
| SELECT nextval('sequence_test'::regclass); |
| nextval |
| --------- |
| 99 |
| (1 row) |
| |
| SELECT setval('sequence_test'::regclass, 32); |
| setval |
| -------- |
| 32 |
| (1 row) |
| |
| SELECT nextval('sequence_test'::text); |
| nextval |
| --------- |
| 33 |
| (1 row) |
| |
| SELECT setval('sequence_test'::regclass, 99, false); |
| setval |
| -------- |
| 99 |
| (1 row) |
| |
| SELECT nextval('sequence_test'::text); |
| nextval |
| --------- |
| 99 |
| (1 row) |
| |
| DISCARD SEQUENCES; |
| SELECT currval('sequence_test'::regclass); |
| ERROR: currval of sequence "sequence_test" is not yet defined in this session |
| DROP SEQUENCE sequence_test; |
| -- renaming sequences |
| CREATE SEQUENCE foo_seq CACHE 1; |
| ALTER TABLE foo_seq RENAME TO foo_seq_new; |
| SELECT * FROM foo_seq_new; |
| last_value | log_cnt | is_called |
| ------------+---------+----------- |
| 1 | 0 | f |
| (1 row) |
| |
| SELECT nextval('foo_seq_new'); |
| nextval |
| --------- |
| 1 |
| (1 row) |
| |
| SELECT nextval('foo_seq_new'); |
| nextval |
| --------- |
| 2 |
| (1 row) |
| |
| -- log_cnt can be higher if there is a checkpoint just at the right |
| -- time, so just test for the expected range |
| SELECT last_value, log_cnt IN (31, 32) AS log_cnt_ok, is_called FROM foo_seq_new; |
| last_value | log_cnt_ok | is_called |
| ------------+------------+----------- |
| 2 | t | t |
| (1 row) |
| |
| DROP SEQUENCE foo_seq_new; |
| -- renaming serial sequences |
| ALTER TABLE serialtest1_f2_seq RENAME TO serialtest1_f2_foo; |
| INSERT INTO serialTest1 VALUES ('more'); |
| SELECT * FROM serialTest1; |
| f1 | f2 |
| -------+----- |
| foo | 1 |
| bar | 2 |
| force | 100 |
| more | 3 |
| (4 rows) |
| |
| -- |
| -- Check dependencies of serial and ordinary sequences |
| -- |
| CREATE TEMP SEQUENCE myseq2; |
| CREATE TEMP SEQUENCE myseq3; |
| CREATE TEMP TABLE t1 ( |
| f1 serial, |
| f2 int DEFAULT nextval('myseq2'), |
| f3 int DEFAULT nextval('myseq3'::text) |
| ); |
| -- Both drops should fail, but with different error messages: |
| DROP SEQUENCE t1_f1_seq; |
| ERROR: cannot drop sequence t1_f1_seq because other objects depend on it |
| DETAIL: default value for column f1 of table t1 depends on sequence t1_f1_seq |
| HINT: Use DROP ... CASCADE to drop the dependent objects too. |
| DROP SEQUENCE myseq2; |
| ERROR: cannot drop sequence myseq2 because other objects depend on it |
| DETAIL: default value for column f2 of table t1 depends on sequence myseq2 |
| HINT: Use DROP ... CASCADE to drop the dependent objects too. |
| -- This however will work: |
| DROP SEQUENCE myseq3; |
| DROP TABLE t1; |
| -- Fails because no longer existent: |
| DROP SEQUENCE t1_f1_seq; |
| ERROR: sequence "t1_f1_seq" does not exist |
| -- Now OK: |
| DROP SEQUENCE myseq2; |
| -- |
| -- Alter sequence |
| -- |
| ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24 |
| INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE; |
| NOTICE: relation "sequence_test2" does not exist, skipping |
| ALTER SEQUENCE serialTest1 CYCLE; -- error, not a sequence |
| ERROR: "serialtest1" 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; |
| 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'); -- 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 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 |
| --------- |
| -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 ~ 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_test10 | smallint | 16 | 2 | 0 | 1 | -20000 | 32767 | 1 | NO |
| regression | public | sequence_test11 | integer | 32 | 2 | 0 | 1 | 1 | 2147483647 | 1 | NO |
| regression | public | sequence_test12 | integer | 32 | 2 | 0 | -1 | -2147483648 | -1 | -1 | NO |
| regression | public | sequence_test13 | integer | 32 | 2 | 0 | -32768 | -2147483648 | 2147483647 | 1 | NO |
| regression | public | sequence_test14 | integer | 32 | 2 | 0 | 32767 | -2147483648 | 2147483647 | -1 | NO |
| 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 | sequence_test5 | smallint | 16 | 2 | 0 | 1 | 1 | 32767 | 1 | NO |
| regression | public | sequence_test6 | smallint | 16 | 2 | 0 | 1 | 1 | 32767 | 1 | NO |
| regression | public | sequence_test7 | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO |
| regression | public | sequence_test8 | smallint | 16 | 2 | 0 | 1 | 1 | 20000 | 1 | NO |
| regression | public | sequence_test9 | smallint | 16 | 2 | 0 | -1 | -32768 | -1 | -1 | NO |
| regression | public | serialtest1_f2_foo | integer | 32 | 2 | 0 | 1 | 1 | 2147483647 | 1 | NO |
| regression | public | serialtest2_f2_seq | integer | 32 | 2 | 0 | 1 | 1 | 2147483647 | 1 | NO |
| regression | public | serialtest2_f3_seq | smallint | 16 | 2 | 0 | 1 | 1 | 32767 | 1 | NO |
| regression | public | serialtest2_f4_seq | smallint | 16 | 2 | 0 | 1 | 1 | 32767 | 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 |
| (19 rows) |
| |
| SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value |
| FROM pg_sequences |
| 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_test10 | 1 | -20000 | 32767 | 1 | f | 20 | |
| public | sequence_test11 | 1 | 1 | 2147483647 | 1 | f | 20 | |
| public | sequence_test12 | -1 | -2147483648 | -1 | -1 | f | 20 | |
| public | sequence_test13 | -32768 | -2147483648 | 2147483647 | 1 | f | 20 | |
| public | sequence_test14 | 32767 | -2147483648 | 2147483647 | -1 | f | 20 | |
| public | sequence_test2 | 32 | 5 | 36 | 4 | t | 20 | 5 |
| public | sequence_test3 | 1 | 1 | 9223372036854775807 | 1 | f | 20 | |
| public | sequence_test4 | -1 | -9223372036854775808 | -1 | -1 | f | 20 | -20 |
| public | sequence_test5 | 1 | 1 | 32767 | 1 | f | 20 | |
| public | sequence_test6 | 1 | 1 | 32767 | 1 | f | 20 | |
| public | sequence_test7 | 1 | 1 | 9223372036854775807 | 1 | f | 20 | |
| public | sequence_test8 | 1 | 1 | 20000 | 1 | f | 20 | |
| public | sequence_test9 | -1 | -32768 | -1 | -1 | f | 20 | |
| public | serialtest1_f2_foo | 1 | 1 | 2147483647 | 1 | f | 1 | 3 |
| public | serialtest2_f2_seq | 1 | 1 | 2147483647 | 1 | f | 1 | 2 |
| public | serialtest2_f3_seq | 1 | 1 | 32767 | 1 | f | 1 | 2 |
| public | serialtest2_f4_seq | 1 | 1 | 32767 | 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 |
| (19 rows) |
| |
| SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass); |
| start_value | minimum_value | maximum_value | increment | cycle_option | cache_size | data_type |
| -------------+----------------------+---------------+-----------+--------------+------------+----------- |
| -1 | -9223372036854775808 | -1 | -1 | f | 20 | 20 |
| (1 row) |
| |
| \d sequence_test4 |
| Sequence "public.sequence_test4" |
| Type | Start | Minimum | Maximum | Increment | Cycles? | Cache |
| --------+-------+----------------------+---------+-----------+---------+------- |
| bigint | -1 | -9223372036854775808 | -1 | -1 | no | 20 |
| |
| \d serialtest2_f2_seq |
| Sequence "public.serialtest2_f2_seq" |
| Type | Start | Minimum | Maximum | Increment | Cycles? | Cache |
| ---------+-------+---------+------------+-----------+---------+------- |
| integer | 1 | 1 | 2147483647 | 1 | no | 1 |
| Owned by: public.serialtest2.f2 |
| |
| -- Test comments |
| COMMENT ON SEQUENCE asdf IS 'won''t work'; |
| ERROR: relation "asdf" does not exist |
| COMMENT ON SEQUENCE sequence_test2 IS 'will work'; |
| COMMENT ON SEQUENCE sequence_test2 IS NULL; |
| -- Test lastval() |
| CREATE SEQUENCE seq; |
| SELECT nextval('seq'); |
| nextval |
| --------- |
| 1 |
| (1 row) |
| |
| SELECT lastval(); |
| lastval |
| --------- |
| 1 |
| (1 row) |
| |
| SELECT setval('seq', 99); |
| setval |
| -------- |
| 99 |
| (1 row) |
| |
| SELECT lastval(); |
| lastval |
| --------- |
| 99 |
| (1 row) |
| |
| DISCARD SEQUENCES; |
| SELECT lastval(); |
| ERROR: lastval is not yet defined in this session |
| CREATE SEQUENCE seq2; |
| SELECT nextval('seq2'); |
| nextval |
| --------- |
| 1 |
| (1 row) |
| |
| SELECT lastval(); |
| lastval |
| --------- |
| 1 |
| (1 row) |
| |
| DROP SEQUENCE seq2; |
| -- should fail |
| SELECT lastval(); |
| ERROR: lastval is not yet defined in this session |
| -- 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 |
| CREATE USER regress_seq_user; |
| -- nextval |
| BEGIN; |
| SET LOCAL SESSION AUTHORIZATION regress_seq_user; |
| CREATE SEQUENCE seq3; |
| REVOKE ALL ON seq3 FROM regress_seq_user; |
| GRANT SELECT ON seq3 TO regress_seq_user; |
| SELECT nextval('seq3'); |
| ERROR: permission denied for sequence seq3 |
| ROLLBACK; |
| BEGIN; |
| SET LOCAL SESSION AUTHORIZATION regress_seq_user; |
| CREATE SEQUENCE seq3; |
| REVOKE ALL ON seq3 FROM regress_seq_user; |
| GRANT UPDATE ON seq3 TO regress_seq_user; |
| SELECT nextval('seq3'); |
| nextval |
| --------- |
| 1 |
| (1 row) |
| |
| ROLLBACK; |
| BEGIN; |
| SET LOCAL SESSION AUTHORIZATION regress_seq_user; |
| CREATE SEQUENCE seq3; |
| REVOKE ALL ON seq3 FROM regress_seq_user; |
| GRANT USAGE ON seq3 TO regress_seq_user; |
| SELECT nextval('seq3'); |
| nextval |
| --------- |
| 1 |
| (1 row) |
| |
| ROLLBACK; |
| -- currval |
| BEGIN; |
| SET LOCAL SESSION AUTHORIZATION regress_seq_user; |
| CREATE SEQUENCE seq3; |
| SELECT nextval('seq3'); |
| nextval |
| --------- |
| 1 |
| (1 row) |
| |
| REVOKE ALL ON seq3 FROM regress_seq_user; |
| GRANT SELECT ON seq3 TO regress_seq_user; |
| SELECT currval('seq3'); |
| currval |
| --------- |
| 1 |
| (1 row) |
| |
| ROLLBACK; |
| BEGIN; |
| SET LOCAL SESSION AUTHORIZATION regress_seq_user; |
| CREATE SEQUENCE seq3; |
| SELECT nextval('seq3'); |
| nextval |
| --------- |
| 1 |
| (1 row) |
| |
| REVOKE ALL ON seq3 FROM regress_seq_user; |
| GRANT UPDATE ON seq3 TO regress_seq_user; |
| SELECT currval('seq3'); |
| ERROR: permission denied for sequence seq3 |
| ROLLBACK; |
| BEGIN; |
| SET LOCAL SESSION AUTHORIZATION regress_seq_user; |
| CREATE SEQUENCE seq3; |
| SELECT nextval('seq3'); |
| nextval |
| --------- |
| 1 |
| (1 row) |
| |
| REVOKE ALL ON seq3 FROM regress_seq_user; |
| GRANT USAGE ON seq3 TO regress_seq_user; |
| SELECT currval('seq3'); |
| currval |
| --------- |
| 1 |
| (1 row) |
| |
| ROLLBACK; |
| -- lastval |
| BEGIN; |
| SET LOCAL SESSION AUTHORIZATION regress_seq_user; |
| CREATE SEQUENCE seq3; |
| SELECT nextval('seq3'); |
| nextval |
| --------- |
| 1 |
| (1 row) |
| |
| REVOKE ALL ON seq3 FROM regress_seq_user; |
| GRANT SELECT ON seq3 TO regress_seq_user; |
| SELECT lastval(); |
| lastval |
| --------- |
| 1 |
| (1 row) |
| |
| ROLLBACK; |
| BEGIN; |
| SET LOCAL SESSION AUTHORIZATION regress_seq_user; |
| CREATE SEQUENCE seq3; |
| SELECT nextval('seq3'); |
| nextval |
| --------- |
| 1 |
| (1 row) |
| |
| REVOKE ALL ON seq3 FROM regress_seq_user; |
| GRANT UPDATE ON seq3 TO regress_seq_user; |
| SELECT lastval(); |
| ERROR: permission denied for sequence seq3 |
| ROLLBACK; |
| BEGIN; |
| SET LOCAL SESSION AUTHORIZATION regress_seq_user; |
| CREATE SEQUENCE seq3; |
| SELECT nextval('seq3'); |
| nextval |
| --------- |
| 1 |
| (1 row) |
| |
| REVOKE ALL ON seq3 FROM regress_seq_user; |
| GRANT USAGE ON seq3 TO regress_seq_user; |
| SELECT lastval(); |
| lastval |
| --------- |
| 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 sequence sequence_test2 |
| ROLLBACK; |
| -- Sequences should get wiped out as well: |
| DROP TABLE serialTest1, serialTest2; |
| -- Make sure sequences are gone: |
| SELECT * FROM information_schema.sequences WHERE sequence_name IN |
| ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq', |
| 'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq') |
| 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 |
| (1 row) |
| |
| DROP USER regress_seq_user; |
| DROP SEQUENCE seq; |
| -- cache tests |
| CREATE SEQUENCE test_seq1 CACHE 10; |
| SELECT nextval('test_seq1'); |
| nextval |
| --------- |
| 1 |
| (1 row) |
| |
| SELECT nextval('test_seq1'); |
| nextval |
| --------- |
| 2 |
| (1 row) |
| |
| SELECT nextval('test_seq1'); |
| nextval |
| --------- |
| 3 |
| (1 row) |
| |
| DROP SEQUENCE test_seq1; |
