Re: list of extended statistics on psql (\dX)

2021-07-26 Thread Tatsuro Yamada 

Hi Tomas and Justin,

On 2021/07/27 4:26, Tomas Vondra wrote:

Hi,

I've pushed the last version of the fix, including the regression tests etc. 
Backpatch to 14, where \dX was introduced.



Thank you!


Regards,
Tatsuro Yamada

Re: list of extended statistics on psql (\dX)

2021-07-26 Thread Tomas Vondra 

Hi,

I've pushed the last version of the fix, including the regression tests 
etc. Backpatch to 14, where \dX was introduced.


regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: list of extended statistics on psql (\dX)

2021-07-07 Thread Tatsuro Yamada 

Hi Tomas and Justin,

On 2021/06/07 4:47, Tomas Vondra wrote:

Here's a slightly more complete patch, tweaking the regression tests a
bit to detect this.



I tested your patch on PG14beta2 and PG15devel.
And they work fine.
===
 All 209 tests passed.
===

Next time I create a feature on psql, I will be careful to add
a check for schema visibility rules. :-D

Thanks,
Tatsuro Yamada

Re: list of extended statistics on psql (\dX)

2021-06-06 Thread Tomas Vondra 
Hi,

Here's a slightly more complete patch, tweaking the regression tests a
bit to detect this.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 2abf255798..eba659705e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4774,7 +4774,7 @@ listExtendedStats(const char *pattern)
 	processSQLNamePattern(pset.db, , pattern,
 		  false, false,
 		  "es.stxnamespace::pg_catalog.regnamespace::text", "es.stxname",
-		  NULL, NULL);
+		  NULL, "pg_catalog.pg_statistics_obj_is_visible(es.oid)");
 
 	appendPQExpBufferStr(, "ORDER BY 1, 2;");
 
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 8c214d8dfc..fa9fa9c8f0 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -2987,6 +2987,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   
@@ -3002,7 +3003,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_?
@@ -3037,7 +3038,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_?
@@ -3064,30 +3065,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

Re: list of extended statistics on psql (\dX)

2021-05-30 Thread Tomas Vondra 



On 5/30/21 7:24 PM, Justin Pryzby wrote:
> On Wed, Jan 20, 2021 at 11:00:50PM +0100, Tomas Vondra wrote:
>> Thanks, I've pushed this. I had to tweak the regression tests a bit, for two
>> reasons:
> 
> \dX isn't checking schema visibility rules, so accidentally shows stats 
> objects
> outside of the search path.  I noticed after installing the PG14b1 client,
> since we create stats objects in a separate schema to allow excluding them 
> with
> pg_dump -N.
> 
> diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
> index 195f8d8cd2..e29f13c65e 100644
> --- a/src/bin/psql/describe.c
> +++ b/src/bin/psql/describe.c
> @@ -4774,7 +4774,7 @@ listExtendedStats(const char *pattern)
>   processSQLNamePattern(pset.db, , pattern,
> false, false,
> 
> "es.stxnamespace::pg_catalog.regnamespace::text", "es.stxname",
> -   NULL, NULL);
> +   NULL, 
> "pg_catalog.pg_statistics_obj_is_visible(es.oid)");
>  
>   appendPQExpBufferStr(, "ORDER BY 1, 2;");
>  

Thanks for noticing this! Will push.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: list of extended statistics on psql (\dX)

2021-05-30 Thread Justin Pryzby 
On Wed, Jan 20, 2021 at 11:00:50PM +0100, Tomas Vondra wrote:
> Thanks, I've pushed this. I had to tweak the regression tests a bit, for two
> reasons:

\dX isn't checking schema visibility rules, so accidentally shows stats objects
outside of the search path.  I noticed after installing the PG14b1 client,
since we create stats objects in a separate schema to allow excluding them with
pg_dump -N.

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 195f8d8cd2..e29f13c65e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4774,7 +4774,7 @@ listExtendedStats(const char *pattern)
processSQLNamePattern(pset.db, , pattern,
  false, false,
  
"es.stxnamespace::pg_catalog.regnamespace::text", "es.stxname",
- NULL, NULL);
+ NULL, 
"pg_catalog.pg_statistics_obj_is_visible(es.oid)");
 
appendPQExpBufferStr(, "ORDER BY 1, 2;");