From bb3f839bfcb396c3066a31559d3a72ef0b4b5fae Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 1 Sep 2008 21:24:52 +0000 Subject: [PATCH] Add a variant expected-output file for the sequence regression test, to cover output that is seen when a checkpoint occurs at just the right time during the test. Per my report of 2008-08-31. This could be back-patched but I'm not sure it's worth the trouble. --- src/test/regress/expected/sequence_1.out | 290 +++++++++++++++++++++++ 1 file changed, 290 insertions(+) create mode 100644 src/test/regress/expected/sequence_1.out diff --git a/src/test/regress/expected/sequence_1.out b/src/test/regress/expected/sequence_1.out new file mode 100644 index 00000000000..a97f499e75b --- /dev/null +++ b/src/test/regress/expected/sequence_1.out @@ -0,0 +1,290 @@ +--- +--- test creation of SERIAL column +--- + +CREATE TABLE serialTest (f1 text, f2 serial); +NOTICE: CREATE TABLE will create implicit sequence "serialtest_f2_seq" for serial column "serialtest.f2" + +INSERT INTO serialTest VALUES ('foo'); +INSERT INTO serialTest VALUES ('bar'); +INSERT INTO serialTest VALUES ('force', 100); +INSERT INTO serialTest VALUES ('wrong', NULL); +ERROR: null value in column "f2" violates not-null constraint + +SELECT * FROM serialTest; + f1 | f2 +-------+----- + foo | 1 + bar | 2 + force | 100 +(3 rows) + +-- basic sequence operations using both text and oid references +CREATE SEQUENCE sequence_test; + +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) + +DROP SEQUENCE sequence_test; +-- renaming sequences +CREATE SEQUENCE foo_seq; +ALTER TABLE foo_seq RENAME TO foo_seq_new; +SELECT * FROM foo_seq_new; + sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called +---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- + foo_seq | 1 | 1 | 1 | 9223372036854775807 | 1 | 1 | 1 | f | f +(1 row) + +SELECT nextval('foo_seq_new'); + nextval +--------- + 1 +(1 row) + +SELECT nextval('foo_seq_new'); + nextval +--------- + 2 +(1 row) + +SELECT * FROM foo_seq_new; + sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called +---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- + foo_seq | 2 | 1 | 1 | 9223372036854775807 | 1 | 1 | 31 | f | t +(1 row) + +DROP SEQUENCE foo_seq_new; +-- renaming serial sequences +ALTER TABLE serialtest_f2_seq RENAME TO serialtest_f2_foo; +INSERT INTO serialTest VALUES ('more'); +SELECT * FROM serialTest; + 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) +); +NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1" +-- 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 for table t1 column f1 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 for table t1 column f2 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 +-- +CREATE SEQUENCE sequence_test2 START WITH 32; +SELECT nextval('sequence_test2'); + nextval +--------- + 32 +(1 row) + +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'); + nextval +--------- + 5 +(1 row) + +ALTER SEQUENCE sequence_test2 RESTART; +SELECT nextval('sequence_test2'); + nextval +--------- + 32 +(1 row) + +SELECT nextval('sequence_test2'); + nextval +--------- + 36 +(1 row) + +SELECT nextval('sequence_test2'); + nextval +--------- + 5 +(1 row) + +-- 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) + +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 +CREATE USER seq_user; +BEGIN; +SET LOCAL SESSION AUTHORIZATION seq_user; +CREATE SEQUENCE seq3; +SELECT nextval('seq3'); + nextval +--------- + 1 +(1 row) + +REVOKE ALL ON seq3 FROM seq_user; +SELECT lastval(); +ERROR: permission denied for sequence seq3 +ROLLBACK; +DROP USER seq_user; +DROP SEQUENCE seq; -- 2.30.2