We maintain a separate test output file sequence_1.out because the
log_cnt value can vary if there is a checkpoint happening at the right
time. So we have to maintain two files because of a one character
difference. I propose the attached patch to restructure the test a bit
to avoid this, without loss of test coverage.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 3d28ea1b6684fb00591526a79dfaeafbff459a44 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Tue, 24 Jan 2017 09:44:40 -0500
Subject: [PATCH] Simplify sequence test
We maintained two separate expected files because log_cnt could be one
of two values. Rewrite the test so that we only need one file.
---
src/test/regress/expected/sequence.out | 10 +-
src/test/regress/expected/sequence_1.out | 559 -------------------------------
src/test/regress/sql/sequence.sql | 4 +-
3 files changed, 9 insertions(+), 564 deletions(-)
delete mode 100644 src/test/regress/expected/sequence_1.out
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index a2bdd3002b..ad03a31a4e 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -190,10 +190,12 @@ SELECT nextval('foo_seq_new');
2
(1 row)
-SELECT * FROM foo_seq_new;
- last_value | log_cnt | is_called
-------------+---------+-----------
- 2 | 31 | t
+-- 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;
diff --git a/src/test/regress/expected/sequence_1.out b/src/test/regress/expected/sequence_1.out
deleted file mode 100644
index 5d7ab72944..0000000000
--- a/src/test/regress/expected/sequence_1.out
+++ /dev/null
@@ -1,559 +0,0 @@
----
---- test creation of SERIAL column
----
-CREATE TABLE serialTest (f1 text, f2 serial);
-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
-DETAIL: Failing row contains (wrong, null).
-SELECT * FROM serialTest;
- f1 | f2
--------+-----
- foo | 1
- bar | 2
- force | 100
-(3 rows)
-
--- 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;
-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)
-
-SELECT * FROM foo_seq_new;
- last_value | log_cnt | is_called
-------------+---------+-----------
- 2 | 32 | 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)
-);
--- 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
---
-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
-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)
-
-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')
- 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)
-
-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')
- 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)
-
--- 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
-CREATE USER regress_seq_user;
--- privileges tests
--- 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;
--- Sequences should get wiped out as well:
-DROP TABLE serialTest, 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;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index a79330e780..df59f90f04 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -84,7 +84,9 @@ CREATE SEQUENCE foo_seq;
SELECT * FROM foo_seq_new;
SELECT nextval('foo_seq_new');
SELECT nextval('foo_seq_new');
-SELECT * FROM foo_seq_new;
+-- 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;
DROP SEQUENCE foo_seq_new;
-- renaming serial sequences
--
2.11.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
