1-- 2-- CREATE SEQUENCE 3-- 4 5-- various error cases 6CREATE UNLOGGED SEQUENCE sequence_testx; 7CREATE SEQUENCE sequence_testx INCREMENT BY 0; 8CREATE SEQUENCE sequence_testx INCREMENT BY -1 MINVALUE 20; 9CREATE SEQUENCE sequence_testx INCREMENT BY 1 MAXVALUE -20; 10CREATE SEQUENCE sequence_testx INCREMENT BY -1 START 10; 11CREATE SEQUENCE sequence_testx INCREMENT BY 1 START -10; 12CREATE SEQUENCE sequence_testx CACHE 0; 13 14-- OWNED BY errors 15CREATE SEQUENCE sequence_testx OWNED BY nobody; -- nonsense word 16CREATE SEQUENCE sequence_testx OWNED BY pg_class_oid_index.oid; -- not a table 17CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname; -- not same schema 18CREATE TABLE sequence_test_table (a int); 19CREATE SEQUENCE sequence_testx OWNED BY sequence_test_table.b; -- wrong column 20DROP TABLE sequence_test_table; 21 22-- sequence data types 23CREATE SEQUENCE sequence_test5 AS integer; 24CREATE SEQUENCE sequence_test6 AS smallint; 25CREATE SEQUENCE sequence_test7 AS bigint; 26CREATE SEQUENCE sequence_test8 AS integer MAXVALUE 100000; 27CREATE SEQUENCE sequence_test9 AS integer INCREMENT BY -1; 28CREATE SEQUENCE sequence_test10 AS integer MINVALUE -100000 START 1; 29CREATE SEQUENCE sequence_test11 AS smallint; 30CREATE SEQUENCE sequence_test12 AS smallint INCREMENT -1; 31CREATE SEQUENCE sequence_test13 AS smallint MINVALUE -32768; 32CREATE SEQUENCE sequence_test14 AS smallint MAXVALUE 32767 INCREMENT -1; 33CREATE SEQUENCE sequence_testx AS text; 34CREATE SEQUENCE sequence_testx AS nosuchtype; 35 36CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000; 37CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000; 38 39ALTER SEQUENCE sequence_test5 AS smallint; -- success, max will be adjusted 40ALTER SEQUENCE sequence_test8 AS smallint; -- fail, max has to be adjusted 41ALTER SEQUENCE sequence_test8 AS smallint MAXVALUE 20000; -- ok now 42ALTER SEQUENCE sequence_test9 AS smallint; -- success, min will be adjusted 43ALTER SEQUENCE sequence_test10 AS smallint; -- fail, min has to be adjusted 44ALTER SEQUENCE sequence_test10 AS smallint MINVALUE -20000; -- ok now 45 46ALTER SEQUENCE sequence_test11 AS int; -- max will be adjusted 47ALTER SEQUENCE sequence_test12 AS int; -- min will be adjusted 48ALTER SEQUENCE sequence_test13 AS int; -- min and max will be adjusted 49ALTER SEQUENCE sequence_test14 AS int; -- min and max will be adjusted 50 51--- 52--- test creation of SERIAL column 53--- 54 55CREATE TABLE serialTest1 (f1 text, f2 serial); 56 57INSERT INTO serialTest1 VALUES ('foo'); 58INSERT INTO serialTest1 VALUES ('bar'); 59INSERT INTO serialTest1 VALUES ('force', 100); 60INSERT INTO serialTest1 VALUES ('wrong', NULL); 61 62SELECT * FROM serialTest1; 63 64SELECT pg_get_serial_sequence('serialTest1', 'f2'); 65 66-- test smallserial / bigserial 67CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2, 68 f5 bigserial, f6 serial8); 69 70INSERT INTO serialTest2 (f1) 71 VALUES ('test_defaults'); 72 73INSERT INTO serialTest2 (f1, f2, f3, f4, f5, f6) 74 VALUES ('test_max_vals', 2147483647, 32767, 32767, 9223372036854775807, 75 9223372036854775807), 76 ('test_min_vals', -2147483648, -32768, -32768, -9223372036854775808, 77 -9223372036854775808); 78 79-- All these INSERTs should fail: 80INSERT INTO serialTest2 (f1, f3) 81 VALUES ('bogus', -32769); 82 83INSERT INTO serialTest2 (f1, f4) 84 VALUES ('bogus', -32769); 85 86INSERT INTO serialTest2 (f1, f3) 87 VALUES ('bogus', 32768); 88 89INSERT INTO serialTest2 (f1, f4) 90 VALUES ('bogus', 32768); 91 92INSERT INTO serialTest2 (f1, f5) 93 VALUES ('bogus', -9223372036854775809); 94 95INSERT INTO serialTest2 (f1, f6) 96 VALUES ('bogus', -9223372036854775809); 97 98INSERT INTO serialTest2 (f1, f5) 99 VALUES ('bogus', 9223372036854775808); 100 101INSERT INTO serialTest2 (f1, f6) 102 VALUES ('bogus', 9223372036854775808); 103 104SELECT * FROM serialTest2 ORDER BY f2 ASC; 105 106SELECT nextval('serialTest2_f2_seq'); 107SELECT nextval('serialTest2_f3_seq'); 108SELECT nextval('serialTest2_f4_seq'); 109SELECT nextval('serialTest2_f5_seq'); 110SELECT nextval('serialTest2_f6_seq'); 111 112-- basic sequence operations using both text and oid references 113CREATE SEQUENCE sequence_test; 114CREATE SEQUENCE IF NOT EXISTS sequence_test; 115 116SELECT nextval('sequence_test'::text); 117SELECT nextval('sequence_test'::regclass); 118SELECT currval('sequence_test'::text); 119SELECT currval('sequence_test'::regclass); 120SELECT setval('sequence_test'::text, 32); 121SELECT nextval('sequence_test'::regclass); 122SELECT setval('sequence_test'::text, 99, false); 123SELECT nextval('sequence_test'::regclass); 124SELECT setval('sequence_test'::regclass, 32); 125SELECT nextval('sequence_test'::text); 126SELECT setval('sequence_test'::regclass, 99, false); 127SELECT nextval('sequence_test'::text); 128DISCARD SEQUENCES; 129SELECT currval('sequence_test'::regclass); 130 131DROP SEQUENCE sequence_test; 132 133-- renaming sequences 134CREATE SEQUENCE foo_seq; 135ALTER TABLE foo_seq RENAME TO foo_seq_new; 136SELECT * FROM foo_seq_new; 137SELECT nextval('foo_seq_new'); 138SELECT nextval('foo_seq_new'); 139-- log_cnt can be higher if there is a checkpoint just at the right 140-- time, so just test for the expected range 141SELECT last_value, log_cnt IN (31, 32) AS log_cnt_ok, is_called FROM foo_seq_new; 142DROP SEQUENCE foo_seq_new; 143 144-- renaming serial sequences 145ALTER TABLE serialtest1_f2_seq RENAME TO serialtest1_f2_foo; 146INSERT INTO serialTest1 VALUES ('more'); 147SELECT * FROM serialTest1; 148 149-- 150-- Check dependencies of serial and ordinary sequences 151-- 152CREATE TEMP SEQUENCE myseq2; 153CREATE TEMP SEQUENCE myseq3; 154CREATE TEMP TABLE t1 ( 155 f1 serial, 156 f2 int DEFAULT nextval('myseq2'), 157 f3 int DEFAULT nextval('myseq3'::text) 158); 159-- Both drops should fail, but with different error messages: 160DROP SEQUENCE t1_f1_seq; 161DROP SEQUENCE myseq2; 162-- This however will work: 163DROP SEQUENCE myseq3; 164DROP TABLE t1; 165-- Fails because no longer existent: 166DROP SEQUENCE t1_f1_seq; 167-- Now OK: 168DROP SEQUENCE myseq2; 169 170-- 171-- Alter sequence 172-- 173 174ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24 175 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE; 176 177ALTER SEQUENCE serialTest1 CYCLE; -- error, not a sequence 178 179CREATE SEQUENCE sequence_test2 START WITH 32; 180CREATE SEQUENCE sequence_test4 INCREMENT BY -1; 181 182SELECT nextval('sequence_test2'); 183SELECT nextval('sequence_test4'); 184 185ALTER SEQUENCE sequence_test2 RESTART; 186SELECT nextval('sequence_test2'); 187 188ALTER SEQUENCE sequence_test2 RESTART WITH 0; -- error 189ALTER SEQUENCE sequence_test4 RESTART WITH 40; -- error 190 191-- test CYCLE and NO CYCLE 192ALTER SEQUENCE sequence_test2 RESTART WITH 24 193 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE; 194SELECT nextval('sequence_test2'); 195SELECT nextval('sequence_test2'); 196SELECT nextval('sequence_test2'); 197SELECT nextval('sequence_test2'); 198SELECT nextval('sequence_test2'); -- cycled 199 200ALTER SEQUENCE sequence_test2 RESTART WITH 24 201 NO CYCLE; 202SELECT nextval('sequence_test2'); 203SELECT nextval('sequence_test2'); 204SELECT nextval('sequence_test2'); 205SELECT nextval('sequence_test2'); 206SELECT nextval('sequence_test2'); -- error 207 208ALTER SEQUENCE sequence_test2 RESTART WITH -24 START WITH -24 209 INCREMENT BY -4 MINVALUE -36 MAXVALUE -5 CYCLE; 210SELECT nextval('sequence_test2'); 211SELECT nextval('sequence_test2'); 212SELECT nextval('sequence_test2'); 213SELECT nextval('sequence_test2'); 214SELECT nextval('sequence_test2'); -- cycled 215 216ALTER SEQUENCE sequence_test2 RESTART WITH -24 217 NO CYCLE; 218SELECT nextval('sequence_test2'); 219SELECT nextval('sequence_test2'); 220SELECT nextval('sequence_test2'); 221SELECT nextval('sequence_test2'); 222SELECT nextval('sequence_test2'); -- error 223 224-- reset 225ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 32 START WITH 32 226 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE; 227 228SELECT setval('sequence_test2', -100); -- error 229SELECT setval('sequence_test2', 100); -- error 230SELECT setval('sequence_test2', 5); 231 232CREATE SEQUENCE sequence_test3; -- not read from, to test is_called 233 234 235-- Information schema 236SELECT * FROM information_schema.sequences 237 WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest']) 238 ORDER BY sequence_name ASC; 239 240SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value 241FROM pg_sequences 242WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest']) 243 ORDER BY sequencename ASC; 244 245 246SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass); 247 248 249\d sequence_test4 250\d serialtest2_f2_seq 251 252 253-- Test comments 254COMMENT ON SEQUENCE asdf IS 'won''t work'; 255COMMENT ON SEQUENCE sequence_test2 IS 'will work'; 256COMMENT ON SEQUENCE sequence_test2 IS NULL; 257 258-- Test lastval() 259CREATE SEQUENCE seq; 260SELECT nextval('seq'); 261SELECT lastval(); 262SELECT setval('seq', 99); 263SELECT lastval(); 264DISCARD SEQUENCES; 265SELECT lastval(); 266 267CREATE SEQUENCE seq2; 268SELECT nextval('seq2'); 269SELECT lastval(); 270 271DROP SEQUENCE seq2; 272-- should fail 273SELECT lastval(); 274 275-- Test sequences in read-only transactions 276CREATE TEMPORARY SEQUENCE sequence_test_temp1; 277START TRANSACTION READ ONLY; 278SELECT nextval('sequence_test_temp1'); -- ok 279SELECT nextval('sequence_test2'); -- error 280ROLLBACK; 281START TRANSACTION READ ONLY; 282SELECT setval('sequence_test_temp1', 1); -- ok 283SELECT setval('sequence_test2', 1); -- error 284ROLLBACK; 285 286-- privileges tests 287 288CREATE USER regress_seq_user; 289 290-- nextval 291BEGIN; 292SET LOCAL SESSION AUTHORIZATION regress_seq_user; 293CREATE SEQUENCE seq3; 294REVOKE ALL ON seq3 FROM regress_seq_user; 295GRANT SELECT ON seq3 TO regress_seq_user; 296SELECT nextval('seq3'); 297ROLLBACK; 298 299BEGIN; 300SET LOCAL SESSION AUTHORIZATION regress_seq_user; 301CREATE SEQUENCE seq3; 302REVOKE ALL ON seq3 FROM regress_seq_user; 303GRANT UPDATE ON seq3 TO regress_seq_user; 304SELECT nextval('seq3'); 305ROLLBACK; 306 307BEGIN; 308SET LOCAL SESSION AUTHORIZATION regress_seq_user; 309CREATE SEQUENCE seq3; 310REVOKE ALL ON seq3 FROM regress_seq_user; 311GRANT USAGE ON seq3 TO regress_seq_user; 312SELECT nextval('seq3'); 313ROLLBACK; 314 315-- currval 316BEGIN; 317SET LOCAL SESSION AUTHORIZATION regress_seq_user; 318CREATE SEQUENCE seq3; 319SELECT nextval('seq3'); 320REVOKE ALL ON seq3 FROM regress_seq_user; 321GRANT SELECT ON seq3 TO regress_seq_user; 322SELECT currval('seq3'); 323ROLLBACK; 324 325BEGIN; 326SET LOCAL SESSION AUTHORIZATION regress_seq_user; 327CREATE SEQUENCE seq3; 328SELECT nextval('seq3'); 329REVOKE ALL ON seq3 FROM regress_seq_user; 330GRANT UPDATE ON seq3 TO regress_seq_user; 331SELECT currval('seq3'); 332ROLLBACK; 333 334BEGIN; 335SET LOCAL SESSION AUTHORIZATION regress_seq_user; 336CREATE SEQUENCE seq3; 337SELECT nextval('seq3'); 338REVOKE ALL ON seq3 FROM regress_seq_user; 339GRANT USAGE ON seq3 TO regress_seq_user; 340SELECT currval('seq3'); 341ROLLBACK; 342 343-- lastval 344BEGIN; 345SET LOCAL SESSION AUTHORIZATION regress_seq_user; 346CREATE SEQUENCE seq3; 347SELECT nextval('seq3'); 348REVOKE ALL ON seq3 FROM regress_seq_user; 349GRANT SELECT ON seq3 TO regress_seq_user; 350SELECT lastval(); 351ROLLBACK; 352 353BEGIN; 354SET LOCAL SESSION AUTHORIZATION regress_seq_user; 355CREATE SEQUENCE seq3; 356SELECT nextval('seq3'); 357REVOKE ALL ON seq3 FROM regress_seq_user; 358GRANT UPDATE ON seq3 TO regress_seq_user; 359SELECT lastval(); 360ROLLBACK; 361 362BEGIN; 363SET LOCAL SESSION AUTHORIZATION regress_seq_user; 364CREATE SEQUENCE seq3; 365SELECT nextval('seq3'); 366REVOKE ALL ON seq3 FROM regress_seq_user; 367GRANT USAGE ON seq3 TO regress_seq_user; 368SELECT lastval(); 369ROLLBACK; 370 371-- setval 372BEGIN; 373SET LOCAL SESSION AUTHORIZATION regress_seq_user; 374CREATE SEQUENCE seq3; 375REVOKE ALL ON seq3 FROM regress_seq_user; 376SAVEPOINT save; 377SELECT setval('seq3', 5); 378ROLLBACK TO save; 379GRANT UPDATE ON seq3 TO regress_seq_user; 380SELECT setval('seq3', 5); 381SELECT nextval('seq3'); 382ROLLBACK; 383 384-- ALTER SEQUENCE 385BEGIN; 386SET LOCAL SESSION AUTHORIZATION regress_seq_user; 387ALTER SEQUENCE sequence_test2 START WITH 1; 388ROLLBACK; 389 390-- Sequences should get wiped out as well: 391DROP TABLE serialTest1, serialTest2; 392 393-- Make sure sequences are gone: 394SELECT * FROM information_schema.sequences WHERE sequence_name IN 395 ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq', 396 'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq') 397 ORDER BY sequence_name ASC; 398 399DROP USER regress_seq_user; 400DROP SEQUENCE seq; 401 402-- cache tests 403CREATE SEQUENCE test_seq1 CACHE 10; 404SELECT nextval('test_seq1'); 405SELECT nextval('test_seq1'); 406SELECT nextval('test_seq1'); 407 408DROP SEQUENCE test_seq1; 409