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

Commit611e424

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 parentf2a37dd commit611e424

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
@@ -2987,6 +2987,7 @@ create statistics stts_s1.stts_foo on col1, col2 from stts_t3;
29872987
create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from stts_t3;
29882988
insert into stts_t1 select i,i from generate_series(1,100) i;
29892989
analyze stts_t1;
2990+
set search_path to public, stts_s1, stts_s2, tststats;
29902991
\dX
29912992
List of extended statistics
29922993
Schema | Name | Definition | Ndistinct | Dependencies | MCV
@@ -3002,7 +3003,7 @@ analyze stts_t1;
30023003
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
30033004
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
30043005
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
3005-
tststats | priv_test_stats | a, b FROMtststats.priv_test_tbl | | | defined
3006+
tststats | priv_test_stats | a, b FROM priv_test_tbl | | | defined
30063007
(12 rows)
30073008

30083009
\dX stts_?
@@ -3037,7 +3038,7 @@ analyze stts_t1;
30373038
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
30383039
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
30393040
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
3040-
tststats | priv_test_stats | a, b FROMtststats.priv_test_tbl | | | defined
3041+
tststats | priv_test_stats | a, b FROM priv_test_tbl | | | defined
30413042
(12 rows)
30423043

30433044
\dX+ stts_?
@@ -3064,30 +3065,45 @@ analyze stts_t1;
30643065
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
30653066
(1 row)
30663067

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

30873102
reset role;
30883103
drop table stts_t1, stts_t2, stts_t3;
30893104
drop schema stts_s1, stts_s2 cascade;
30903105
drop user regress_stats_ext;
3106+
reset search_path;
30913107
-- User with no access
30923108
CREATE USER regress_stats_user1;
30933109
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