From d002f16c6ec38f76d1ee97367ba6af3000d441d0 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 30 Jan 2017 17:15:42 -0500 Subject: [PATCH] Add a regression test script dedicated to exercising system views. Quite a few of our built-in system views were not exercised anywhere in the regression tests. This is perhaps not so exciting for the ones that are simple projections/joins of system catalogs, but for the ones that are wrappers for set-returning C functions, the omission translates directly to lack of test coverage for those functions. In many cases, the reason for the omission is that the view doesn't have much to do with any specific SQL feature, so there's no natural place to test it. To remedy that, invent a new script sysviews.sql that's dedicated to testing SRF-based views. Move a couple of tests that did fit this charter into the new script, and add simple "count(*)" based tests of other views within the charter. That's enough to ensure we at least exercise the main code path through the SRF, although it does little to prove that the output is sane. More could be done here, no doubt, and I hope someone will think about how we can test these views more thoroughly. But this is a starting point. Discussion: https://postgr.es/m/19359.1485723741@sss.pgh.pa.us --- src/test/regress/expected/rangefuncs.out | 16 --- src/test/regress/expected/sysviews.out | 113 ++++++++++++++++++++++ src/test/regress/expected/timestamptz.out | 35 ------- src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/rangefuncs.sql | 2 - src/test/regress/sql/sysviews.sql | 48 +++++++++ src/test/regress/sql/timestamptz.sql | 16 --- 8 files changed, 163 insertions(+), 70 deletions(-) create mode 100644 src/test/regress/expected/sysviews.out create mode 100644 src/test/regress/sql/sysviews.sql diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out index 56481de5c3..526a4aed0a 100644 --- a/src/test/regress/expected/rangefuncs.out +++ b/src/test/regress/expected/rangefuncs.out @@ -1,19 +1,3 @@ -SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%'; - name | setting -----------------------+--------- - enable_bitmapscan | on - enable_hashagg | on - enable_hashjoin | on - enable_indexonlyscan | on - enable_indexscan | on - enable_material | on - enable_mergejoin | on - enable_nestloop | on - enable_seqscan | on - enable_sort | on - enable_tidscan | on -(11 rows) - CREATE TABLE foo2(fooid int, f2 int); INSERT INTO foo2 VALUES(1, 11); INSERT INTO foo2 VALUES(2, 22); diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out new file mode 100644 index 0000000000..852a7c347e --- /dev/null +++ b/src/test/regress/expected/sysviews.out @@ -0,0 +1,113 @@ +-- +-- Test assorted system views +-- +-- This test is mainly meant to provide some code coverage for the +-- set-returning functions that underlie certain system views. +-- The output of most of these functions is very environment-dependent, +-- so our ability to test with fixed expected output is pretty limited; +-- but even a trivial check of count(*) will exercise the normal code path +-- through the SRF. +select count(*) >= 0 as ok from pg_available_extension_versions; + ok +---- + t +(1 row) + +select count(*) >= 0 as ok from pg_available_extensions; + ok +---- + t +(1 row) + +-- At introduction, pg_config had 23 entries; it may grow +select count(*) > 20 as ok from pg_config; + ok +---- + t +(1 row) + +-- We expect no cursors in this test; see also portals.sql +select count(*) = 0 as ok from pg_cursors; + ok +---- + t +(1 row) + +select count(*) >= 0 as ok from pg_file_settings; + ok +---- + t +(1 row) + +-- There will surely be at least one active lock +select count(*) > 0 as ok from pg_locks; + ok +---- + t +(1 row) + +-- We expect no prepared statements in this test; see also prepare.sql +select count(*) = 0 as ok from pg_prepared_statements; + ok +---- + t +(1 row) + +-- See also prepared_xacts.sql +select count(*) >= 0 as ok from pg_prepared_xacts; + ok +---- + t +(1 row) + +-- This is to record the prevailing planner enable_foo settings during +-- a regression test run. +select name, setting from pg_settings where name like 'enable%'; + name | setting +----------------------+--------- + enable_bitmapscan | on + enable_hashagg | on + enable_hashjoin | on + enable_indexonlyscan | on + enable_indexscan | on + enable_material | on + enable_mergejoin | on + enable_nestloop | on + enable_seqscan | on + enable_sort | on + enable_tidscan | on +(11 rows) + +-- Test that the pg_timezone_names and pg_timezone_abbrevs views are +-- more-or-less working. We can't test their contents in any great detail +-- without the outputs changing anytime IANA updates the underlying data, +-- but it seems reasonable to expect at least one entry per major meridian. +-- (At the time of writing, the actual counts are around 38 because of +-- zones using fractional GMT offsets, so this is a pretty loose test.) +select count(distinct utc_offset) >= 24 as ok from pg_timezone_names; + ok +---- + t +(1 row) + +select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; + ok +---- + t +(1 row) + +-- Let's check the non-default timezone abbreviation sets, too +set timezone_abbreviations = 'Australia'; +select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; + ok +---- + t +(1 row) + +set timezone_abbreviations = 'India'; +select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; + ok +---- + t +(1 row) + diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index 51d4d21157..69d3965c60 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -2603,41 +2603,6 @@ SELECT '2007-12-09 07:30:00 UTC'::timestamptz AT TIME ZONE 'VET'; Sun Dec 09 03:00:00 2007 (1 row) --- --- Test that the pg_timezone_names and pg_timezone_abbrevs views are --- more-or-less working. We can't test their contents in any great detail --- without the outputs changing anytime IANA updates the underlying data, --- but it seems reasonable to expect at least one entry per major meridian. --- (At the time of writing, the actual counts are around 38 because of --- zones using fractional GMT offsets, so this is a pretty loose test.) --- -select count(distinct utc_offset) >= 24 as ok from pg_timezone_names; - ok ----- - t -(1 row) - -select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; - ok ----- - t -(1 row) - --- Let's check the non-default timezone abbreviation sets, too -set timezone_abbreviations = 'Australia'; -select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; - ok ----- - t -(1 row) - -set timezone_abbreviations = 'India'; -select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; - ok ----- - t -(1 row) - -- -- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504) -- diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index e9b2bad6fd..edeb2d6bc7 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -89,7 +89,7 @@ test: brin gin gist spgist privileges init_privs security_label collate matview # ---------- # Another group of parallel tests # ---------- -test: alter_generic alter_operator misc psql async dbsize misc_functions tsrf +test: alter_generic alter_operator misc psql async dbsize misc_functions sysviews tsrf # rules cannot run concurrently with any test that creates a view test: rules psql_crosstab amutils diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 7cdc0f6a69..27a46d76d5 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -123,6 +123,7 @@ test: psql test: async test: dbsize test: misc_functions +test: sysviews test: tsrf test: rules test: psql_crosstab diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sql index c8edc553bc..09ac8fbdb4 100644 --- a/src/test/regress/sql/rangefuncs.sql +++ b/src/test/regress/sql/rangefuncs.sql @@ -1,5 +1,3 @@ -SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%'; - CREATE TABLE foo2(fooid int, f2 int); INSERT INTO foo2 VALUES(1, 11); INSERT INTO foo2 VALUES(2, 22); diff --git a/src/test/regress/sql/sysviews.sql b/src/test/regress/sql/sysviews.sql new file mode 100644 index 0000000000..0941b6beac --- /dev/null +++ b/src/test/regress/sql/sysviews.sql @@ -0,0 +1,48 @@ +-- +-- Test assorted system views +-- +-- This test is mainly meant to provide some code coverage for the +-- set-returning functions that underlie certain system views. +-- The output of most of these functions is very environment-dependent, +-- so our ability to test with fixed expected output is pretty limited; +-- but even a trivial check of count(*) will exercise the normal code path +-- through the SRF. + +select count(*) >= 0 as ok from pg_available_extension_versions; + +select count(*) >= 0 as ok from pg_available_extensions; + +-- At introduction, pg_config had 23 entries; it may grow +select count(*) > 20 as ok from pg_config; + +-- We expect no cursors in this test; see also portals.sql +select count(*) = 0 as ok from pg_cursors; + +select count(*) >= 0 as ok from pg_file_settings; + +-- There will surely be at least one active lock +select count(*) > 0 as ok from pg_locks; + +-- We expect no prepared statements in this test; see also prepare.sql +select count(*) = 0 as ok from pg_prepared_statements; + +-- See also prepared_xacts.sql +select count(*) >= 0 as ok from pg_prepared_xacts; + +-- This is to record the prevailing planner enable_foo settings during +-- a regression test run. +select name, setting from pg_settings where name like 'enable%'; + +-- Test that the pg_timezone_names and pg_timezone_abbrevs views are +-- more-or-less working. We can't test their contents in any great detail +-- without the outputs changing anytime IANA updates the underlying data, +-- but it seems reasonable to expect at least one entry per major meridian. +-- (At the time of writing, the actual counts are around 38 because of +-- zones using fractional GMT offsets, so this is a pretty loose test.) +select count(distinct utc_offset) >= 24 as ok from pg_timezone_names; +select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; +-- Let's check the non-default timezone abbreviation sets, too +set timezone_abbreviations = 'Australia'; +select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; +set timezone_abbreviations = 'India'; +select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql index ab86622a88..5898747064 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -469,22 +469,6 @@ SELECT '2007-12-09 07:00:01 UTC'::timestamptz AT TIME ZONE 'VET'; SELECT '2007-12-09 07:29:59 UTC'::timestamptz AT TIME ZONE 'VET'; SELECT '2007-12-09 07:30:00 UTC'::timestamptz AT TIME ZONE 'VET'; --- --- Test that the pg_timezone_names and pg_timezone_abbrevs views are --- more-or-less working. We can't test their contents in any great detail --- without the outputs changing anytime IANA updates the underlying data, --- but it seems reasonable to expect at least one entry per major meridian. --- (At the time of writing, the actual counts are around 38 because of --- zones using fractional GMT offsets, so this is a pretty loose test.) --- -select count(distinct utc_offset) >= 24 as ok from pg_timezone_names; -select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; --- Let's check the non-default timezone abbreviation sets, too -set timezone_abbreviations = 'Australia'; -select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; -set timezone_abbreviations = 'India'; -select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; - -- -- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14504) -- -- 2.40.0