Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit431ab0e

Browse files
committed
Add some regression tests for serial pseudotypes.
Josh Kupershmidt
1 parent61307dc commit431ab0e

File tree

2 files changed

+162
-6
lines changed

2 files changed

+162
-6
lines changed

‎src/test/regress/expected/sequence.out‎

Lines changed: 103 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,84 @@ SELECT * FROM serialTest;
1616
force | 100
1717
(3 rows)
1818

19+
-- test smallserial / bigserial
20+
CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2,
21+
f5 bigserial, f6 serial8);
22+
NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f2_seq" for serial column "serialtest2.f2"
23+
NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f3_seq" for serial column "serialtest2.f3"
24+
NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f4_seq" for serial column "serialtest2.f4"
25+
NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f5_seq" for serial column "serialtest2.f5"
26+
NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f6_seq" for serial column "serialtest2.f6"
27+
INSERT INTO serialTest2 (f1)
28+
VALUES ('test_defaults');
29+
INSERT INTO serialTest2 (f1, f2, f3, f4, f5, f6)
30+
VALUES ('test_max_vals', 2147483647, 32767, 32767, 9223372036854775807,
31+
9223372036854775807),
32+
('test_min_vals', -2147483648, -32768, -32768, -9223372036854775808,
33+
-9223372036854775808);
34+
-- All these INSERTs should fail:
35+
INSERT INTO serialTest2 (f1, f3)
36+
VALUES ('bogus', -32769);
37+
ERROR: smallint out of range
38+
INSERT INTO serialTest2 (f1, f4)
39+
VALUES ('bogus', -32769);
40+
ERROR: smallint out of range
41+
INSERT INTO serialTest2 (f1, f3)
42+
VALUES ('bogus', 32768);
43+
ERROR: smallint out of range
44+
INSERT INTO serialTest2 (f1, f4)
45+
VALUES ('bogus', 32768);
46+
ERROR: smallint out of range
47+
INSERT INTO serialTest2 (f1, f5)
48+
VALUES ('bogus', -9223372036854775809);
49+
ERROR: bigint out of range
50+
INSERT INTO serialTest2 (f1, f6)
51+
VALUES ('bogus', -9223372036854775809);
52+
ERROR: bigint out of range
53+
INSERT INTO serialTest2 (f1, f5)
54+
VALUES ('bogus', 9223372036854775808);
55+
ERROR: bigint out of range
56+
INSERT INTO serialTest2 (f1, f6)
57+
VALUES ('bogus', 9223372036854775808);
58+
ERROR: bigint out of range
59+
SELECT * FROM serialTest2 ORDER BY f2 ASC;
60+
f1 | f2 | f3 | f4 | f5 | f6
61+
---------------+-------------+--------+--------+----------------------+----------------------
62+
test_min_vals | -2147483648 | -32768 | -32768 | -9223372036854775808 | -9223372036854775808
63+
test_defaults | 1 | 1 | 1 | 1 | 1
64+
test_max_vals | 2147483647 | 32767 | 32767 | 9223372036854775807 | 9223372036854775807
65+
(3 rows)
66+
67+
SELECT nextval('serialTest2_f2_seq');
68+
nextval
69+
---------
70+
2
71+
(1 row)
72+
73+
SELECT nextval('serialTest2_f3_seq');
74+
nextval
75+
---------
76+
2
77+
(1 row)
78+
79+
SELECT nextval('serialTest2_f4_seq');
80+
nextval
81+
---------
82+
2
83+
(1 row)
84+
85+
SELECT nextval('serialTest2_f5_seq');
86+
nextval
87+
---------
88+
2
89+
(1 row)
90+
91+
SELECT nextval('serialTest2_f6_seq');
92+
nextval
93+
---------
94+
2
95+
(1 row)
96+
1997
-- basic sequence operations using both text and oid references
2098
CREATE SEQUENCE sequence_test;
2199
SELECT nextval('sequence_test'::text);
@@ -221,11 +299,19 @@ SELECT nextval('sequence_test2');
221299
(1 row)
222300

223301
-- Information schema
224-
SELECT * FROM information_schema.sequences WHERE sequence_name IN ('sequence_test2');
225-
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
226-
------------------+-----------------+----------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------+-----------+--------------
227-
regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES
228-
(1 row)
302+
SELECT * FROM information_schema.sequences WHERE sequence_name IN
303+
('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
304+
'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
305+
ORDER BY sequence_name ASC;
306+
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
307+
------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
308+
regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES
309+
regression | public | serialtest2_f2_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
310+
regression | public | serialtest2_f3_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
311+
regression | public | serialtest2_f4_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
312+
regression | public | serialtest2_f5_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
313+
regression | public | serialtest2_f6_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
314+
(6 rows)
229315

230316
-- Test comments
231317
COMMENT ON SEQUENCE asdf IS 'won''t work';
@@ -289,5 +375,17 @@ REVOKE ALL ON seq3 FROM seq_user;
289375
SELECT lastval();
290376
ERROR: permission denied for sequence seq3
291377
ROLLBACK;
378+
-- Sequences should get wiped out as well:
379+
DROP TABLE serialTest, serialTest2;
380+
-- Make sure sequences are gone:
381+
SELECT * FROM information_schema.sequences WHERE sequence_name IN
382+
('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
383+
'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
384+
ORDER BY sequence_name ASC;
385+
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
386+
------------------+-----------------+----------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------+-----------+--------------
387+
regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES
388+
(1 row)
389+
292390
DROP USER seq_user;
293391
DROP SEQUENCE seq;

‎src/test/regress/sql/sequence.sql‎

Lines changed: 59 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,52 @@ INSERT INTO serialTest VALUES ('wrong', NULL);
1111

1212
SELECT*FROM serialTest;
1313

14+
-- test smallserial / bigserial
15+
CREATETABLEserialTest2 (f1text, f2serial, f3 smallserial, f4 serial2,
16+
f5bigserial, f6 serial8);
17+
18+
INSERT INTO serialTest2 (f1)
19+
VALUES ('test_defaults');
20+
21+
INSERT INTO serialTest2 (f1, f2, f3, f4, f5, f6)
22+
VALUES ('test_max_vals',2147483647,32767,32767,9223372036854775807,
23+
9223372036854775807),
24+
('test_min_vals',-2147483648,-32768,-32768,-9223372036854775808,
25+
-9223372036854775808);
26+
27+
-- All these INSERTs should fail:
28+
INSERT INTO serialTest2 (f1, f3)
29+
VALUES ('bogus',-32769);
30+
31+
INSERT INTO serialTest2 (f1, f4)
32+
VALUES ('bogus',-32769);
33+
34+
INSERT INTO serialTest2 (f1, f3)
35+
VALUES ('bogus',32768);
36+
37+
INSERT INTO serialTest2 (f1, f4)
38+
VALUES ('bogus',32768);
39+
40+
INSERT INTO serialTest2 (f1, f5)
41+
VALUES ('bogus',-9223372036854775809);
42+
43+
INSERT INTO serialTest2 (f1, f6)
44+
VALUES ('bogus',-9223372036854775809);
45+
46+
INSERT INTO serialTest2 (f1, f5)
47+
VALUES ('bogus',9223372036854775808);
48+
49+
INSERT INTO serialTest2 (f1, f6)
50+
VALUES ('bogus',9223372036854775808);
51+
52+
SELECT*FROM serialTest2ORDER BY f2ASC;
53+
54+
SELECT nextval('serialTest2_f2_seq');
55+
SELECT nextval('serialTest2_f3_seq');
56+
SELECT nextval('serialTest2_f4_seq');
57+
SELECT nextval('serialTest2_f5_seq');
58+
SELECT nextval('serialTest2_f6_seq');
59+
1460
-- basic sequence operations using both text and oid references
1561
CREATESEQUENCEsequence_test;
1662

@@ -86,7 +132,10 @@ SELECT nextval('sequence_test2');
86132
SELECT nextval('sequence_test2');
87133

88134
-- Information schema
89-
SELECT*FROMinformation_schema.sequencesWHERE sequence_nameIN ('sequence_test2');
135+
SELECT*FROMinformation_schema.sequencesWHERE sequence_nameIN
136+
('sequence_test2','serialtest2_f2_seq','serialtest2_f3_seq',
137+
'serialtest2_f4_seq','serialtest2_f5_seq','serialtest2_f6_seq')
138+
ORDER BY sequence_nameASC;
90139

91140
-- Test comments
92141
COMMENT ON SEQUENCE asdf IS'won''t work';
@@ -118,5 +167,14 @@ REVOKE ALL ON seq3 FROM seq_user;
118167
SELECT lastval();
119168
ROLLBACK;
120169

170+
-- Sequences should get wiped out as well:
171+
DROPTABLE serialTest, serialTest2;
172+
173+
-- Make sure sequences are gone:
174+
SELECT*FROMinformation_schema.sequencesWHERE sequence_nameIN
175+
('sequence_test2','serialtest2_f2_seq','serialtest2_f3_seq',
176+
'serialtest2_f4_seq','serialtest2_f5_seq','serialtest2_f6_seq')
177+
ORDER BY sequence_nameASC;
178+
121179
DROPUSER seq_user;
122180
DROPSEQUENCE seq;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp