Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 824 insertions, 0 deletions

diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
new file mode 100644
index 0000000..8b928b2
--- /dev/null
+++ b/src/test/regress/expected/sequence.out
@@ -0,0 +1,824 @@
+--
+-- 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_class_oid_index.oid;  -- not a table
+ERROR:  referenced relation "pg_class_oid_index" 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;
+-- sequence data types
+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;
+                                          ^
+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
+---
+CREATE TABLE serialTest1 (f1 text, f2 serial);
+INSERT INTO serialTest1 VALUES ('foo');
+INSERT INTO serialTest1 VALUES ('bar');
+INSERT INTO serialTest1 VALUES ('force', 100);
+INSERT INTO serialTest1 VALUES ('wrong', NULL);
+ERROR:  null value in column "f2" of relation "serialtest1" violates not-null constraint
+DETAIL:  Failing row contains (wrong, null).
+SELECT * FROM serialTest1;
+  f1   | f2  
+-------+-----
+ foo   |   1
+ bar   |   2
+ force | 100
+(3 rows)
+
+SELECT pg_get_serial_sequence('serialTest1', 'f2');
+  pg_get_serial_sequence   
+---------------------------
+ public.serialtest1_f2_seq
+(1 row)
+
+-- 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)
+
+-- 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;
+-- renaming serial sequences
+ALTER TABLE serialtest1_f2_seq RENAME TO serialtest1_f2_foo;
+INSERT INTO serialTest1 VALUES ('more');
+SELECT * FROM serialTest1;
+  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 value for column f1 of table t1 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 value for column f2 of table t1 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
+ALTER SEQUENCE serialTest1 CYCLE;  -- error, not a sequence
+ERROR:  "serialtest1" 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;
+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');  -- 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 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 
+---------
+     -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 ~ 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_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
+(19 rows)
+
+SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
+FROM pg_sequences
+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
+(19 rows)
+
+SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass);
+ start_value |    minimum_value     | maximum_value | increment | cycle_option | cache_size | data_type 
+-------------+----------------------+---------------+-----------+--------------+------------+-----------
+          -1 | -9223372036854775808 |            -1 |        -1 | f            |          1 |        20
+(1 row)
+
+\d sequence_test4
+                       Sequence "public.sequence_test4"
+  Type  | Start |       Minimum        | Maximum | Increment | Cycles? | Cache 
+--------+-------+----------------------+---------+-----------+---------+-------
+ bigint |    -1 | -9223372036854775808 |      -1 |        -1 | no      |     1
+
+\d serialtest2_f2_seq
+                 Sequence "public.serialtest2_f2_seq"
+  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
+---------+-------+---------+------------+-----------+---------+-------
+ integer |     1 |       1 | 2147483647 |         1 | no      |     1
+Owned by: public.serialtest2.f2
+
+-- 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
+-- Test sequences in read-only transactions
+CREATE TEMPORARY SEQUENCE sequence_test_temp1;
+START TRANSACTION READ ONLY;
+SELECT nextval('sequence_test_temp1');  -- ok
+ nextval 
+---------
+       1
+(1 row)
+
+SELECT nextval('sequence_test2');  -- error
+ERROR:  cannot execute nextval() in a read-only transaction
+ROLLBACK;
+START TRANSACTION READ ONLY;
+SELECT setval('sequence_test_temp1', 1);  -- ok
+ setval 
+--------
+      1
+(1 row)
+
+SELECT setval('sequence_test2', 1);  -- error
+ERROR:  cannot execute setval() in a read-only transaction
+ROLLBACK;
+-- privileges tests
+CREATE USER regress_seq_user;
+-- 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;
+-- 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 sequence sequence_test2
+ROLLBACK;
+-- Sequences should get wiped out as well:
+DROP TABLE serialTest1, 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;