Adjust min/max values when changing sequence type

When changing the type of a sequence, adjust the min/max values of the
sequence if it looks like the previous values were the default values.
Previously, it would leave the old values in place, requiring manual
adjustments even in the usual/default cases.

Reviewed-by: Michael Paquier <michael.paquier@gmail.com>
Reviewed-by: Vitaly Burovoy <vitaly.burovoy@gmail.com>

2 files changed, 54 insertions, 8 deletions

diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index f3394891513..1d8d02b8002 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -32,19 +32,35 @@ DROP TABLE sequence_test_table;
 CREATE SEQUENCE sequence_test5 AS integer;
 CREATE SEQUENCE sequence_test6 AS smallint;
 CREATE SEQUENCE sequence_test7 AS bigint;
+CREATE SEQUENCE sequence_test8 AS integer MAXVALUE 100000;
+CREATE SEQUENCE sequence_test9 AS integer INCREMENT BY -1;
+CREATE SEQUENCE sequence_test10 AS integer MINVALUE -100000 START 1;
+CREATE SEQUENCE sequence_test11 AS smallint;
+CREATE SEQUENCE sequence_test12 AS smallint INCREMENT -1;
+CREATE SEQUENCE sequence_test13 AS smallint MINVALUE -32768;
+CREATE SEQUENCE sequence_test14 AS smallint MAXVALUE 32767 INCREMENT -1;
 CREATE SEQUENCE sequence_testx AS text;
 ERROR:  sequence type must be smallint, integer, or bigint
 CREATE SEQUENCE sequence_testx AS nosuchtype;
 ERROR:  type "nosuchtype" does not exist
 LINE 1: CREATE SEQUENCE sequence_testx AS nosuchtype;
                                           ^
-ALTER SEQUENCE sequence_test5 AS smallint;  -- fails
-ERROR:  MAXVALUE (2147483647) is out of range for sequence data type smallint
-ALTER SEQUENCE sequence_test5 AS smallint NO MINVALUE NO MAXVALUE;
 CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
 ERROR:  MAXVALUE (100000) is out of range for sequence data type smallint
 CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
 ERROR:  MINVALUE (-100000) is out of range for sequence data type smallint
+ALTER SEQUENCE sequence_test5 AS smallint;  -- success, max will be adjusted
+ALTER SEQUENCE sequence_test8 AS smallint;  -- fail, max has to be adjusted
+ERROR:  MAXVALUE (100000) is out of range for sequence data type smallint
+ALTER SEQUENCE sequence_test8 AS smallint MAXVALUE 20000;  -- ok now
+ALTER SEQUENCE sequence_test9 AS smallint;  -- success, min will be adjusted
+ALTER SEQUENCE sequence_test10 AS smallint;  -- fail, min has to be adjusted
+ERROR:  MINVALUE (-100000) is out of range for sequence data type smallint
+ALTER SEQUENCE sequence_test10 AS smallint MINVALUE -20000;  -- ok now
+ALTER SEQUENCE sequence_test11 AS int;  -- max will be adjusted
+ALTER SEQUENCE sequence_test12 AS int;  -- min will be adjusted
+ALTER SEQUENCE sequence_test13 AS int;  -- min and max will be adjusted
+ALTER SEQUENCE sequence_test14 AS int;  -- min and max will be adjusted
 ---
 --- test creation of SERIAL column
 ---
@@ -459,19 +475,26 @@ SELECT * FROM information_schema.sequences
   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_test10    | smallint  |                16 |                       2 |             0 | 1           | -20000               | 32767               | 1         | NO
+ regression       | public          | sequence_test11    | integer   |                32 |                       2 |             0 | 1           | 1                    | 2147483647          | 1         | NO
+ regression       | public          | sequence_test12    | integer   |                32 |                       2 |             0 | -1          | -2147483648          | -1                  | -1        | NO
+ regression       | public          | sequence_test13    | integer   |                32 |                       2 |             0 | -32768      | -2147483648          | 2147483647          | 1         | NO
+ regression       | public          | sequence_test14    | integer   |                32 |                       2 |             0 | 32767       | -2147483648          | 2147483647          | -1        | NO
  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          | sequence_test5     | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
  regression       | public          | sequence_test6     | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
  regression       | public          | sequence_test7     | bigint    |                64 |                       2 |             0 | 1           | 1                    | 9223372036854775807 | 1         | NO
+ regression       | public          | sequence_test8     | smallint  |                16 |                       2 |             0 | 1           | 1                    | 20000               | 1         | NO
+ regression       | public          | sequence_test9     | smallint  |                16 |                       2 |             0 | -1          | -32768               | -1                  | -1        | NO
  regression       | public          | serialtest1_f2_foo | integer   |                32 |                       2 |             0 | 1           | 1                    | 2147483647          | 1         | NO
  regression       | public          | serialtest2_f2_seq | integer   |                32 |                       2 |             0 | 1           | 1                    | 2147483647          | 1         | NO
  regression       | public          | serialtest2_f3_seq | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 1         | NO
  regression       | public          | serialtest2_f4_seq | smallint  |                16 |                       2 |             0 | 1           | 1                    | 32767               | 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
-(12 rows)
+(19 rows)
 
 SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
 FROM pg_sequences
@@ -479,19 +502,26 @@ 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_test10    |           1 |               -20000 |               32767 |            1 | f     |          1 |           
+ public     | sequence_test11    |           1 |                    1 |          2147483647 |            1 | f     |          1 |           
+ public     | sequence_test12    |          -1 |          -2147483648 |                  -1 |           -1 | f     |          1 |           
+ public     | sequence_test13    |      -32768 |          -2147483648 |          2147483647 |            1 | f     |          1 |           
+ public     | sequence_test14    |       32767 |          -2147483648 |          2147483647 |           -1 | f     |          1 |           
  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     | sequence_test5     |           1 |                    1 |               32767 |            1 | f     |          1 |           
  public     | sequence_test6     |           1 |                    1 |               32767 |            1 | f     |          1 |           
  public     | sequence_test7     |           1 |                    1 | 9223372036854775807 |            1 | f     |          1 |           
+ public     | sequence_test8     |           1 |                    1 |               20000 |            1 | f     |          1 |           
+ public     | sequence_test9     |          -1 |               -32768 |                  -1 |           -1 | f     |          1 |           
  public     | serialtest1_f2_foo |           1 |                    1 |          2147483647 |            1 | f     |          1 |          3
  public     | serialtest2_f2_seq |           1 |                    1 |          2147483647 |            1 | f     |          1 |          2
  public     | serialtest2_f3_seq |           1 |                    1 |               32767 |            1 | f     |          1 |          2
  public     | serialtest2_f4_seq |           1 |                    1 |               32767 |            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
-(12 rows)
+(19 rows)
 
 SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass);
  start_value |    minimum_value     | maximum_value | increment | cycle_option | cache_size | data_type 
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 0fbd255967e..74663d7351e 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -23,15 +23,31 @@ DROP TABLE sequence_test_table;
 CREATE SEQUENCE sequence_test5 AS integer;
 CREATE SEQUENCE sequence_test6 AS smallint;
 CREATE SEQUENCE sequence_test7 AS bigint;
+CREATE SEQUENCE sequence_test8 AS integer MAXVALUE 100000;
+CREATE SEQUENCE sequence_test9 AS integer INCREMENT BY -1;
+CREATE SEQUENCE sequence_test10 AS integer MINVALUE -100000 START 1;
+CREATE SEQUENCE sequence_test11 AS smallint;
+CREATE SEQUENCE sequence_test12 AS smallint INCREMENT -1;
+CREATE SEQUENCE sequence_test13 AS smallint MINVALUE -32768;
+CREATE SEQUENCE sequence_test14 AS smallint MAXVALUE 32767 INCREMENT -1;
 CREATE SEQUENCE sequence_testx AS text;
 CREATE SEQUENCE sequence_testx AS nosuchtype;
 
-ALTER SEQUENCE sequence_test5 AS smallint;  -- fails
-ALTER SEQUENCE sequence_test5 AS smallint NO MINVALUE NO MAXVALUE;
-
 CREATE SEQUENCE sequence_testx AS smallint MAXVALUE 100000;
 CREATE SEQUENCE sequence_testx AS smallint MINVALUE -100000;
 
+ALTER SEQUENCE sequence_test5 AS smallint;  -- success, max will be adjusted
+ALTER SEQUENCE sequence_test8 AS smallint;  -- fail, max has to be adjusted
+ALTER SEQUENCE sequence_test8 AS smallint MAXVALUE 20000;  -- ok now
+ALTER SEQUENCE sequence_test9 AS smallint;  -- success, min will be adjusted
+ALTER SEQUENCE sequence_test10 AS smallint;  -- fail, min has to be adjusted
+ALTER SEQUENCE sequence_test10 AS smallint MINVALUE -20000;  -- ok now
+
+ALTER SEQUENCE sequence_test11 AS int;  -- max will be adjusted
+ALTER SEQUENCE sequence_test12 AS int;  -- min will be adjusted
+ALTER SEQUENCE sequence_test13 AS int;  -- min and max will be adjusted
+ALTER SEQUENCE sequence_test14 AS int;  -- min and max will be adjusted
+
 ---
 --- test creation of SERIAL column
 ---