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

Commitd002f16

Browse files
committed
Add a regression test script dedicated to exercising system views.
Quite a few of our built-in system views were not exercised anywherein the regression tests. This is perhaps not so exciting for the onesthat are simple projections/joins of system catalogs, but for the onesthat are wrappers for set-returning C functions, the omission translatesdirectly to lack of test coverage for those functions.In many cases, the reason for the omission is that the view doesn't havemuch to do with any specific SQL feature, so there's no natural place totest it. To remedy that, invent a new script sysviews.sql that's dedicatedto testing SRF-based views. Move a couple of tests that did fit thischarter into the new script, and add simple "count(*)" based tests ofother views within the charter. That's enough to ensure we at leastexercise the main code path through the SRF, although it does little toprove that the output is sane.More could be done here, no doubt, and I hope someone will think abouthow we can test these views more thoroughly. But this is a startingpoint.Discussion:https://postgr.es/m/19359.1485723741@sss.pgh.pa.us
1 parent511ae62 commitd002f16

File tree

8 files changed

+163
-70
lines changed

8 files changed

+163
-70
lines changed

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

Lines changed: 0 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,19 +1,3 @@
1-
SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%';
2-
name | setting
3-
----------------------+---------
4-
enable_bitmapscan | on
5-
enable_hashagg | on
6-
enable_hashjoin | on
7-
enable_indexonlyscan | on
8-
enable_indexscan | on
9-
enable_material | on
10-
enable_mergejoin | on
11-
enable_nestloop | on
12-
enable_seqscan | on
13-
enable_sort | on
14-
enable_tidscan | on
15-
(11 rows)
16-
171
CREATE TABLE foo2(fooid int, f2 int);
182
INSERT INTO foo2 VALUES(1, 11);
193
INSERT INTO foo2 VALUES(2, 22);
Lines changed: 113 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,113 @@
1+
--
2+
-- Test assorted system views
3+
--
4+
-- This test is mainly meant to provide some code coverage for the
5+
-- set-returning functions that underlie certain system views.
6+
-- The output of most of these functions is very environment-dependent,
7+
-- so our ability to test with fixed expected output is pretty limited;
8+
-- but even a trivial check of count(*) will exercise the normal code path
9+
-- through the SRF.
10+
select count(*) >= 0 as ok from pg_available_extension_versions;
11+
ok
12+
----
13+
t
14+
(1 row)
15+
16+
select count(*) >= 0 as ok from pg_available_extensions;
17+
ok
18+
----
19+
t
20+
(1 row)
21+
22+
-- At introduction, pg_config had 23 entries; it may grow
23+
select count(*) > 20 as ok from pg_config;
24+
ok
25+
----
26+
t
27+
(1 row)
28+
29+
-- We expect no cursors in this test; see also portals.sql
30+
select count(*) = 0 as ok from pg_cursors;
31+
ok
32+
----
33+
t
34+
(1 row)
35+
36+
select count(*) >= 0 as ok from pg_file_settings;
37+
ok
38+
----
39+
t
40+
(1 row)
41+
42+
-- There will surely be at least one active lock
43+
select count(*) > 0 as ok from pg_locks;
44+
ok
45+
----
46+
t
47+
(1 row)
48+
49+
-- We expect no prepared statements in this test; see also prepare.sql
50+
select count(*) = 0 as ok from pg_prepared_statements;
51+
ok
52+
----
53+
t
54+
(1 row)
55+
56+
-- See also prepared_xacts.sql
57+
select count(*) >= 0 as ok from pg_prepared_xacts;
58+
ok
59+
----
60+
t
61+
(1 row)
62+
63+
-- This is to record the prevailing planner enable_foo settings during
64+
-- a regression test run.
65+
select name, setting from pg_settings where name like 'enable%';
66+
name | setting
67+
----------------------+---------
68+
enable_bitmapscan | on
69+
enable_hashagg | on
70+
enable_hashjoin | on
71+
enable_indexonlyscan | on
72+
enable_indexscan | on
73+
enable_material | on
74+
enable_mergejoin | on
75+
enable_nestloop | on
76+
enable_seqscan | on
77+
enable_sort | on
78+
enable_tidscan | on
79+
(11 rows)
80+
81+
-- Test that the pg_timezone_names and pg_timezone_abbrevs views are
82+
-- more-or-less working. We can't test their contents in any great detail
83+
-- without the outputs changing anytime IANA updates the underlying data,
84+
-- but it seems reasonable to expect at least one entry per major meridian.
85+
-- (At the time of writing, the actual counts are around 38 because of
86+
-- zones using fractional GMT offsets, so this is a pretty loose test.)
87+
select count(distinct utc_offset) >= 24 as ok from pg_timezone_names;
88+
ok
89+
----
90+
t
91+
(1 row)
92+
93+
select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
94+
ok
95+
----
96+
t
97+
(1 row)
98+
99+
-- Let's check the non-default timezone abbreviation sets, too
100+
set timezone_abbreviations = 'Australia';
101+
select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
102+
ok
103+
----
104+
t
105+
(1 row)
106+
107+
set timezone_abbreviations = 'India';
108+
select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
109+
ok
110+
----
111+
t
112+
(1 row)
113+

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

Lines changed: 0 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -2603,41 +2603,6 @@ SELECT '2007-12-09 07:30:00 UTC'::timestamptz AT TIME ZONE 'VET';
26032603
Sun Dec 09 03:00:00 2007
26042604
(1 row)
26052605

2606-
--
2607-
-- Test that the pg_timezone_names and pg_timezone_abbrevs views are
2608-
-- more-or-less working. We can't test their contents in any great detail
2609-
-- without the outputs changing anytime IANA updates the underlying data,
2610-
-- but it seems reasonable to expect at least one entry per major meridian.
2611-
-- (At the time of writing, the actual counts are around 38 because of
2612-
-- zones using fractional GMT offsets, so this is a pretty loose test.)
2613-
--
2614-
select count(distinct utc_offset) >= 24 as ok from pg_timezone_names;
2615-
ok
2616-
----
2617-
t
2618-
(1 row)
2619-
2620-
select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
2621-
ok
2622-
----
2623-
t
2624-
(1 row)
2625-
2626-
-- Let's check the non-default timezone abbreviation sets, too
2627-
set timezone_abbreviations = 'Australia';
2628-
select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
2629-
ok
2630-
----
2631-
t
2632-
(1 row)
2633-
2634-
set timezone_abbreviations = 'India';
2635-
select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
2636-
ok
2637-
----
2638-
t
2639-
(1 row)
2640-
26412606
--
26422607
-- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)
26432608
--

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -89,7 +89,7 @@ test: brin gin gist spgist privileges init_privs security_label collate matview
8989
# ----------
9090
# Another group of parallel tests
9191
# ----------
92-
test: alter_generic alter_operator misc psql async dbsize misc_functions tsrf
92+
test: alter_generic alter_operator misc psql async dbsize misc_functionssysviewstsrf
9393

9494
# rules cannot run concurrently with any test that creates a view
9595
test: rules psql_crosstab amutils

‎src/test/regress/serial_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -123,6 +123,7 @@ test: psql
123123
test: async
124124
test: dbsize
125125
test: misc_functions
126+
test: sysviews
126127
test: tsrf
127128
test: rules
128129
test: psql_crosstab

‎src/test/regress/sql/rangefuncs.sql

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
SELECT name, settingFROM pg_settingsWHERE nameLIKE'enable%';
2-
31
CREATETABLEfoo2(fooidint, f2int);
42
INSERT INTO foo2VALUES(1,11);
53
INSERT INTO foo2VALUES(2,22);

‎src/test/regress/sql/sysviews.sql

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,48 @@
1+
--
2+
-- Test assorted system views
3+
--
4+
-- This test is mainly meant to provide some code coverage for the
5+
-- set-returning functions that underlie certain system views.
6+
-- The output of most of these functions is very environment-dependent,
7+
-- so our ability to test with fixed expected output is pretty limited;
8+
-- but even a trivial check of count(*) will exercise the normal code path
9+
-- through the SRF.
10+
11+
selectcount(*)>=0as okfrom pg_available_extension_versions;
12+
13+
selectcount(*)>=0as okfrom pg_available_extensions;
14+
15+
-- At introduction, pg_config had 23 entries; it may grow
16+
selectcount(*)>20as okfrom pg_config;
17+
18+
-- We expect no cursors in this test; see also portals.sql
19+
selectcount(*)=0as okfrom pg_cursors;
20+
21+
selectcount(*)>=0as okfrom pg_file_settings;
22+
23+
-- There will surely be at least one active lock
24+
selectcount(*)>0as okfrom pg_locks;
25+
26+
-- We expect no prepared statements in this test; see also prepare.sql
27+
selectcount(*)=0as okfrom pg_prepared_statements;
28+
29+
-- See also prepared_xacts.sql
30+
selectcount(*)>=0as okfrom pg_prepared_xacts;
31+
32+
-- This is to record the prevailing planner enable_foo settings during
33+
-- a regression test run.
34+
select name, settingfrom pg_settingswhere namelike'enable%';
35+
36+
-- Test that the pg_timezone_names and pg_timezone_abbrevs views are
37+
-- more-or-less working. We can't test their contents in any great detail
38+
-- without the outputs changing anytime IANA updates the underlying data,
39+
-- but it seems reasonable to expect at least one entry per major meridian.
40+
-- (At the time of writing, the actual counts are around 38 because of
41+
-- zones using fractional GMT offsets, so this is a pretty loose test.)
42+
selectcount(distinct utc_offset)>=24as okfrom pg_timezone_names;
43+
selectcount(distinct utc_offset)>=24as okfrom pg_timezone_abbrevs;
44+
-- Let's check the non-default timezone abbreviation sets, too
45+
set timezone_abbreviations='Australia';
46+
selectcount(distinct utc_offset)>=24as okfrom pg_timezone_abbrevs;
47+
set timezone_abbreviations='India';
48+
selectcount(distinct utc_offset)>=24as okfrom pg_timezone_abbrevs;

‎src/test/regress/sql/timestamptz.sql

Lines changed: 0 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -469,22 +469,6 @@ SELECT '2007-12-09 07:00:01 UTC'::timestamptz AT TIME ZONE 'VET';
469469
SELECT'2007-12-09 07:29:59 UTC'::timestamptz ATTIME ZONE'VET';
470470
SELECT'2007-12-09 07:30:00 UTC'::timestamptz ATTIME ZONE'VET';
471471

472-
--
473-
-- Test that the pg_timezone_names and pg_timezone_abbrevs views are
474-
-- more-or-less working. We can't test their contents in any great detail
475-
-- without the outputs changing anytime IANA updates the underlying data,
476-
-- but it seems reasonable to expect at least one entry per major meridian.
477-
-- (At the time of writing, the actual counts are around 38 because of
478-
-- zones using fractional GMT offsets, so this is a pretty loose test.)
479-
--
480-
selectcount(distinct utc_offset)>=24as okfrom pg_timezone_names;
481-
selectcount(distinct utc_offset)>=24as okfrom pg_timezone_abbrevs;
482-
-- Let's check the non-default timezone abbreviation sets, too
483-
set timezone_abbreviations='Australia';
484-
selectcount(distinct utc_offset)>=24as okfrom pg_timezone_abbrevs;
485-
set timezone_abbreviations='India';
486-
selectcount(distinct utc_offset)>=24as okfrom pg_timezone_abbrevs;
487-
488472
--
489473
-- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504)
490474
--

0 commit comments

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