pgstattuple: add test for coverage

From 1fff7a51d1f91d0db3abe71ecef51ec0f69b1e3e Mon Sep 17 00:00:00 2001
From: Lee Dong Wook <sh95119@gmail.com>
Date: Wed, 27 Jul 2022 10:34:44 +0900
Subject: [PATCH] pgstattuple: add test to improve coverage

---
 contrib/pgstattuple/Makefile                 |   2 +
 contrib/pgstattuple/expected/pgstattuple.out | 103 ++++++++++++++-----
 contrib/pgstattuple/sql/pgstattuple.sql      |  30 +++++-
 3 files changed, 108 insertions(+), 27 deletions(-)

diff --git a/contrib/pgstattuple/Makefile b/contrib/pgstattuple/Makefile
index c5b17fc703e2..85a5c6e34fbe 100644
--- a/contrib/pgstattuple/Makefile
+++ b/contrib/pgstattuple/Makefile
@@ -13,6 +13,8 @@ DATA = pgstattuple--1.4.sql pgstattuple--1.4--1.5.sql \
 	pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.1.sql
 PGFILEDESC = "pgstattuple - tuple-level statistics"
 
+EXTRA_INSTALL=contrib/bloom
+
 REGRESS = pgstattuple
 
 ifdef USE_PGXS
diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out
index e4ac86f9e30f..5007df0969ac 100644
--- a/contrib/pgstattuple/expected/pgstattuple.out
+++ b/contrib/pgstattuple/expected/pgstattuple.out
@@ -1,44 +1,67 @@
 CREATE EXTENSION pgstattuple;
+CREATE EXTENSION bloom;
 --
 -- It's difficult to come up with platform-independent test cases for
 -- the pgstattuple functions, but the results for empty tables and
 -- indexes should be that.
 --
-create table test (a int primary key, b int[]);
+create table test (a int primary key, b int[], p point) with (autovacuum_enabled = off);
+insert into test(a, b, p)
+select a, array(select generate_series as num from generate_series(1, 10)), point(a*10, a*10) from generate_series(1, 10000) a;
+-- make dead tuples
+delete from test where a between 1 and 5000;
 select * from pgstattuple('test');
  table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
 -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
-         0 |           0 |         0 |             0 |                0 |              0 |                  0 |          0 |            0
+   1171456 |        5000 |    560000 |          47.8 |             5000 |         560000 |               47.8 |       7452 |         0.64
 (1 row)
 
 select * from pgstattuple('test'::text);
  table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
 -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
-         0 |           0 |         0 |             0 |                0 |              0 |                  0 |          0 |            0
+   1171456 |        5000 |    560000 |          47.8 |             5000 |         560000 |               47.8 |       7452 |         0.64
 (1 row)
 
 select * from pgstattuple('test'::name);
  table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
 -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
-         0 |           0 |         0 |             0 |                0 |              0 |                  0 |          0 |            0
+   1171456 |        5000 |    560000 |          47.8 |             5000 |         560000 |               47.8 |       7452 |         0.64
 (1 row)
 
 select * from pgstattuple('test'::regclass);
  table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
 -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
-         0 |           0 |         0 |             0 |                0 |              0 |                  0 |          0 |            0
+   1171456 |        5000 |    560000 |          47.8 |             5000 |         560000 |               47.8 |       7452 |         0.64
 (1 row)
 
 select pgstattuple(oid) from pg_class where relname = 'test';
-     pgstattuple     
----------------------
- (0,0,0,0,0,0,0,0,0)
+                      pgstattuple                      
+-------------------------------------------------------
+ (1171456,5000,560000,47.8,5000,560000,47.8,7452,0.64)
 (1 row)
 
 select pgstattuple(relname) from pg_class where relname = 'test';
-     pgstattuple     
----------------------
- (0,0,0,0,0,0,0,0,0)
+                      pgstattuple                      
+-------------------------------------------------------
+ (1171456,5000,560000,47.8,5000,560000,47.8,7452,0.64)
+(1 row)
+
+select * from pgstattuple_approx('test');
+ table_len | scanned_percent | approx_tuple_count | approx_tuple_len | approx_tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | approx_free_space | approx_free_percent 
+-----------+-----------------+--------------------+------------------+----------------------+------------------+----------------+--------------------+-------------------+---------------------
+   1171456 |             100 |               5000 |           560000 |    47.80375874125874 |             5000 |         560000 |  47.80375874125874 |              7452 |  0.6361314466783217
+(1 row)
+
+select * from pgstattuple_approx('test'::text);
+ table_len | scanned_percent | approx_tuple_count | approx_tuple_len | approx_tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | approx_free_space | approx_free_percent 
+-----------+-----------------+--------------------+------------------+----------------------+------------------+----------------+--------------------+-------------------+---------------------
+   1171456 |             100 |               5000 |           560000 |    47.80375874125874 |             5000 |         560000 |  47.80375874125874 |              7452 |  0.6361314466783217
+(1 row)
+
+select * from pgstattuple_approx('test'::regclass);
+ table_len | scanned_percent | approx_tuple_count | approx_tuple_len | approx_tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | approx_free_space | approx_free_percent 
+-----------+-----------------+--------------------+------------------+----------------------+------------------+----------------+--------------------+-------------------+---------------------
+   1171456 |             100 |               5000 |           560000 |    47.80375874125874 |             5000 |         560000 |  47.80375874125874 |              7452 |  0.6361314466783217
 (1 row)
 
 select version, tree_level,
@@ -48,7 +71,7 @@ select version, tree_level,
     from pgstatindex('test_pkey');
  version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation 
 ---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
-       4 |          0 |          1 |             0 |              0 |          0 |           0 |             0 |              NaN |                NaN
+       4 |          1 |         30 |             3 |              1 |         28 |           0 |             0 |            87.91 |                  0
 (1 row)
 
 select version, tree_level,
@@ -58,7 +81,7 @@ select version, tree_level,
     from pgstatindex('test_pkey'::text);
  version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation 
 ---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
-       4 |          0 |          1 |             0 |              0 |          0 |           0 |             0 |              NaN |                NaN
+       4 |          1 |         30 |             3 |              1 |         28 |           0 |             0 |            87.91 |                  0
 (1 row)
 
 select version, tree_level,
@@ -68,7 +91,7 @@ select version, tree_level,
     from pgstatindex('test_pkey'::name);
  version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation 
 ---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
-       4 |          0 |          1 |             0 |              0 |          0 |           0 |             0 |              NaN |                NaN
+       4 |          1 |         30 |             3 |              1 |         28 |           0 |             0 |            87.91 |                  0
 (1 row)
 
 select version, tree_level,
@@ -78,49 +101,49 @@ select version, tree_level,
     from pgstatindex('test_pkey'::regclass);
  version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation 
 ---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
-       4 |          0 |          1 |             0 |              0 |          0 |           0 |             0 |              NaN |                NaN
+       4 |          1 |         30 |             3 |              1 |         28 |           0 |             0 |            87.91 |                  0
 (1 row)
 
 select pg_relpages('test');
  pg_relpages 
 -------------
-           0
+         143
 (1 row)
 
 select pg_relpages('test_pkey');
  pg_relpages 
 -------------
-           1
+          30
 (1 row)
 
 select pg_relpages('test_pkey'::text);
  pg_relpages 
 -------------
-           1
+          30
 (1 row)
 
 select pg_relpages('test_pkey'::name);
  pg_relpages 
 -------------
-           1
+          30
 (1 row)
 
 select pg_relpages('test_pkey'::regclass);
  pg_relpages 
 -------------
-           1
+          30
 (1 row)
 
 select pg_relpages(oid) from pg_class where relname = 'test_pkey';
  pg_relpages 
 -------------
-           1
+          30
 (1 row)
 
 select pg_relpages(relname) from pg_class where relname = 'test_pkey';
  pg_relpages 
 -------------
-           1
+          30
 (1 row)
 
 create index test_ginidx on test using gin (b);
@@ -132,11 +155,41 @@ select * from pgstatginindex('test_ginidx');
 
 create index test_hashidx on test using hash (b);
 select * from pgstathashindex('test_hashidx');
- version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items | free_percent 
----------+--------------+----------------+--------------+--------------+------------+------------+--------------
-       4 |            4 |              0 |            1 |            0 |          0 |          0 |          100
+ version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items |   free_percent    
+---------+--------------+----------------+--------------+--------------+------------+------------+-------------------
+       4 |           17 |             12 |            1 |           15 |       5000 |          0 | 72.12061736104916
+(1 row)
+
+-- check that (btree, hash, gist) index with pgstattuple should work
+create index test_btreeidx on test using btree (a);
+create index test_gistidx on test using gist (p);
+select * from pgstattuple('test_btreeidx');
+ table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
+-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
+    131072 |        5000 |     80000 |         61.04 |                0 |              0 |                  0 |      13812 |        10.54
+(1 row)
+
+select * from pgstattuple('test_hashidx');
+ERROR:  index "test_hashidx" contains unexpected zero page at block 31
+HINT:  Please REINDEX it.
+select * from pgstattuple('test_gistidx');
+ table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
+-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
+    237568 |        5000 |    200000 |         84.19 |                0 |              0 |                  0 |       8144 |         3.43
 (1 row)
 
+-- check that these index type error with (gin, spgist, brin, etc)
+create index test_spgistidx on test using spgist (p);
+create index test_brinidx on test using brin (a);
+create index test_bloomidx on test using bloom (a);
+select * from pgstattuple('test_ginidx');
+ERROR:  index "test_ginidx" (gin index) is not supported
+select * from pgstattuple('test_spgistidx');
+ERROR:  index "test_spgistidx" (spgist index) is not supported
+select * from pgstattuple('test_brinidx');
+ERROR:  index "test_brinidx" (brin index) is not supported
+select * from pgstattuple('test_bloomidx');
+ERROR:  index "test_bloomidx" (unknown index) is not supported
 -- these should error with the wrong type
 select pgstatginindex('test_pkey');
 ERROR:  relation "test_pkey" is not a GIN index
diff --git a/contrib/pgstattuple/sql/pgstattuple.sql b/contrib/pgstattuple/sql/pgstattuple.sql
index 5111be0e6250..64dbd87fd53e 100644
--- a/contrib/pgstattuple/sql/pgstattuple.sql
+++ b/contrib/pgstattuple/sql/pgstattuple.sql
@@ -1,12 +1,17 @@
 CREATE EXTENSION pgstattuple;
-
+CREATE EXTENSION bloom;
 --
 -- It's difficult to come up with platform-independent test cases for
 -- the pgstattuple functions, but the results for empty tables and
 -- indexes should be that.
 --
 
-create table test (a int primary key, b int[]);
+create table test (a int primary key, b int[], p point) with (autovacuum_enabled = off);
+
+insert into test(a, b, p)
+select a, array(select generate_series as num from generate_series(1, 10)), point(a*10, a*10) from generate_series(1, 10000) a;
+-- make dead tuples
+delete from test where a between 1 and 5000;
 
 select * from pgstattuple('test');
 select * from pgstattuple('test'::text);
@@ -14,6 +19,9 @@ select * from pgstattuple('test'::name);
 select * from pgstattuple('test'::regclass);
 select pgstattuple(oid) from pg_class where relname = 'test';
 select pgstattuple(relname) from pg_class where relname = 'test';
+select * from pgstattuple_approx('test');
+select * from pgstattuple_approx('test'::text);
+select * from pgstattuple_approx('test'::regclass);
 
 select version, tree_level,
     index_size / current_setting('block_size')::int as index_size,
@@ -52,6 +60,24 @@ create index test_hashidx on test using hash (b);
 
 select * from pgstathashindex('test_hashidx');
 
+-- check that (btree, hash, gist) index with pgstattuple should work
+create index test_btreeidx on test using btree (a);
+create index test_gistidx on test using gist (p);
+
+select * from pgstattuple('test_btreeidx');
+select * from pgstattuple('test_hashidx');
+select * from pgstattuple('test_gistidx');
+
+-- check that these index type error with (gin, spgist, brin, etc)
+create index test_spgistidx on test using spgist (p);
+create index test_brinidx on test using brin (a);
+create index test_bloomidx on test using bloom (a);
+
+select * from pgstattuple('test_ginidx');
+select * from pgstattuple('test_spgistidx');
+select * from pgstattuple('test_brinidx');
+select * from pgstattuple('test_bloomidx');
+
 -- these should error with the wrong type
 select pgstatginindex('test_pkey');
 select pgstathashindex('test_pkey');