Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf68b609

Browse files
committed
psql \dX: check schema when listing statistics objects
Commitad600bb added psql command \dX listing extended statisticsobjects, but it failed to consider search_path when selecting theelements so some of the returned elements might be invisible.The visibility was already considered for tab completion (added bycommitd99d58c), so adding it to the query is fairly simple.Reported and fix by Justin Pryzby, regression tests by me. Backpatchto PostgreSQL 14, where \dX was introduced.Batchpatch-through: 14Author: Justin PryzbyReviewed-by: Tatsuro YamadaDiscussion:https://postgr.es/m/c027a541-5856-75a5-0868-341301e1624b%40nttcom.co.jp_1
1 parent085f931 commitf68b609

File tree

3 files changed

+40
-19
lines changed

3 files changed

+40
-19
lines changed

‎src/bin/psql/describe.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4774,7 +4774,7 @@ listExtendedStats(const char *pattern)
47744774
processSQLNamePattern(pset.db,&buf,pattern,
47754775
false, false,
47764776
"es.stxnamespace::pg_catalog.regnamespace::text","es.stxname",
4777-
NULL,NULL);
4777+
NULL,"pg_catalog.pg_statistics_obj_is_visible(es.oid)");
47784778

47794779
appendPQExpBufferStr(&buf,"ORDER BY 1, 2;");
47804780

‎src/test/regress/expected/stats_ext.out

Lines changed: 34 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -2991,6 +2991,7 @@ create statistics stts_s1.stts_foo on col1, col2 from stts_t3;
29912991
create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from stts_t3;
29922992
insert into stts_t1 select i,i from generate_series(1,100) i;
29932993
analyze stts_t1;
2994+
set search_path to public, stts_s1, stts_s2, tststats;
29942995
\dX
29952996
List of extended statistics
29962997
Schema | Name | Definition | Ndistinct | Dependencies | MCV
@@ -3006,7 +3007,7 @@ analyze stts_t1;
30063007
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
30073008
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
30083009
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
3009-
tststats | priv_test_stats | a, b FROMtststats.priv_test_tbl | | | defined
3010+
tststats | priv_test_stats | a, b FROM priv_test_tbl | | | defined
30103011
(12 rows)
30113012

30123013
\dX stts_?
@@ -3041,7 +3042,7 @@ analyze stts_t1;
30413042
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
30423043
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
30433044
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
3044-
tststats | priv_test_stats | a, b FROMtststats.priv_test_tbl | | | defined
3045+
tststats | priv_test_stats | a, b FROM priv_test_tbl | | | defined
30453046
(12 rows)
30463047

30473048
\dX+ stts_?
@@ -3068,30 +3069,45 @@ analyze stts_t1;
30683069
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
30693070
(1 row)
30703071

3072+
set search_path to public, stts_s1;
3073+
\dX
3074+
List of extended statistics
3075+
Schema | Name | Definition | Ndistinct | Dependencies | MCV
3076+
---------+------------------------+------------------------------------------------------------------------+-----------+--------------+---------
3077+
public | func_deps_stat | ((a * 2)), upper(b), ((c + (1)::numeric)) FROM functional_dependencies | | defined |
3078+
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
3079+
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
3080+
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
3081+
public | stts_1 | a, b FROM stts_t1 | defined | |
3082+
public | stts_2 | a, b FROM stts_t1 | defined | defined |
3083+
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
3084+
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
3085+
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
3086+
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
3087+
(10 rows)
3088+
30713089
create role regress_stats_ext nosuperuser;
30723090
set role regress_stats_ext;
30733091
\dX
3074-
List of extended statistics
3075-
Schema | Name | Definition | Ndistinct | Dependencies | MCV
3076-
----------+------------------------+------------------------------------------------------------------------+-----------+--------------+---------
3077-
public | func_deps_stat | ((a * 2)), upper(b), ((c + (1)::numeric)) FROM functional_dependencies | | defined |
3078-
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
3079-
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
3080-
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
3081-
public | stts_1 | a, b FROM stts_t1 | defined | |
3082-
public | stts_2 | a, b FROM stts_t1 | defined | defined |
3083-
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
3084-
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
3085-
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
3086-
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
3087-
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
3088-
tststats | priv_test_stats | a, b FROM tststats.priv_test_tbl | | | defined
3089-
(12 rows)
3092+
List of extended statistics
3093+
Schema | Name | Definition | Ndistinct | Dependencies | MCV
3094+
--------+------------------------+------------------------------------------------------------------------+-----------+--------------+---------
3095+
public | func_deps_stat | ((a * 2)), upper(b), ((c + (1)::numeric)) FROM functional_dependencies | | defined |
3096+
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
3097+
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
3098+
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
3099+
public | stts_1 | a, b FROM stts_t1 | defined | |
3100+
public | stts_2 | a, b FROM stts_t1 | defined | defined |
3101+
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
3102+
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
3103+
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
3104+
(9 rows)
30903105

30913106
reset role;
30923107
drop table stts_t1, stts_t2, stts_t3;
30933108
drop schema stts_s1, stts_s2 cascade;
30943109
drop user regress_stats_ext;
3110+
reset search_path;
30953111
-- User with no access
30963112
CREATE USER regress_stats_user1;
30973113
GRANT USAGE ON SCHEMA tststats TO regress_stats_user1;

‎src/test/regress/sql/stats_ext.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1529,6 +1529,7 @@ create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from stts_
15291529

15301530
insert into stts_t1select i,ifrom generate_series(1,100) i;
15311531
analyze stts_t1;
1532+
set search_path to public, stts_s1, stts_s2, tststats;
15321533

15331534
\dX
15341535
\dX stts_?
@@ -1538,6 +1539,9 @@ analyze stts_t1;
15381539
\dX+*stts_hoge
15391540
\dX+stts_s2.stts_yama
15401541

1542+
set search_path to public, stts_s1;
1543+
\dX
1544+
15411545
create role regress_stats_ext nosuperuser;
15421546
set role regress_stats_ext;
15431547
\dX
@@ -1546,6 +1550,7 @@ reset role;
15461550
droptable stts_t1, stts_t2, stts_t3;
15471551
dropschema stts_s1, stts_s2 cascade;
15481552
dropuser regress_stats_ext;
1553+
reset search_path;
15491554

15501555
-- User with no access
15511556
CREATEUSERregress_stats_user1;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp