From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@postgreSQL.org
Subject: Create a separate test file for exercising system views
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <19256.1485723614.0@sss.pgh.pa.us>
Date: Sun, 29 Jan 2017 16:02:21 -0500
Message-ID: <19359.1485723741@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <19256.1485723614.1@sss.pgh.pa.us>

In connection with the "pg_hba_file_settings view patch" thread, I was
wondering where we could logically insert a regression test case for that
view.  I realized that there is no natural home for it among the existing
regression tests, because it's not really connected to any SQL language
feature.  The same is true for a number of other built-in views, and
unsurprisingly, most of them are not exercised anywhere :-(.

Accordingly, I propose creating a new regression test file whose charter
is to exercise the SRFs underlying system views, as per attached.
I don't think we desperately need new tests for views that expand to
simple SQL, but these test cases correspond directly to code coverage
for C functions, so they seem worthwhile.

I did not do anything about testing the various pg_stat_xxx views.
Those could be added later, or maybe they deserve their own home.
(In many cases, those would need something smarter than the basic
count(*) technique used here, because the C functions are invoked
in the view's SELECT list not in FROM, so the planner would throw
away those calls.)

Comments/objections?

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="new-sysviews-test-1.patch";
	charset="us-ascii"
Content-ID: <19256.1485723614.2@sss.pgh.pa.us>
Content-Description: new-sysviews-test-1.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/ex=
pected/rangefuncs.out
index 56481de..526a4ae 100644
*** a/src/test/regress/expected/rangefuncs.out
--- b/src/test/regress/expected/rangefuncs.out
***************
*** 1,19 ****
- SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%';
-          name         | setting=20
- ----------------------+---------
-  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)
-=20
  CREATE TABLE foo2(fooid int, f2 int);
  INSERT INTO foo2 VALUES(1, 11);
  INSERT INTO foo2 VALUES(2, 22);
--- 1,3 ----
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expe=
cted/sysviews.out
index ...852a7c3 .
*** a/src/test/regress/expected/sysviews.out
--- b/src/test/regress/expected/sysviews.out
***************
*** 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(*) >=3D 0 as ok from pg_available_extension_versions;
+  ok=20
+ ----
+  t
+ (1 row)
+=20
+ select count(*) >=3D 0 as ok from pg_available_extensions;
+  ok=20
+ ----
+  t
+ (1 row)
+=20
+ -- At introduction, pg_config had 23 entries; it may grow
+ select count(*) > 20 as ok from pg_config;
+  ok=20
+ ----
+  t
+ (1 row)
+=20
+ -- We expect no cursors in this test; see also portals.sql
+ select count(*) =3D 0 as ok from pg_cursors;
+  ok=20
+ ----
+  t
+ (1 row)
+=20
+ select count(*) >=3D 0 as ok from pg_file_settings;
+  ok=20
+ ----
+  t
+ (1 row)
+=20
+ -- There will surely be at least one active lock
+ select count(*) > 0 as ok from pg_locks;
+  ok=20
+ ----
+  t
+ (1 row)
+=20
+ -- We expect no prepared statements in this test; see also prepare.sql
+ select count(*) =3D 0 as ok from pg_prepared_statements;
+  ok=20
+ ----
+  t
+ (1 row)
+=20
+ -- See also prepared_xacts.sql
+ select count(*) >=3D 0 as ok from pg_prepared_xacts;
+  ok=20
+ ----
+  t
+ (1 row)
+=20
+ -- 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=20
+ ----------------------+---------
+  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)
+=20
+ -- 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 meridia=
n.
+ -- (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) >=3D 24 as ok from pg_timezone_names;
+  ok=20
+ ----
+  t
+ (1 row)
+=20
+ select count(distinct utc_offset) >=3D 24 as ok from pg_timezone_abbrevs;
+  ok=20
+ ----
+  t
+ (1 row)
+=20
+ -- Let's check the non-default timezone abbreviation sets, too
+ set timezone_abbreviations =3D 'Australia';
+ select count(distinct utc_offset) >=3D 24 as ok from pg_timezone_abbrevs;
+  ok=20
+ ----
+  t
+ (1 row)
+=20
+ set timezone_abbreviations =3D 'India';
+ select count(distinct utc_offset) >=3D 24 as ok from pg_timezone_abbrevs;
+  ok=20
+ ----
+  t
+ (1 row)
+=20
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/e=
xpected/timestamptz.out
index 51d4d21..69d3965 100644
*** a/src/test/regress/expected/timestamptz.out
--- b/src/test/regress/expected/timestamptz.out
*************** SELECT '2007-12-09 07:30:00 UTC'::timest
*** 2604,2644 ****
  (1 row)
=20=20
  --
- -- 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 meridia=
n.
- -- (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) >=3D 24 as ok from pg_timezone_names;
-  ok=20
- ----
-  t
- (1 row)
-=20
- select count(distinct utc_offset) >=3D 24 as ok from pg_timezone_abbrevs;
-  ok=20
- ----
-  t
- (1 row)
-=20
- -- Let's check the non-default timezone abbreviation sets, too
- set timezone_abbreviations =3D 'Australia';
- select count(distinct utc_offset) >=3D 24 as ok from pg_timezone_abbrevs;
-  ok=20
- ----
-  t
- (1 row)
-=20
- set timezone_abbreviations =3D 'India';
- select count(distinct utc_offset) >=3D 24 as ok from pg_timezone_abbrevs;
-  ok=20
- ----
-  t
- (1 row)
-=20
- --
  -- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14=
504)
  --
  create temp table tmptz (f1 timestamptz primary key);
--- 2604,2609 ----
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel=
_schedule
index e9b2bad..edeb2d6 100644
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
*************** test: brin gin gist spgist privileges in
*** 89,95 ****
  # ----------
  # Another group of parallel tests
  # ----------
! test: alter_generic alter_operator misc psql async dbsize misc_functions =
tsrf
=20=20
  # rules cannot run concurrently with any test that creates a view
  test: rules psql_crosstab amutils
--- 89,95 ----
  # ----------
  # Another group of parallel tests
  # ----------
! test: alter_generic alter_operator misc psql async dbsize misc_functions =
sysviews tsrf
=20=20
  # 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_sch=
edule
index 7cdc0f6..27a46d7 100644
*** a/src/test/regress/serial_schedule
--- b/src/test/regress/serial_schedule
*************** test: psql
*** 123,128 ****
--- 123,129 ----
  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/ran=
gefuncs.sql
index c8edc55..09ac8fb 100644
*** a/src/test/regress/sql/rangefuncs.sql
--- b/src/test/regress/sql/rangefuncs.sql
***************
*** 1,5 ****
- SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%';
-=20
  CREATE TABLE foo2(fooid int, f2 int);
  INSERT INTO foo2 VALUES(1, 11);
  INSERT INTO foo2 VALUES(2, 22);
--- 1,3 ----
diff --git a/src/test/regress/sql/sysviews.sql b/src/test/regress/sql/sysvi=
ews.sql
index ...0941b6b .
*** a/src/test/regress/sql/sysviews.sql
--- b/src/test/regress/sql/sysviews.sql
***************
*** 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.
+=20
+ select count(*) >=3D 0 as ok from pg_available_extension_versions;
+=20
+ select count(*) >=3D 0 as ok from pg_available_extensions;
+=20
+ -- At introduction, pg_config had 23 entries; it may grow
+ select count(*) > 20 as ok from pg_config;
+=20
+ -- We expect no cursors in this test; see also portals.sql
+ select count(*) =3D 0 as ok from pg_cursors;
+=20
+ select count(*) >=3D 0 as ok from pg_file_settings;
+=20
+ -- There will surely be at least one active lock
+ select count(*) > 0 as ok from pg_locks;
+=20
+ -- We expect no prepared statements in this test; see also prepare.sql
+ select count(*) =3D 0 as ok from pg_prepared_statements;
+=20
+ -- See also prepared_xacts.sql
+ select count(*) >=3D 0 as ok from pg_prepared_xacts;
+=20
+ -- 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%';
+=20
+ -- 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 meridia=
n.
+ -- (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) >=3D 24 as ok from pg_timezone_names;
+ select count(distinct utc_offset) >=3D 24 as ok from pg_timezone_abbrevs;
+ -- Let's check the non-default timezone abbreviation sets, too
+ set timezone_abbreviations =3D 'Australia';
+ select count(distinct utc_offset) >=3D 24 as ok from pg_timezone_abbrevs;
+ set timezone_abbreviations =3D 'India';
+ select count(distinct utc_offset) >=3D 24 as ok from pg_timezone_abbrevs;
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/ti=
mestamptz.sql
index ab86622..5898747 100644
*** a/src/test/regress/sql/timestamptz.sql
--- b/src/test/regress/sql/timestamptz.sql
*************** SELECT '2007-12-09 07:29:59 UTC'::timest
*** 470,491 ****
  SELECT '2007-12-09 07:30:00 UTC'::timestamptz AT TIME ZONE 'VET';
=20=20
  --
- -- 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 meridia=
n.
- -- (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) >=3D 24 as ok from pg_timezone_names;
- select count(distinct utc_offset) >=3D 24 as ok from pg_timezone_abbrevs;
- -- Let's check the non-default timezone abbreviation sets, too
- set timezone_abbreviations =3D 'Australia';
- select count(distinct utc_offset) >=3D 24 as ok from pg_timezone_abbrevs;
- set timezone_abbreviations =3D 'India';
- select count(distinct utc_offset) >=3D 24 as ok from pg_timezone_abbrevs;
-=20
- --
  -- Test that AT TIME ZONE isn't misoptimized when using an index (bug #14=
504)
  --
  create temp table tmptz (f1 timestamptz primary key);
--- 470,475 ----

------- =_aaaaaaaaaa0
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

------- =_aaaaaaaaaa0--