psql \dX: check schema when listing statistics objects

Commit ad600bba04 added psql command \dX listing extended statistics
objects, but it failed to consider search_path when selecting the
elements so some of the returned elements might be invisible.

The visibility was already considered for tab completion (added by
commit d99d58cdc8), so adding it to the query is fairly simple.

Reported and fix by Justin Pryzby, regression tests by me. Backpatch
to PostgreSQL 14, where \dX was introduced.

Batchpatch-through: 14
Author: Justin Pryzby
Reviewed-by: Tatsuro Yamada
Discussion: https://postgr.es/m/c027a541-5856-75a5-0868-341301e1624b%40nttcom.co.jp_1

3 files changed, 40 insertions, 19 deletions

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index ba658f731b0..8333558bdad 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4774,7 +4774,7 @@ listExtendedStats(const char *pattern)
 	processSQLNamePattern(pset.db, &buf, pattern,
 						  false, false,
 						  "es.stxnamespace::pg_catalog.regnamespace::text", "es.stxname",
-						  NULL, NULL);
+						  NULL, "pg_catalog.pg_statistics_obj_is_visible(es.oid)");
 
 	appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
 
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 62b05c79f9e..7fb54de53d6 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -2991,6 +2991,7 @@ create statistics stts_s1.stts_foo on col1, col2 from stts_t3;
 create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from stts_t3;
 insert into stts_t1 select i,i from generate_series(1,100) i;
 analyze stts_t1;
+set search_path to public, stts_s1, stts_s2, tststats;
 \dX
                                                            List of extended statistics
   Schema  |          Name          |                               Definition                               | Ndistinct | Dependencies |   MCV   
@@ -3006,7 +3007,7 @@ analyze stts_t1;
  public   | stts_hoge              | col1, col2, col3 FROM stts_t3                                          | defined   | defined      | defined
  stts_s1  | stts_foo               | col1, col2 FROM stts_t3                                                | defined   | defined      | defined
  stts_s2  | stts_yama              | col1, col3 FROM stts_t3                                                |           | defined      | defined
- tststats | priv_test_stats        | a, b FROM tststats.priv_test_tbl                                       |           |              | defined
+ tststats | priv_test_stats        | a, b FROM priv_test_tbl                                                |           |              | defined
 (12 rows)
 
 \dX stts_?
@@ -3041,7 +3042,7 @@ analyze stts_t1;
  public   | stts_hoge              | col1, col2, col3 FROM stts_t3                                          | defined   | defined      | defined
  stts_s1  | stts_foo               | col1, col2 FROM stts_t3                                                | defined   | defined      | defined
  stts_s2  | stts_yama              | col1, col3 FROM stts_t3                                                |           | defined      | defined
- tststats | priv_test_stats        | a, b FROM tststats.priv_test_tbl                                       |           |              | defined
+ tststats | priv_test_stats        | a, b FROM priv_test_tbl                                                |           |              | defined
 (12 rows)
 
 \dX+ stts_?
@@ -3068,30 +3069,45 @@ analyze stts_t1;
  stts_s2 | stts_yama | col1, col3 FROM stts_t3 |           | defined      | defined
 (1 row)
 
+set search_path to public, stts_s1;
+\dX
+                                                          List of extended statistics
+ Schema  |          Name          |                               Definition                               | Ndistinct | Dependencies |   MCV   
+---------+------------------------+------------------------------------------------------------------------+-----------+--------------+---------
+ public  | func_deps_stat         | ((a * 2)), upper(b), ((c + (1)::numeric)) FROM functional_dependencies |           | defined      | 
+ public  | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays                                          |           |              | defined
+ public  | mcv_lists_bool_stats   | a, b, c FROM mcv_lists_bool                                            |           |              | defined
+ public  | mcv_lists_stats        | a, b, d FROM mcv_lists                                                 |           |              | defined
+ public  | stts_1                 | a, b FROM stts_t1                                                      | defined   |              | 
+ public  | stts_2                 | a, b FROM stts_t1                                                      | defined   | defined      | 
+ public  | stts_3                 | a, b FROM stts_t1                                                      | defined   | defined      | defined
+ public  | stts_4                 | b, c FROM stts_t2                                                      | defined   | defined      | defined
+ public  | stts_hoge              | col1, col2, col3 FROM stts_t3                                          | defined   | defined      | defined
+ stts_s1 | stts_foo               | col1, col2 FROM stts_t3                                                | defined   | defined      | defined
+(10 rows)
+
 create role regress_stats_ext nosuperuser;
 set role regress_stats_ext;
 \dX
-                                                           List of extended statistics
-  Schema  |          Name          |                               Definition                               | Ndistinct | Dependencies |   MCV   
-----------+------------------------+------------------------------------------------------------------------+-----------+--------------+---------
- public   | func_deps_stat         | ((a * 2)), upper(b), ((c + (1)::numeric)) FROM functional_dependencies |           | defined      | 
- public   | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays                                          |           |              | defined
- public   | mcv_lists_bool_stats   | a, b, c FROM mcv_lists_bool                                            |           |              | defined
- public   | mcv_lists_stats        | a, b, d FROM mcv_lists                                                 |           |              | defined
- public   | stts_1                 | a, b FROM stts_t1                                                      | defined   |              | 
- public   | stts_2                 | a, b FROM stts_t1                                                      | defined   | defined      | 
- public   | stts_3                 | a, b FROM stts_t1                                                      | defined   | defined      | defined
- public   | stts_4                 | b, c FROM stts_t2                                                      | defined   | defined      | defined
- public   | stts_hoge              | col1, col2, col3 FROM stts_t3                                          | defined   | defined      | defined
- stts_s1  | stts_foo               | col1, col2 FROM stts_t3                                                | defined   | defined      | defined
- stts_s2  | stts_yama              | col1, col3 FROM stts_t3                                                |           | defined      | defined
- tststats | priv_test_stats        | a, b FROM tststats.priv_test_tbl                                       |           |              | defined
-(12 rows)
+                                                          List of extended statistics
+ Schema |          Name          |                               Definition                               | Ndistinct | Dependencies |   MCV   
+--------+------------------------+------------------------------------------------------------------------+-----------+--------------+---------
+ public | func_deps_stat         | ((a * 2)), upper(b), ((c + (1)::numeric)) FROM functional_dependencies |           | defined      | 
+ public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays                                          |           |              | defined
+ public | mcv_lists_bool_stats   | a, b, c FROM mcv_lists_bool                                            |           |              | defined
+ public | mcv_lists_stats        | a, b, d FROM mcv_lists                                                 |           |              | defined
+ public | stts_1                 | a, b FROM stts_t1                                                      | defined   |              | 
+ public | stts_2                 | a, b FROM stts_t1                                                      | defined   | defined      | 
+ public | stts_3                 | a, b FROM stts_t1                                                      | defined   | defined      | defined
+ public | stts_4                 | b, c FROM stts_t2                                                      | defined   | defined      | defined
+ public | stts_hoge              | col1, col2, col3 FROM stts_t3                                          | defined   | defined      | defined
+(9 rows)
 
 reset role;
 drop table stts_t1, stts_t2, stts_t3;
 drop schema stts_s1, stts_s2 cascade;
 drop user regress_stats_ext;
+reset search_path;
 -- User with no access
 CREATE USER regress_stats_user1;
 GRANT USAGE ON SCHEMA tststats TO regress_stats_user1;
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index e033080d4fb..d563c4654cc 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -1529,6 +1529,7 @@ create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from stts_
 
 insert into stts_t1 select i,i from generate_series(1,100) i;
 analyze stts_t1;
+set search_path to public, stts_s1, stts_s2, tststats;
 
 \dX
 \dX stts_?
@@ -1538,6 +1539,9 @@ analyze stts_t1;
 \dX+ *stts_hoge
 \dX+ stts_s2.stts_yama
 
+set search_path to public, stts_s1;
+\dX
+
 create role regress_stats_ext nosuperuser;
 set role regress_stats_ext;
 \dX
@@ -1546,6 +1550,7 @@ reset role;
 drop table stts_t1, stts_t2, stts_t3;
 drop schema stts_s1, stts_s2 cascade;
 drop user regress_stats_ext;
+reset search_path;
 
 -- User with no access
 CREATE USER regress_stats_user1;