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.

Reviewed-by: Petr Jelinek <petr.jelinek@2ndquadrant.com>

3 files changed, 9 insertions, 564 deletions

diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index a2bdd3002b0..ad03a31a4e9 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 5d7ab72944a..00000000000
--- 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 a79330e7804..df59f90f046 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -84,7 +84,9 @@ ALTER TABLE foo_seq RENAME TO foo_seq_new;
 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