On 1/25/17 11:57 PM, Michael Paquier wrote:
> @@ -15984,6 +15992,9 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
> "CREATE SEQUENCE %s\n",
> fmtId(tbinfo->dobj.name));
>
> + if (strcmp(seqtype, "bigint") != 0)
> + appendPQExpBuffer(query, " AS %s\n", seqtype);
> Wouldn't it be better to assign that unconditionally? There is no
> reason that a dump taken from pg_dump version X will work on X - 1 (as
> there is no reason to not make the life of users uselessly difficult
> as that's your point), but that seems better to me than rely on the
> sequence type hardcoded in all the pre-10 dump queries for sequences.
Generally, we don't add default values, to keep the dumps from being too
verbose.
(I also think that being able to restore dumps to older versions would
be nice, but that's another discussion.)
> Could you increase the regression test coverage to cover some of the
> new code paths? For example such cases are not tested:
> =# create sequence toto as smallint;
> CREATE SEQUENCE
> =# alter sequence toto as smallint maxvalue 1000;
> ERROR: 22023: RESTART value (2147483646) cannot be greater than MAXVALUE
> (1000)
> LOCATION: init_params, sequence.c:1537
Yeah, I had taken some notes along the way to add more test coverage, so
since you're interested, attached is a patch. It's independent of the
current patch and overlaps slightly. The example you show isn't really
a new code path, just triggered differently, but the enhanced tests will
cover it nonetheless.
> =# alter sequence toto as smallint;
> ERROR: 22023: MAXVALUE (2147483647) is too large for sequence data
> type smallint
> LOCATION: init_params, sequence.c:1407
>
> + if ((seqform->seqtypid == INT2OID && seqform->seqmin < PG_INT16_MIN)
> + || (seqform->seqtypid == INT4OID && seqform->seqmin < PG_INT32_MIN)
> + || (seqform->seqtypid == INT8OID && seqform->seqmin < PG_INT64_MIN))
> + {
> + char bufm[100];
> +
> + snprintf(bufm, sizeof(bufm), INT64_FORMAT, seqform->seqmin);
> +
> + ereport(ERROR,
> + (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> + errmsg("MINVALUE (%s) is too large for sequence data type
> %s",
> + bufm, format_type_be(seqform->seqtypid))));
> + }
> "large" does not apply to values lower than the minimum, no? The int64
> path is never going to be reached (same for the max value), it doesn't
> hurt to code it I agree.
Yeah, I think that should be rephrased as something like "out of
bounds", which is the term used elsewhere.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 533c4dcd04ffab9b8177307039f6c9ebcd6808b9 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Fri, 27 Jan 2017 12:43:14 -0500
Subject: [PATCH] Additional test coverage for sequences
---
src/test/regress/expected/sequence.out | 231 ++++++++++++++++++++++++++++-----
src/test/regress/sql/sequence.sql | 95 ++++++++++++--
2 files changed, 286 insertions(+), 40 deletions(-)
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index ad03a31a4e..c19b5f5ef6 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -1,3 +1,33 @@
+--
+-- 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_tables.tablename; -- not a table
+ERROR: referenced relation "pg_tables" 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;
---
--- test creation of SERIAL column
---
@@ -242,17 +272,38 @@ DROP SEQUENCE myseq2;
-- Alter sequence
--
ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24
- INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+ INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
NOTICE: relation "sequence_test2" does not exist, skipping
+ALTER SEQUENCE pg_class CYCLE; -- error, not a sequence
+ERROR: "pg_class" 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;
+ INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
SELECT nextval('sequence_test2');
nextval
---------
@@ -277,13 +328,26 @@ SELECT nextval('sequence_test2');
36
(1 row)
-SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2'); -- cycled
nextval
---------
5
(1 row)
-ALTER SEQUENCE sequence_test2 RESTART;
+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
---------
@@ -296,45 +360,121 @@ SELECT nextval('sequence_test2');
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
---------
- 5
+ -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 IN
- ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
- 'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+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_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)
+ 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 | sequence_test4 | bigint | 64 | 2 | 0 | -1 | -9223372036854775808 | -1 | -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
+ regression | public | serialtest_f2_foo | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
+(9 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')
+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_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)
+ 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 | sequence_test4 | -1 | -9223372036854775808 | -1 | -1 | f | 1 | -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
+ public | serialtest_f2_foo | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 3
+(9 rows)
+
+SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass);
+ start_value | minimum_value | maximum_value | increment | cycle_option | cache_size
+-------------+----------------------+---------------+-----------+--------------+------------
+ -1 | -9223372036854775808 | -1 | -1 | f | 1
+(1 row)
-- Test comments
COMMENT ON SEQUENCE asdf IS 'won''t work';
@@ -524,6 +664,35 @@ SELECT lastval();
(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 relation sequence_test2
+ROLLBACK;
-- Sequences should get wiped out as well:
DROP TABLE serialTest, serialTest2;
-- Make sure sequences are gone:
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index df59f90f04..46c7cabb68 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -1,3 +1,24 @@
+--
+-- CREATE SEQUENCE
+--
+
+-- various error cases
+CREATE UNLOGGED SEQUENCE sequence_testx;
+CREATE SEQUENCE sequence_testx INCREMENT BY 0;
+CREATE SEQUENCE sequence_testx INCREMENT BY -1 MINVALUE 20;
+CREATE SEQUENCE sequence_testx INCREMENT BY 1 MAXVALUE -20;
+CREATE SEQUENCE sequence_testx INCREMENT BY -1 START 10;
+CREATE SEQUENCE sequence_testx INCREMENT BY 1 START -10;
+CREATE SEQUENCE sequence_testx CACHE 0;
+
+-- OWNED BY errors
+CREATE SEQUENCE sequence_testx OWNED BY nobody; -- nonsense word
+CREATE SEQUENCE sequence_testx OWNED BY pg_tables.tablename; -- not a table
+CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname; -- not same schema
+CREATE TABLE sequence_test_table (a int);
+CREATE SEQUENCE sequence_testx OWNED BY sequence_test_table.b; -- wrong column
+DROP TABLE sequence_test_table;
+
---
--- test creation of SERIAL column
---
@@ -120,43 +141,80 @@ CREATE TEMP TABLE t1 (
--
ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24
- INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+ INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+
+ALTER SEQUENCE pg_class CYCLE; -- error, not a sequence
CREATE SEQUENCE sequence_test2 START WITH 32;
+CREATE SEQUENCE sequence_test4 INCREMENT BY -1;
SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test4');
+ALTER SEQUENCE sequence_test2 RESTART;
+SELECT nextval('sequence_test2');
+
+ALTER SEQUENCE sequence_test2 RESTART WITH 0; -- error
+ALTER SEQUENCE sequence_test4 RESTART WITH 40; -- error
+
+-- test CYCLE and NO CYCLE
ALTER SEQUENCE sequence_test2 RESTART WITH 24
- INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+ INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
+SELECT nextval('sequence_test2');
SELECT nextval('sequence_test2');
SELECT nextval('sequence_test2');
SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2'); -- cycled
+
+ALTER SEQUENCE sequence_test2 RESTART WITH 24
+ NO CYCLE;
+SELECT nextval('sequence_test2');
SELECT nextval('sequence_test2');
SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2'); -- error
-ALTER SEQUENCE sequence_test2 RESTART;
+ALTER SEQUENCE sequence_test2 RESTART WITH -24 START WITH -24
+ INCREMENT BY -4 MINVALUE -36 MAXVALUE -5 CYCLE;
+SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2'); -- cycled
+ALTER SEQUENCE sequence_test2 RESTART WITH -24
+ NO CYCLE;
+SELECT nextval('sequence_test2');
SELECT nextval('sequence_test2');
SELECT nextval('sequence_test2');
SELECT nextval('sequence_test2');
+SELECT nextval('sequence_test2'); -- error
+-- 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
+SELECT setval('sequence_test2', 100); -- error
+SELECT setval('sequence_test2', 5);
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')
+SELECT * FROM information_schema.sequences
+ WHERE sequence_name ~ ANY(ARRAY['sequence_test', 'serialtest'])
ORDER BY sequence_name ASC;
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')
+WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])
ORDER BY sequencename ASC;
+
+SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass);
+
+
-- Test comments
COMMENT ON SEQUENCE asdf IS 'won''t work';
COMMENT ON SEQUENCE sequence_test2 IS 'will work';
@@ -264,6 +322,25 @@ CREATE SEQUENCE seq3;
SELECT lastval();
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);
+ROLLBACK TO save;
+GRANT UPDATE ON seq3 TO regress_seq_user;
+SELECT setval('seq3', 5);
+SELECT nextval('seq3');
+ROLLBACK;
+
+-- ALTER SEQUENCE
+BEGIN;
+SET LOCAL SESSION AUTHORIZATION regress_seq_user;
+ALTER SEQUENCE sequence_test2 START WITH 1;
+ROLLBACK;
+
-- Sequences should get wiped out as well:
DROP TABLE serialTest, serialTest2;
--
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
