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

Commit1db0d17

Browse files
committed
Revert "psql \dX: list extended statistics objects"
Reverts891a1d0, because the new psql command \dX only worked forusers users who can read pg_statistic_ext_data catalog, and most regularusers lack that privilege (the catalog may contain sensitive user data).Reported-by: Noriyoshi ShinodaDiscussion:https://postgr.es/m/c027a541-5856-75a5-0868-341301e1624b%40nttcom.co.jp_1
1 parente09155b commit1db0d17

File tree

8 files changed

+1
-307
lines changed

8 files changed

+1
-307
lines changed

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 0 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -1918,28 +1918,6 @@ testdb=>
19181918
</para>
19191919
</listitem>
19201920
</varlistentry>
1921-
1922-
<varlistentry>
1923-
<term><literal>\dX [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
1924-
<listitem>
1925-
<para>
1926-
Lists extended statistics.
1927-
If <replaceable class="parameter">pattern</replaceable>
1928-
is specified, only those extended statistics whose names match the
1929-
pattern are listed.
1930-
If <literal>+</literal> is appended to the command name, each extended
1931-
statistics is listed with its size.
1932-
</para>
1933-
1934-
<para>
1935-
The column of the kind of extended stats (e.g. Ndistinct) shows some statuses.
1936-
"requested" means that it needs to collect statistics by <link
1937-
linkend="sql-analyze"><command>ANALYZE</command></link>.
1938-
"built" means <link linkend="sql-analyze"><command>ANALYZE</command></link> was
1939-
finished, and the planner can use it. NULL means that it doesn't exists.
1940-
</para>
1941-
</listitem>
1942-
</varlistentry>
19431921

19441922
<varlistentry>
19451923
<term><literal>\dy[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>

‎src/bin/psql/command.c

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -928,9 +928,6 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
928928
else
929929
success=listExtensions(pattern);
930930
break;
931-
case'X':/* Extended Statistics */
932-
success=listExtendedStats(pattern,show_verbose);
933-
break;
934931
case'y':/* Event Triggers */
935932
success=listEventTriggers(pattern,show_verbose);
936933
break;

‎src/bin/psql/describe.c

Lines changed: 0 additions & 150 deletions
Original file line numberDiff line numberDiff line change
@@ -4392,156 +4392,6 @@ listEventTriggers(const char *pattern, bool verbose)
43924392
return true;
43934393
}
43944394

4395-
/*
4396-
* \dX
4397-
*
4398-
* Describes extended statistics.
4399-
*/
4400-
bool
4401-
listExtendedStats(constchar*pattern,boolverbose)
4402-
{
4403-
PQExpBufferDatabuf;
4404-
PGresult*res;
4405-
printQueryOptmyopt=pset.popt;
4406-
4407-
if (pset.sversion<100000)
4408-
{
4409-
charsverbuf[32];
4410-
4411-
pg_log_error("The server (version %s) does not support extended statistics.",
4412-
formatPGVersionNumber(pset.sversion, false,
4413-
sverbuf,sizeof(sverbuf)));
4414-
return true;
4415-
}
4416-
4417-
initPQExpBuffer(&buf);
4418-
printfPQExpBuffer(&buf,
4419-
"SELECT \n"
4420-
"es.stxnamespace::pg_catalog.regnamespace::text AS \"%s\", \n"
4421-
"es.stxname AS \"%s\", \n"
4422-
"pg_catalog.format('%%s FROM %%s', \n"
4423-
" (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(a.attname),', ') \n"
4424-
" FROM pg_catalog.unnest(es.stxkeys) s(attnum) \n"
4425-
" JOIN pg_catalog.pg_attribute a \n"
4426-
" ON (es.stxrelid = a.attrelid \n"
4427-
" AND a.attnum = s.attnum \n"
4428-
" AND NOT a.attisdropped)), \n"
4429-
"es.stxrelid::regclass) AS \"%s\"",
4430-
gettext_noop("Schema"),
4431-
gettext_noop("Name"),
4432-
gettext_noop("Definition"));
4433-
4434-
/*
4435-
* Since 12 there are two catalogs - one for the definition, one for the
4436-
* data built by ANALYZE. Older releases use a single catalog. Also, 12
4437-
* adds the MCV statistics kind.
4438-
*/
4439-
if (pset.sversion<120000)
4440-
{
4441-
appendPQExpBuffer(&buf,
4442-
",\nCASE WHEN es.stxndistinct IS NOT NULL THEN 'built' \n"
4443-
" WHEN 'd' = any(es.stxkind) THEN 'requested' \n"
4444-
"END AS \"%s\", \n"
4445-
"CASE WHEN es.stxdependencies IS NOT NULL THEN 'built' \n"
4446-
" WHEN 'f' = any(es.stxkind) THEN 'requested' \n"
4447-
"END AS \"%s\"",
4448-
gettext_noop("Ndistinct"),
4449-
gettext_noop("Dependencies"));
4450-
}
4451-
else
4452-
{
4453-
appendPQExpBuffer(&buf,
4454-
",\nCASE WHEN esd.stxdndistinct IS NOT NULL THEN 'built' \n"
4455-
" WHEN 'd' = any(es.stxkind) THEN 'requested' \n"
4456-
"END AS \"%s\", \n"
4457-
"CASE WHEN esd.stxddependencies IS NOT NULL THEN 'built' \n"
4458-
" WHEN 'f' = any(es.stxkind) THEN 'requested' \n"
4459-
"END AS \"%s\", \n"
4460-
"CASE WHEN esd.stxdmcv IS NOT NULL THEN 'built' \n"
4461-
" WHEN 'm' = any(es.stxkind) THEN 'requested' \n"
4462-
"END AS \"%s\"",
4463-
gettext_noop("Ndistinct"),
4464-
gettext_noop("Dependencies"),
4465-
gettext_noop("MCV"));
4466-
}
4467-
4468-
/* In verbose mode, print sizes of the extended statistics objects. */
4469-
if (verbose)
4470-
{
4471-
if (pset.sversion<120000)
4472-
{
4473-
appendPQExpBuffer(&buf,
4474-
",\nCASE WHEN es.stxndistinct IS NOT NULL THEN \n"
4475-
" pg_catalog.pg_size_pretty(pg_catalog.length(es.stxndistinct)::bigint) \n"
4476-
" WHEN 'd' = any(es.stxkind) THEN '0 bytes' \n"
4477-
"END AS \"%s\", \n"
4478-
"CASE WHEN es.stxdependencies IS NOT NULL THEN \n"
4479-
" pg_catalog.pg_size_pretty(pg_catalog.length(es.stxdependencies)::bigint) \n"
4480-
" WHEN 'f' = any(es.stxkind) THEN '0 bytes' \n"
4481-
"END AS \"%s\"",
4482-
gettext_noop("Ndistinct_size"),
4483-
gettext_noop("Dependencies_size"));
4484-
}
4485-
else
4486-
{
4487-
appendPQExpBuffer(&buf,
4488-
",\nCASE WHEN esd.stxdndistinct IS NOT NULL THEN \n"
4489-
" pg_catalog.pg_size_pretty(pg_catalog.length(esd.stxdndistinct)::bigint) \n"
4490-
" WHEN 'd' = any(es.stxkind) THEN '0 bytes' \n"
4491-
"END AS \"%s\", \n"
4492-
"CASE WHEN esd.stxddependencies IS NOT NULL THEN \n"
4493-
" pg_catalog.pg_size_pretty(pg_catalog.length(esd.stxddependencies)::bigint) \n"
4494-
" WHEN 'f' = any(es.stxkind) THEN '0 bytes' \n"
4495-
"END AS \"%s\", \n"
4496-
"CASE WHEN esd.stxdmcv IS NOT NULL THEN \n"
4497-
" pg_catalog.pg_size_pretty(pg_catalog.length(esd.stxdmcv)::bigint) \n"
4498-
" WHEN 'm' = any(es.stxkind) THEN '0 bytes' \n"
4499-
"END AS \"%s\"",
4500-
gettext_noop("Ndistinct_size"),
4501-
gettext_noop("Dependencies_size"),
4502-
gettext_noop("MCV_size"));
4503-
}
4504-
}
4505-
4506-
if (pset.sversion<120000)
4507-
{
4508-
appendPQExpBufferStr(&buf,
4509-
" \nFROM pg_catalog.pg_statistic_ext es \n"
4510-
"INNER JOIN pg_catalog.pg_class c \n"
4511-
"ON es.stxrelid = c.oid \n");
4512-
}
4513-
else
4514-
{
4515-
appendPQExpBufferStr(&buf,
4516-
" \nFROM pg_catalog.pg_statistic_ext es \n"
4517-
"LEFT JOIN pg_catalog.pg_statistic_ext_data esd \n"
4518-
"ON es.oid = esd.stxoid \n"
4519-
"INNER JOIN pg_catalog.pg_class c \n"
4520-
"ON es.stxrelid = c.oid \n");
4521-
}
4522-
4523-
processSQLNamePattern(pset.db,&buf,pattern, false,
4524-
false,NULL,
4525-
"es.stxname",NULL,
4526-
NULL);
4527-
4528-
appendPQExpBufferStr(&buf,"ORDER BY 1, 2;");
4529-
4530-
res=PSQLexec(buf.data);
4531-
termPQExpBuffer(&buf);
4532-
if (!res)
4533-
return false;
4534-
4535-
myopt.nullPrint=NULL;
4536-
myopt.title=_("List of extended statistics");
4537-
myopt.translate_header= true;
4538-
4539-
printQuery(res,&myopt,pset.queryFout, false,pset.logfile);
4540-
4541-
PQclear(res);
4542-
return true;
4543-
}
4544-
45454395
/*
45464396
* \dC
45474397
*

‎src/bin/psql/describe.h

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -102,9 +102,6 @@ extern bool listExtensions(const char *pattern);
102102
/* \dx+ */
103103
externboollistExtensionContents(constchar*pattern);
104104

105-
/* \dX */
106-
externboollistExtendedStats(constchar*pattern,boolverbose);
107-
108105
/* \dy */
109106
externboollistEventTriggers(constchar*pattern,boolverbose);
110107

‎src/bin/psql/help.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -267,7 +267,6 @@ slashUsage(unsigned short int pager)
267267
fprintf(output,_(" \\du[S+] [PATTERN] list roles\n"));
268268
fprintf(output,_(" \\dv[S+] [PATTERN] list views\n"));
269269
fprintf(output,_(" \\dx[+] [PATTERN] list extensions\n"));
270-
fprintf(output,_(" \\dX[+] [PATTERN] list extended statistics\n"));
271270
fprintf(output,_(" \\dy [PATTERN] list event triggers\n"));
272271
fprintf(output,_(" \\l[+] [PATTERN] list databases\n"));
273272
fprintf(output,_(" \\sf[+] FUNCNAME show a function's definition\n"));

‎src/bin/psql/tab-complete.c

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1505,7 +1505,7 @@ psql_completion(const char *text, int start, int end)
15051505
"\\dF","\\dFd","\\dFp","\\dFt","\\dg","\\di","\\dl","\\dL",
15061506
"\\dm","\\dn","\\do","\\dO","\\dp","\\dP","\\dPi","\\dPt",
15071507
"\\drds","\\dRs","\\dRp","\\ds","\\dS",
1508-
"\\dt","\\dT","\\dv","\\du","\\dx","\\dX","\\dy",
1508+
"\\dt","\\dT","\\dv","\\du","\\dx","\\dy",
15091509
"\\e","\\echo","\\ef","\\elif","\\else","\\encoding",
15101510
"\\endif","\\errverbose","\\ev",
15111511
"\\f",
@@ -3974,8 +3974,6 @@ psql_completion(const char *text, int start, int end)
39743974
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views,NULL);
39753975
elseif (TailMatchesCS("\\dx*"))
39763976
COMPLETE_WITH_QUERY(Query_for_list_of_extensions);
3977-
elseif (TailMatchesCS("\\dX*"))
3978-
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_statistics,NULL);
39793977
elseif (TailMatchesCS("\\dm*"))
39803978
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews,NULL);
39813979
elseif (TailMatchesCS("\\dE*"))

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

Lines changed: 0 additions & 94 deletions
Original file line numberDiff line numberDiff line change
@@ -1727,100 +1727,6 @@ INSERT INTO tststats.priv_test_tbl
17271727
CREATE STATISTICS tststats.priv_test_stats (mcv) ON a, b
17281728
FROM tststats.priv_test_tbl;
17291729
ANALYZE tststats.priv_test_tbl;
1730-
-- Check printing info about extended statistics by \dX
1731-
create table stts_t1 (a int, b int);
1732-
create statistics stts_1 (ndistinct) on a, b from stts_t1;
1733-
create statistics stts_2 (ndistinct, dependencies) on a, b from stts_t1;
1734-
create statistics stts_3 (ndistinct, dependencies, mcv) on a, b from stts_t1;
1735-
create table stts_t2 (a int, b int, c int);
1736-
create statistics stts_4 on b, c from stts_t2;
1737-
create table stts_t3 (col1 int, col2 int, col3 int);
1738-
create statistics stts_hoge on col1, col2, col3 from stts_t3;
1739-
create schema stts_s1;
1740-
create schema stts_s2;
1741-
create statistics stts_s1.stts_foo on col1, col2 from stts_t3;
1742-
create statistics stts_s2.stts_yama (dependencies, mcv) on col1, col3 from stts_t3;
1743-
insert into stts_t1 select i,i from generate_series(1,100) i;
1744-
analyze stts_t1;
1745-
\dX
1746-
List of extended statistics
1747-
Schema | Name | Definition | Ndistinct | Dependencies | MCV
1748-
----------+------------------------+--------------------------------------+-----------+--------------+-----------
1749-
public | func_deps_stat | a, b, c FROM functional_dependencies | | built |
1750-
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | built
1751-
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | built
1752-
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | built
1753-
public | stts_1 | a, b FROM stts_t1 | built | |
1754-
public | stts_2 | a, b FROM stts_t1 | built | built |
1755-
public | stts_3 | a, b FROM stts_t1 | built | built | built
1756-
public | stts_4 | b, c FROM stts_t2 | requested | requested | requested
1757-
public | stts_hoge | col1, col2, col3 FROM stts_t3 | requested | requested | requested
1758-
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | requested | requested | requested
1759-
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | requested | requested
1760-
tststats | priv_test_stats | a, b FROM tststats.priv_test_tbl | | | built
1761-
(12 rows)
1762-
1763-
\dX stts_?
1764-
List of extended statistics
1765-
Schema | Name | Definition | Ndistinct | Dependencies | MCV
1766-
--------+--------+-------------------+-----------+--------------+-----------
1767-
public | stts_1 | a, b FROM stts_t1 | built | |
1768-
public | stts_2 | a, b FROM stts_t1 | built | built |
1769-
public | stts_3 | a, b FROM stts_t1 | built | built | built
1770-
public | stts_4 | b, c FROM stts_t2 | requested | requested | requested
1771-
(4 rows)
1772-
1773-
\dX *stts_hoge
1774-
List of extended statistics
1775-
Schema | Name | Definition | Ndistinct | Dependencies | MCV
1776-
--------+-----------+-------------------------------+-----------+--------------+-----------
1777-
public | stts_hoge | col1, col2, col3 FROM stts_t3 | requested | requested | requested
1778-
(1 row)
1779-
1780-
\dX+
1781-
List of extended statistics
1782-
Schema | Name | Definition | Ndistinct | Dependencies | MCV | Ndistinct_size | Dependencies_size | MCV_size
1783-
----------+------------------------+--------------------------------------+-----------+--------------+-----------+----------------+-------------------+------------
1784-
public | func_deps_stat | a, b, c FROM functional_dependencies | | built | | | 106 bytes |
1785-
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | built | | | 24 kB
1786-
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | built | | | 386 bytes
1787-
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | built | | | 294 bytes
1788-
public | stts_1 | a, b FROM stts_t1 | built | | | 13 bytes | |
1789-
public | stts_2 | a, b FROM stts_t1 | built | built | | 13 bytes | 40 bytes |
1790-
public | stts_3 | a, b FROM stts_t1 | built | built | built | 13 bytes | 40 bytes | 6126 bytes
1791-
public | stts_4 | b, c FROM stts_t2 | requested | requested | requested | 0 bytes | 0 bytes | 0 bytes
1792-
public | stts_hoge | col1, col2, col3 FROM stts_t3 | requested | requested | requested | 0 bytes | 0 bytes | 0 bytes
1793-
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | requested | requested | requested | 0 bytes | 0 bytes | 0 bytes
1794-
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | requested | requested | | 0 bytes | 0 bytes
1795-
tststats | priv_test_stats | a, b FROM tststats.priv_test_tbl | | | built | | | 686 bytes
1796-
(12 rows)
1797-
1798-
\dX+ stts_?
1799-
List of extended statistics
1800-
Schema | Name | Definition | Ndistinct | Dependencies | MCV | Ndistinct_size | Dependencies_size | MCV_size
1801-
--------+--------+-------------------+-----------+--------------+-----------+----------------+-------------------+------------
1802-
public | stts_1 | a, b FROM stts_t1 | built | | | 13 bytes | |
1803-
public | stts_2 | a, b FROM stts_t1 | built | built | | 13 bytes | 40 bytes |
1804-
public | stts_3 | a, b FROM stts_t1 | built | built | built | 13 bytes | 40 bytes | 6126 bytes
1805-
public | stts_4 | b, c FROM stts_t2 | requested | requested | requested | 0 bytes | 0 bytes | 0 bytes
1806-
(4 rows)
1807-
1808-
\dX+ *stts_hoge
1809-
List of extended statistics
1810-
Schema | Name | Definition | Ndistinct | Dependencies | MCV | Ndistinct_size | Dependencies_size | MCV_size
1811-
--------+-----------+-------------------------------+-----------+--------------+-----------+----------------+-------------------+----------
1812-
public | stts_hoge | col1, col2, col3 FROM stts_t3 | requested | requested | requested | 0 bytes | 0 bytes | 0 bytes
1813-
(1 row)
1814-
1815-
\dX+ stts_s2.stts_yama
1816-
List of extended statistics
1817-
Schema | Name | Definition | Ndistinct | Dependencies | MCV | Ndistinct_size | Dependencies_size | MCV_size
1818-
---------+-----------+-------------------------+-----------+--------------+-----------+----------------+-------------------+----------
1819-
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | requested | requested | | 0 bytes | 0 bytes
1820-
(1 row)
1821-
1822-
drop table stts_t1, stts_t2, stts_t3;
1823-
drop schema stts_s1, stts_s2 cascade;
18241730
-- User with no access
18251731
CREATE USER regress_stats_user1;
18261732
GRANT USAGE ON SCHEMA tststats TO regress_stats_user1;

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

Lines changed: 0 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -914,37 +914,6 @@ CREATE STATISTICS tststats.priv_test_stats (mcv) ON a, b
914914

915915
ANALYZEtststats.priv_test_tbl;
916916

917-
-- Check printing info about extended statistics by \dX
918-
createtablestts_t1 (aint, bint);
919-
create statistics stts_1 (ndistinct)on a, bfrom stts_t1;
920-
create statistics stts_2 (ndistinct, dependencies)on a, bfrom stts_t1;
921-
create statistics stts_3 (ndistinct, dependencies, mcv)on a, bfrom stts_t1;
922-
923-
createtablestts_t2 (aint, bint, cint);
924-
create statistics stts_4on b, cfrom stts_t2;
925-
926-
createtablestts_t3 (col1int, col2int, col3int);
927-
create statistics stts_hogeon col1, col2, col3from stts_t3;
928-
929-
createschemastts_s1;
930-
createschemastts_s2;
931-
create statisticsstts_s1.stts_fooon col1, col2from stts_t3;
932-
create statisticsstts_s2.stts_yama (dependencies, mcv)on col1, col3from stts_t3;
933-
934-
insert into stts_t1select i,ifrom generate_series(1,100) i;
935-
analyze stts_t1;
936-
937-
\dX
938-
\dX stts_?
939-
\dX*stts_hoge
940-
\dX+
941-
\dX+ stts_?
942-
\dX+*stts_hoge
943-
\dX+stts_s2.stts_yama
944-
945-
droptable stts_t1, stts_t2, stts_t3;
946-
dropschema stts_s1, stts_s2 cascade;
947-
948917
-- User with no access
949918
CREATEUSERregress_stats_user1;
950919
GRANT USAGEON SCHEMA tststats TO regress_stats_user1;

0 commit comments

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