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
Appearance settings

Commit 624aa2a

Browse filesBrowse files
deanrasheeddeanrasheed
committed
Make the name optional in CREATE STATISTICS.
This allows users to omit the statistics name in a CREATE STATISTICS command, letting the system auto-generate a sensible, unique name, putting the statistics object in the same schema as the table. Simon Riggs, reviewed by Matthias van de Meent. Discussion: https://postgr.es/m/CANbhV-FGD2d_C3zFTfT2aRfX_TaPSgOeKES58RLZx5XzQp5NhA@mail.gmail.com
1 parent fa6c230 commit 624aa2a
Copy full SHA for 624aa2a

File tree

5 files changed

+86
-53
lines changed
Filter options

5 files changed

+86
-53
lines changed

‎doc/src/sgml/ref/create_statistics.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/create_statistics.sgml
+8-4Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -21,11 +21,11 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="parameter">statistics_name</replaceable>
24+
CREATE STATISTICS [ [ IF NOT EXISTS ] <replaceable class="parameter">statistics_name</replaceable> ]
2525
ON ( <replaceable class="parameter">expression</replaceable> )
2626
FROM <replaceable class="parameter">table_name</replaceable>
2727

28-
CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="parameter">statistics_name</replaceable>
28+
CREATE STATISTICS [ [ IF NOT EXISTS ] <replaceable class="parameter">statistics_name</replaceable> ]
2929
[ ( <replaceable class="parameter">statistics_kind</replaceable> [, ... ] ) ]
3030
ON { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) }, { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [, ...]
3131
FROM <replaceable class="parameter">table_name</replaceable>
@@ -60,8 +60,8 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="parameter">statistics_na
6060
If a schema name is given (for example, <literal>CREATE STATISTICS
6161
myschema.mystat ...</literal>) then the statistics object is created in the
6262
specified schema. Otherwise it is created in the current schema.
63-
The name of the statistics object must be distinct from the name of any
64-
other statistics object in the same schema.
63+
If given, the name of the statistics object must be distinct from the name
64+
of any other statistics object in the same schema.
6565
</para>
6666
</refsect1>
6767

@@ -78,6 +78,7 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="parameter">statistics_na
7878
exists. A notice is issued in this case. Note that only the name of
7979
the statistics object is considered here, not the details of its
8080
definition.
81+
Statistics name is required when <literal>IF NOT EXISTS</literal> is specified.
8182
</para>
8283
</listitem>
8384
</varlistentry>
@@ -88,6 +89,9 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="parameter">statistics_na
8889
<para>
8990
The name (optionally schema-qualified) of the statistics object to be
9091
created.
92+
If the name is omitted, <productname>PostgreSQL</productname> chooses a
93+
suitable name based on the parent table's name and the defined column
94+
name(s) and/or expression(s).
9195
</para>
9296
</listitem>
9397
</varlistentry>

‎src/backend/commands/statscmds.c

Copy file name to clipboardExpand all lines: src/backend/commands/statscmds.c
+3-4Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -155,10 +155,9 @@ CreateStatistics(CreateStatsStmt *stmt)
155155

156156
/*
157157
* If the node has a name, split it up and determine creation namespace.
158-
* If not (a possibility not considered by the grammar, but one which can
159-
* occur via the "CREATE TABLE ... (LIKE)" command), then we put the
160-
* object in the same namespace as the relation, and cons up a name for
161-
* it.
158+
* If not, put the object in the same namespace as the relation, and cons
159+
* up a name for it. (This can happen either via "CREATE STATISTICS ..."
160+
* or via "CREATE TABLE ... (LIKE)".)
162161
*/
163162
if (stmt->defnames)
164163
namespaceId = QualifiedNameGetCreationNamespace(stmt->defnames,

‎src/backend/parser/gram.y

Copy file name to clipboardExpand all lines: src/backend/parser/gram.y
+10-3Lines changed: 10 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -434,7 +434,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
434434
old_aggr_definition old_aggr_list
435435
oper_argtypes RuleActionList RuleActionMulti
436436
opt_column_list columnList opt_name_list
437-
sort_clause opt_sort_clause sortby_list index_params stats_params
437+
sort_clause opt_sort_clause sortby_list index_params
438+
opt_stats_name stats_params
438439
opt_include opt_c_include index_including_params
439440
name_list role_list from_clause from_list opt_array_bounds
440441
qualified_name_list any_name any_name_list type_name_list
@@ -4533,7 +4534,7 @@ ExistingIndex: USING INDEX name { $$ = $3; }
45334534
/*****************************************************************************
45344535
*
45354536
* QUERY :
4536-
* CREATE STATISTICS [IF NOT EXISTS] stats_name [(stat types)]
4537+
* CREATE STATISTICS [[IF NOT EXISTS] stats_name] [(stat types)]
45374538
* ON expression-list FROM from_list
45384539
*
45394540
* Note: the expectation here is that the clauses after ON are a subset of
@@ -4545,7 +4546,7 @@ ExistingIndex: USING INDEX name { $$ = $3; }
45454546
*****************************************************************************/
45464547

45474548
CreateStatsStmt:
4548-
CREATE STATISTICS any_name
4549+
CREATE STATISTICS opt_stats_name
45494550
opt_name_list ON stats_params FROM from_list
45504551
{
45514552
CreateStatsStmt *n = makeNode(CreateStatsStmt);
@@ -4573,6 +4574,12 @@ CreateStatsStmt:
45734574
}
45744575
;
45754576

4577+
/* Statistics name is optional unless IF NOT EXISTS is specified */
4578+
opt_stats_name:
4579+
any_name { $$ = $1; }
4580+
| /*EMPTY*/ { $$ = NULL; }
4581+
;
4582+
45764583
/*
45774584
* Statistics attributes can be either simple column references, or arbitrary
45784585
* expressions in parens. For compatibility with index attributes permitted

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

Copy file name to clipboardExpand all lines: src/test/regress/expected/stats_ext.out
+51-36Lines changed: 51 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -3062,11 +3062,11 @@ CREATE STATISTICS tststats.priv_test_stats (mcv) ON a, b
30623062
ANALYZE tststats.priv_test_tbl;
30633063
-- Check printing info about extended statistics by \dX
30643064
create table stts_t1 (a int, b int);
3065-
create statistics stts_1 (ndistinct) on a, b from stts_t1;
3066-
create statistics stts_2 (ndistinct, dependencies) on a, b from stts_t1;
3067-
create statistics stts_3 (ndistinct, dependencies, mcv) on a, b from stts_t1;
3065+
create statistics (ndistinct) on a, b from stts_t1;
3066+
create statistics (ndistinct, dependencies) on a, b from stts_t1;
3067+
create statistics (ndistinct, dependencies, mcv) on a, b from stts_t1;
30683068
create table stts_t2 (a int, b int, c int);
3069-
create statistics stts_4 on b, c from stts_t2;
3069+
create statistics on b, c from stts_t2;
30703070
create table stts_t3 (col1 int, col2 int, col3 int);
30713071
create statistics stts_hoge on col1, col2, col3 from stts_t3;
30723072
create schema stts_s1;
@@ -3084,24 +3084,24 @@ set search_path to public, stts_s1, stts_s2, tststats;
30843084
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
30853085
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
30863086
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
3087-
public | stts_1 | a, b FROM stts_t1 | defined | |
3088-
public | stts_2 | a, b FROM stts_t1 | defined | defined |
3089-
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
3090-
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
30913087
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
3088+
public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | |
3089+
public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined |
3090+
public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined
3091+
public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined
30923092
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
30933093
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
30943094
tststats | priv_test_stats | a, b FROM priv_test_tbl | | | defined
30953095
(12 rows)
30963096

3097-
\dX stts_?
3098-
List of extended statistics
3099-
Schema | Name | Definition | Ndistinct | Dependencies | MCV
3100-
--------+--------+-------------------+-----------+--------------+---------
3101-
public | stts_1 | a, b FROM stts_t1 | defined | |
3102-
public | stts_2 | a, b FROM stts_t1 | defined | defined |
3103-
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
3104-
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
3097+
\dX stts_t*
3098+
List of extended statistics
3099+
Schema | Name | Definition | Ndistinct | Dependencies | MCV
3100+
--------+-------------------+-------------------+-----------+--------------+---------
3101+
public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | |
3102+
public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined |
3103+
public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined
3104+
public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined
31053105
(4 rows)
31063106

31073107
\dX *stts_hoge
@@ -3119,24 +3119,24 @@ set search_path to public, stts_s1, stts_s2, tststats;
31193119
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
31203120
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
31213121
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
3122-
public | stts_1 | a, b FROM stts_t1 | defined | |
3123-
public | stts_2 | a, b FROM stts_t1 | defined | defined |
3124-
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
3125-
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
31263122
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
3123+
public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | |
3124+
public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined |
3125+
public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined
3126+
public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined
31273127
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
31283128
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
31293129
tststats | priv_test_stats | a, b FROM priv_test_tbl | | | defined
31303130
(12 rows)
31313131

3132-
\dX+ stts_?
3133-
List of extended statistics
3134-
Schema | Name | Definition | Ndistinct | Dependencies | MCV
3135-
--------+--------+-------------------+-----------+--------------+---------
3136-
public | stts_1 | a, b FROM stts_t1 | defined | |
3137-
public | stts_2 | a, b FROM stts_t1 | defined | defined |
3138-
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
3139-
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
3132+
\dX+ stts_t*
3133+
List of extended statistics
3134+
Schema | Name | Definition | Ndistinct | Dependencies | MCV
3135+
--------+-------------------+-------------------+-----------+--------------+---------
3136+
public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | |
3137+
public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined |
3138+
public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined
3139+
public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined
31403140
(4 rows)
31413141

31423142
\dX+ *stts_hoge
@@ -3153,6 +3153,21 @@ set search_path to public, stts_s1, stts_s2, tststats;
31533153
stts_s2 | stts_yama | col1, col3 FROM stts_t3 | | defined | defined
31543154
(1 row)
31553155

3156+
create statistics (mcv) ON a, b, (a+b), (a-b) FROM stts_t1;
3157+
create statistics (mcv) ON a, b, (a+b), (a-b) FROM stts_t1;
3158+
create statistics (mcv) ON (a+b), (a-b) FROM stts_t1;
3159+
\dX stts_t*expr*
3160+
List of extended statistics
3161+
Schema | Name | Definition | Ndistinct | Dependencies | MCV
3162+
--------+-----------------------------+-------------------------------------+-----------+--------------+---------
3163+
public | stts_t1_a_b_expr_expr_stat | a, b, (a + b), (a - b) FROM stts_t1 | | | defined
3164+
public | stts_t1_a_b_expr_expr_stat1 | a, b, (a + b), (a - b) FROM stts_t1 | | | defined
3165+
public | stts_t1_expr_expr_stat | (a + b), (a - b) FROM stts_t1 | | | defined
3166+
(3 rows)
3167+
3168+
drop statistics stts_t1_a_b_expr_expr_stat;
3169+
drop statistics stts_t1_a_b_expr_expr_stat1;
3170+
drop statistics stts_t1_expr_expr_stat;
31563171
set search_path to public, stts_s1;
31573172
\dX
31583173
List of extended statistics
@@ -3162,11 +3177,11 @@ set search_path to public, stts_s1;
31623177
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
31633178
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
31643179
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
3165-
public | stts_1 | a, b FROM stts_t1 | defined | |
3166-
public | stts_2 | a, b FROM stts_t1 | defined | defined |
3167-
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
3168-
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
31693180
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
3181+
public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | |
3182+
public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined |
3183+
public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined
3184+
public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined
31703185
stts_s1 | stts_foo | col1, col2 FROM stts_t3 | defined | defined | defined
31713186
(10 rows)
31723187

@@ -3180,11 +3195,11 @@ set role regress_stats_ext;
31803195
public | mcv_lists_arrays_stats | a, b, c FROM mcv_lists_arrays | | | defined
31813196
public | mcv_lists_bool_stats | a, b, c FROM mcv_lists_bool | | | defined
31823197
public | mcv_lists_stats | a, b, d FROM mcv_lists | | | defined
3183-
public | stts_1 | a, b FROM stts_t1 | defined | |
3184-
public | stts_2 | a, b FROM stts_t1 | defined | defined |
3185-
public | stts_3 | a, b FROM stts_t1 | defined | defined | defined
3186-
public | stts_4 | b, c FROM stts_t2 | defined | defined | defined
31873198
public | stts_hoge | col1, col2, col3 FROM stts_t3 | defined | defined | defined
3199+
public | stts_t1_a_b_stat | a, b FROM stts_t1 | defined | |
3200+
public | stts_t1_a_b_stat1 | a, b FROM stts_t1 | defined | defined |
3201+
public | stts_t1_a_b_stat2 | a, b FROM stts_t1 | defined | defined | defined
3202+
public | stts_t2_b_c_stat | b, c FROM stts_t2 | defined | defined | defined
31883203
(9 rows)
31893204

31903205
reset role;

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

Copy file name to clipboardExpand all lines: src/test/regress/sql/stats_ext.sql
+14-6Lines changed: 14 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1555,12 +1555,12 @@ ANALYZE tststats.priv_test_tbl;
15551555

15561556
-- Check printing info about extended statistics by \dX
15571557
create table stts_t1 (a int, b int);
1558-
create statistics stts_1 (ndistinct) on a, b from stts_t1;
1559-
create statistics stts_2 (ndistinct, dependencies) on a, b from stts_t1;
1560-
create statistics stts_3 (ndistinct, dependencies, mcv) on a, b from stts_t1;
1558+
create statistics (ndistinct) on a, b from stts_t1;
1559+
create statistics (ndistinct, dependencies) on a, b from stts_t1;
1560+
create statistics (ndistinct, dependencies, mcv) on a, b from stts_t1;
15611561

15621562
create table stts_t2 (a int, b int, c int);
1563-
create statistics stts_4 on b, c from stts_t2;
1563+
create statistics on b, c from stts_t2;
15641564

15651565
create table stts_t3 (col1 int, col2 int, col3 int);
15661566
create statistics stts_hoge on col1, col2, col3 from stts_t3;
@@ -1575,13 +1575,21 @@ analyze stts_t1;
15751575
set search_path to public, stts_s1, stts_s2, tststats;
15761576

15771577
\dX
1578-
\dX stts_?
1578+
\dX stts_t*
15791579
\dX *stts_hoge
15801580
\dX+
1581-
\dX+ stts_?
1581+
\dX+ stts_t*
15821582
\dX+ *stts_hoge
15831583
\dX+ stts_s2.stts_yama
15841584

1585+
create statistics (mcv) ON a, b, (a+b), (a-b) FROM stts_t1;
1586+
create statistics (mcv) ON a, b, (a+b), (a-b) FROM stts_t1;
1587+
create statistics (mcv) ON (a+b), (a-b) FROM stts_t1;
1588+
\dX stts_t*expr*
1589+
drop statistics stts_t1_a_b_expr_expr_stat;
1590+
drop statistics stts_t1_a_b_expr_expr_stat1;
1591+
drop statistics stts_t1_expr_expr_stat;
1592+
15851593
set search_path to public, stts_s1;
15861594
\dX
15871595

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.