aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/perform.sgml8
-rw-r--r--doc/src/sgml/planstats.sgml4
-rw-r--r--doc/src/sgml/ref/alter_statistics.sgml28
-rw-r--r--doc/src/sgml/ref/create_statistics.sgml85
-rw-r--r--doc/src/sgml/ref/drop_statistics.sgml16
-rw-r--r--src/backend/commands/statscmds.c109
-rw-r--r--src/backend/nodes/copyfuncs.c6
-rw-r--r--src/backend/nodes/equalfuncs.c6
-rw-r--r--src/backend/nodes/outfuncs.c6
-rw-r--r--src/backend/parser/gram.y42
-rw-r--r--src/backend/utils/adt/ruleutils.c10
-rw-r--r--src/bin/pg_dump/t/002_pg_dump.pl10
-rw-r--r--src/bin/psql/describe.c14
-rw-r--r--src/bin/psql/tab-complete.c38
-rw-r--r--src/include/nodes/parsenodes.h8
-rw-r--r--src/test/regress/expected/alter_generic.out52
-rw-r--r--src/test/regress/expected/object_address.out2
-rw-r--r--src/test/regress/expected/stats_ext.out85
-rw-r--r--src/test/regress/sql/alter_generic.sql12
-rw-r--r--src/test/regress/sql/object_address.sql2
-rw-r--r--src/test/regress/sql/stats_ext.sql64
21 files changed, 321 insertions, 286 deletions
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index b10b734b901..32e17ee5f8e 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -1132,8 +1132,8 @@ WHERE tablename = 'road';
To inspect functional dependencies on a statistics
<literal>stts</literal>, you may do this:
<programlisting>
-CREATE STATISTICS stts WITH (dependencies)
- ON (zip, city) FROM zipcodes;
+CREATE STATISTICS stts (dependencies)
+ ON zip, city FROM zipcodes;
ANALYZE zipcodes;
SELECT stxname, stxkeys, stxdependencies
FROM pg_statistic_ext
@@ -1219,8 +1219,8 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 10;
Continuing the above example, the n-distinct coefficients in a ZIP
code table may look like the following:
<programlisting>
-CREATE STATISTICS stts2 WITH (ndistinct)
- ON (zip, state, city) FROM zipcodes;
+CREATE STATISTICS stts2 (ndistinct)
+ ON zip, state, city FROM zipcodes;
ANALYZE zipcodes;
SELECT stxkeys AS k, stxndistinct AS nd
FROM pg_statistic_ext
diff --git a/doc/src/sgml/planstats.sgml b/doc/src/sgml/planstats.sgml
index 11580bfd228..ef847b96333 100644
--- a/doc/src/sgml/planstats.sgml
+++ b/doc/src/sgml/planstats.sgml
@@ -526,7 +526,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
multivariate statistics on the two columns:
<programlisting>
-CREATE STATISTICS stts WITH (dependencies) ON (a, b) FROM t;
+CREATE STATISTICS stts (dependencies) ON a, b FROM t;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
QUERY PLAN
@@ -569,7 +569,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
calculation, the estimate is much improved:
<programlisting>
DROP STATISTICS stts;
-CREATE STATISTICS stts WITH (dependencies, ndistinct) ON (a, b) FROM t;
+CREATE STATISTICS stts (dependencies, ndistinct) ON a, b FROM t;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
QUERY PLAN
diff --git a/doc/src/sgml/ref/alter_statistics.sgml b/doc/src/sgml/ref/alter_statistics.sgml
index 3e4d28614a2..4f256698529 100644
--- a/doc/src/sgml/ref/alter_statistics.sgml
+++ b/doc/src/sgml/ref/alter_statistics.sgml
@@ -17,7 +17,7 @@ PostgreSQL documentation
<refnamediv>
<refname>ALTER STATISTICS</refname>
<refpurpose>
- change the definition of a extended statistics
+ change the definition of an extended statistics object
</refpurpose>
</refnamediv>
@@ -34,19 +34,20 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
<para>
<command>ALTER STATISTICS</command> changes the parameters of an existing
- extended statistics. Any parameters not specifically set in the
+ extended statistics object. Any parameters not specifically set in the
<command>ALTER STATISTICS</command> command retain their prior settings.
</para>
<para>
- You must own the statistics to use <command>ALTER STATISTICS</>.
- To change a statistics' schema, you must also have <literal>CREATE</>
- privilege on the new schema.
+ You must own the statistics object to use <command>ALTER STATISTICS</>.
+ To change a statistics object's schema, you must also
+ have <literal>CREATE</> privilege on the new schema.
To alter the owner, you must also be a direct or indirect member of the new
owning role, and that role must have <literal>CREATE</literal> privilege on
- the statistics' schema. (These restrictions enforce that altering the owner
- doesn't do anything you couldn't do by dropping and recreating the statistics.
- However, a superuser can alter ownership of any statistics anyway.)
+ the statistics object's schema. (These restrictions enforce that altering
+ the owner doesn't do anything you couldn't do by dropping and recreating
+ the statistics object. However, a superuser can alter ownership of any
+ statistics object anyway.)
</para>
</refsect1>
@@ -59,7 +60,8 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
- The name (optionally schema-qualified) of the statistics to be altered.
+ The name (optionally schema-qualified) of the statistics object to be
+ altered.
</para>
</listitem>
</varlistentry>
@@ -68,7 +70,7 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
<term><replaceable class="PARAMETER">new_owner</replaceable></term>
<listitem>
<para>
- The user name of the new owner of the statistics.
+ The user name of the new owner of the statistics object.
</para>
</listitem>
</varlistentry>
@@ -77,7 +79,7 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
<term><replaceable class="parameter">new_name</replaceable></term>
<listitem>
<para>
- The new name for the statistics.
+ The new name for the statistics object.
</para>
</listitem>
</varlistentry>
@@ -86,7 +88,7 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
<term><replaceable class="parameter">new_schema</replaceable></term>
<listitem>
<para>
- The new schema for the statistics.
+ The new schema for the statistics object.
</para>
</listitem>
</varlistentry>
@@ -99,7 +101,7 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
<title>Compatibility</title>
<para>
- There's no <command>ALTER STATISTICS</command> command in the SQL standard.
+ There is no <command>ALTER STATISTICS</command> command in the SQL standard.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/create_statistics.sgml b/doc/src/sgml/ref/create_statistics.sgml
index edbcf5840bb..92ee4e4efa7 100644
--- a/doc/src/sgml/ref/create_statistics.sgml
+++ b/doc/src/sgml/ref/create_statistics.sgml
@@ -22,8 +22,8 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="PARAMETER">statistics_name</replaceable>
- WITH ( <replaceable class="PARAMETER">option</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] )
- ON ( <replaceable class="PARAMETER">column_name</replaceable>, <replaceable class="PARAMETER">column_name</replaceable> [, ...])
+ [ ( <replaceable class="PARAMETER">statistic_type</replaceable> [, ... ] ) ]
+ ON <replaceable class="PARAMETER">column_name</replaceable>, <replaceable class="PARAMETER">column_name</replaceable> [, ...]
FROM <replaceable class="PARAMETER">table_name</replaceable>
</synopsis>
@@ -34,17 +34,17 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="PARAMETER">statistics_na
<para>
<command>CREATE STATISTICS</command> will create a new extended statistics
- object on the specified table, foreign table or materialized view.
- The statistics will be created in the current database and
- will be owned by the user issuing the command.
+ object tracking data about the specified table, foreign table or
+ materialized view. The statistics object will be created in the current
+ database and will be owned by the user issuing the command.
</para>
<para>
If a schema name is given (for example, <literal>CREATE STATISTICS
- myschema.mystat ...</>) then the statistics is created in the specified
- schema. Otherwise it is created in the current schema. The name of
- the statistics must be distinct from the name of any other statistics in the
- same schema.
+ myschema.mystat ...</>) then the statistics object is created in the
+ specified schema. Otherwise it is created in the current schema.
+ The name of the statistics object must be distinct from the name of any
+ other statistics object in the same schema.
</para>
</refsect1>
@@ -57,10 +57,10 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="PARAMETER">statistics_na
<term><literal>IF NOT EXISTS</></term>
<listitem>
<para>
- Do not throw an error if a statistics with the same name already exists.
- A notice is issued in this case. Note that only the name of the
- statistics object is considered here. The definition of the statistics is
- not considered.
+ Do not throw an error if a statistics object with the same name already
+ exists. A notice is issued in this case. Note that only the name of
+ the statistics object is considered here, not the details of its
+ definition.
</para>
</listitem>
</varlistentry>
@@ -69,67 +69,45 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="PARAMETER">statistics_na
<term><replaceable class="PARAMETER">statistics_name</replaceable></term>
<listitem>
<para>
- The name (optionally schema-qualified) of the statistics to be created.
+ The name (optionally schema-qualified) of the statistics object to be
+ created.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable class="PARAMETER">column_name</replaceable></term>
+ <term><replaceable class="PARAMETER">statistic_type</replaceable></term>
<listitem>
<para>
- The name of a column to be included in the statistics.
+ A statistic type to be computed in this statistics object. Currently
+ supported types are <literal>ndistinct</literal>, which enables
+ n-distinct coefficient tracking,
+ and <literal>dependencies</literal>, which enables functional
+ dependencies.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable class="PARAMETER">table_name</replaceable></term>
- <listitem>
- <para>
- The name (optionally schema-qualified) of the table the statistics should
- be created on.
- </para>
- </listitem>
- </varlistentry>
-
- </variablelist>
-
- <refsect2 id="SQL-CREATESTATISTICS-parameters">
- <title id="SQL-CREATESTATISTICS-parameters-title">Parameters</title>
-
- <indexterm zone="sql-createstatistics-parameters">
- <primary>statistics parameters</primary>
- </indexterm>
-
- <para>
- The <literal>WITH</> clause can specify <firstterm>options</>
- for the statistics. Available options are listed below.
- </para>
-
- <variablelist>
-
- <varlistentry>
- <term><literal>dependencies</> (<type>boolean</>)</term>
+ <term><replaceable class="PARAMETER">column_name</replaceable></term>
<listitem>
<para>
- Enables functional dependencies for the statistics.
+ The name of a table column to be included in the statistics object.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>ndistinct</> (<type>boolean</>)</term>
+ <term><replaceable class="PARAMETER">table_name</replaceable></term>
<listitem>
<para>
- Enables ndistinct coefficients for the statistics.
+ The name (optionally schema-qualified) of the table containing the
+ column(s) the statistics are computed on.
</para>
</listitem>
</varlistentry>
- </variablelist>
-
- </refsect2>
+ </variablelist>
</refsect1>
<refsect1>
@@ -158,7 +136,7 @@ CREATE TABLE t1 (
INSERT INTO t1 SELECT i/100, i/500
FROM generate_series(1,1000000) s(i);
-CREATE STATISTICS s1 WITH (dependencies) ON (a, b) FROM t1;
+CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
ANALYZE t1;
@@ -168,6 +146,11 @@ EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
-- invalid combination of values
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 1);
</programlisting>
+
+ Without functional-dependency statistics, the planner would make the
+ same estimate of the number of matching rows for these two queries.
+ With such statistics, it is able to tell that one case has matches
+ and the other does not.
</para>
</refsect1>
@@ -176,7 +159,7 @@ EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 1);
<title>Compatibility</title>
<para>
- There's no <command>CREATE STATISTICS</command> command in the SQL standard.
+ There is no <command>CREATE STATISTICS</command> command in the SQL standard.
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/drop_statistics.sgml b/doc/src/sgml/ref/drop_statistics.sgml
index 98c338182b4..ef659fca614 100644
--- a/doc/src/sgml/ref/drop_statistics.sgml
+++ b/doc/src/sgml/ref/drop_statistics.sgml
@@ -29,9 +29,9 @@ DROP STATISTICS [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
<title>Description</title>
<para>
- <command>DROP STATISTICS</command> removes statistics from the database.
- Only the statistics owner, the schema owner, and superuser can drop a
- statistics.
+ <command>DROP STATISTICS</command> removes statistics object(s) from the
+ database. Only the statistics object's owner, the schema owner, or a
+ superuser can drop a statistics object.
</para>
</refsect1>
@@ -44,8 +44,8 @@ DROP STATISTICS [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
<term><literal>IF EXISTS</literal></term>
<listitem>
<para>
- Do not throw an error if the statistics do not exist. A notice is
- issued in this case.
+ Do not throw an error if the statistics object does not exist. A notice
+ is issued in this case.
</para>
</listitem>
</varlistentry>
@@ -54,7 +54,7 @@ DROP STATISTICS [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
<term><replaceable class="PARAMETER">name</replaceable></term>
<listitem>
<para>
- The name (optionally schema-qualified) of the statistics to drop.
+ The name (optionally schema-qualified) of the statistics object to drop.
</para>
</listitem>
</varlistentry>
@@ -66,7 +66,7 @@ DROP STATISTICS [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
<title>Examples</title>
<para>
- To destroy two statistics objects on different schemas, without failing
+ To destroy two statistics objects in different schemas, without failing
if they don't exist:
<programlisting>
@@ -82,7 +82,7 @@ DROP STATISTICS IF EXISTS
<title>Compatibility</title>
<para>
- There's no <command>DROP STATISTICS</command> command in the SQL standard.
+ There is no <command>DROP STATISTICS</command> command in the SQL standard.
</para>
</refsect1>
diff --git a/src/backend/commands/statscmds.c b/src/backend/commands/statscmds.c
index 0b9c33e30a3..662b4fa15d2 100644
--- a/src/backend/commands/statscmds.c
+++ b/src/backend/commands/statscmds.c
@@ -60,7 +60,7 @@ CreateStatistics(CreateStatsStmt *stmt)
bool nulls[Natts_pg_statistic_ext];
int2vector *stxkeys;
Relation statrel;
- Relation rel;
+ Relation rel = NULL;
Oid relid;
ObjectAddress parentobject,
childobject;
@@ -71,7 +71,7 @@ CreateStatistics(CreateStatsStmt *stmt)
bool build_dependencies;
bool requested_type = false;
int i;
- ListCell *l;
+ ListCell *cell;
Assert(IsA(stmt, CreateStatsStmt));
@@ -101,35 +101,81 @@ CreateStatistics(CreateStatsStmt *stmt)
}
/*
- * CREATE STATISTICS will influence future execution plans but does not
- * interfere with currently executing plans. So it should be enough to
- * take only ShareUpdateExclusiveLock on relation, conflicting with
- * ANALYZE and other DDL that sets statistical information, but not with
- * normal queries.
+ * Examine the FROM clause. Currently, we only allow it to be a single
+ * simple table, but later we'll probably allow multiple tables and JOIN
+ * syntax. The grammar is already prepared for that, so we have to check
+ * here that what we got is what we can support.
*/
- rel = relation_openrv(stmt->relation, ShareUpdateExclusiveLock);
- relid = RelationGetRelid(rel);
-
- if (rel->rd_rel->relkind != RELKIND_RELATION &&
- rel->rd_rel->relkind != RELKIND_MATVIEW &&
- rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE &&
- rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+ if (list_length(stmt->relations) != 1)
ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("relation \"%s\" is not a table, foreign table, or materialized view",
- RelationGetRelationName(rel))));
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only a single relation is allowed in CREATE STATISTICS")));
+
+ foreach(cell, stmt->relations)
+ {
+ Node *rln = (Node *) lfirst(cell);
+
+ if (!IsA(rln, RangeVar))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only a single relation is allowed in CREATE STATISTICS")));
+
+ /*
+ * CREATE STATISTICS will influence future execution plans but does
+ * not interfere with currently executing plans. So it should be
+ * enough to take only ShareUpdateExclusiveLock on relation,
+ * conflicting with ANALYZE and other DDL that sets statistical
+ * information, but not with normal queries.
+ */
+ rel = relation_openrv((RangeVar *) rln, ShareUpdateExclusiveLock);
+
+ /* Restrict to allowed relation types */
+ if (rel->rd_rel->relkind != RELKIND_RELATION &&
+ rel->rd_rel->relkind != RELKIND_MATVIEW &&
+ rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE &&
+ rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("relation \"%s\" is not a table, foreign table, or materialized view",
+ RelationGetRelationName(rel))));
+
+ /* You must own the relation to create stats on it */
+ if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+ RelationGetRelationName(rel));
+ }
+
+ Assert(rel);
+ relid = RelationGetRelid(rel);
/*
- * Transform column names to array of attnums. While at it, enforce some
- * constraints.
+ * Currently, we only allow simple column references in the expression
+ * list. That will change someday, and again the grammar already supports
+ * it so we have to enforce restrictions here. For now, we can convert
+ * the expression list to a simple array of attnums. While at it, enforce
+ * some constraints.
*/
- foreach(l, stmt->keys)
+ foreach(cell, stmt->exprs)
{
- char *attname = strVal(lfirst(l));
+ Node *expr = (Node *) lfirst(cell);
+ ColumnRef *cref;
+ char *attname;
HeapTuple atttuple;
Form_pg_attribute attForm;
TypeCacheEntry *type;
+ if (!IsA(expr, ColumnRef))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only simple column references are allowed in CREATE STATISTICS")));
+ cref = (ColumnRef *) expr;
+
+ if (list_length(cref->fields) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only simple column references are allowed in CREATE STATISTICS")));
+ attname = strVal((Value *) linitial(cref->fields));
+
atttuple = SearchSysCacheAttName(relid, attname);
if (!HeapTupleIsValid(atttuple))
ereport(ERROR,
@@ -194,30 +240,29 @@ CreateStatistics(CreateStatsStmt *stmt)
stxkeys = buildint2vector(attnums, numcols);
/*
- * Parse the statistics options. Currently only statistics types are
- * recognized.
+ * Parse the statistics types.
*/
build_ndistinct = false;
build_dependencies = false;
- foreach(l, stmt->options)
+ foreach(cell, stmt->stat_types)
{
- DefElem *opt = (DefElem *) lfirst(l);
+ char *type = strVal((Value *) lfirst(cell));
- if (strcmp(opt->defname, "ndistinct") == 0)
+ if (strcmp(type, "ndistinct") == 0)
{
- build_ndistinct = defGetBoolean(opt);
+ build_ndistinct = true;
requested_type = true;
}
- else if (strcmp(opt->defname, "dependencies") == 0)
+ else if (strcmp(type, "dependencies") == 0)
{
- build_dependencies = defGetBoolean(opt);
+ build_dependencies = true;
requested_type = true;
}
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("unrecognized STATISTICS option \"%s\"",
- opt->defname)));
+ errmsg("unrecognized statistics type \"%s\"",
+ type)));
}
/* If no statistic type was specified, build them all. */
if (!requested_type)
@@ -268,6 +313,8 @@ CreateStatistics(CreateStatsStmt *stmt)
/*
* Add a dependency on the table, so that stats get dropped on DROP TABLE.
+ *
+ * XXX don't we need dependencies on the specific columns, instead?
*/
ObjectAddressSet(parentobject, RelationRelationId, relid);
ObjectAddressSet(childobject, StatisticExtRelationId, statoid);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 2d2a9d00b7b..d13a6fc03f0 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3389,9 +3389,9 @@ _copyCreateStatsStmt(const CreateStatsStmt *from)
CreateStatsStmt *newnode = makeNode(CreateStatsStmt);
COPY_NODE_FIELD(defnames);
- COPY_NODE_FIELD(relation);
- COPY_NODE_FIELD(keys);
- COPY_NODE_FIELD(options);
+ COPY_NODE_FIELD(stat_types);
+ COPY_NODE_FIELD(exprs);
+ COPY_NODE_FIELD(relations);
COPY_SCALAR_FIELD(if_not_exists);
return newnode;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index b5459cd7260..c9a8c348927 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1349,9 +1349,9 @@ static bool
_equalCreateStatsStmt(const CreateStatsStmt *a, const CreateStatsStmt *b)
{
COMPARE_NODE_FIELD(defnames);
- COMPARE_NODE_FIELD(relation);
- COMPARE_NODE_FIELD(keys);
- COMPARE_NODE_FIELD(options);
+ COMPARE_NODE_FIELD(stat_types);
+ COMPARE_NODE_FIELD(exprs);
+ COMPARE_NODE_FIELD(relations);
COMPARE_SCALAR_FIELD(if_not_exists);
return true;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 98f67681a7d..3d5b09aeeeb 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2639,9 +2639,9 @@ _outCreateStatsStmt(StringInfo str, const CreateStatsStmt *node)
WRITE_NODE_TYPE("CREATESTATSSTMT");
WRITE_NODE_FIELD(defnames);
- WRITE_NODE_FIELD(relation);
- WRITE_NODE_FIELD(keys);
- WRITE_NODE_FIELD(options);
+ WRITE_NODE_FIELD(stat_types);
+ WRITE_NODE_FIELD(exprs);
+ WRITE_NODE_FIELD(relations);
WRITE_BOOL_FIELD(if_not_exists);
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d04bb7ea3eb..28223311e61 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3835,31 +3835,29 @@ ExistingIndex: USING INDEX index_name { $$ = $3; }
/*****************************************************************************
*
* QUERY :
- * CREATE STATISTICS stats_name WITH (options) ON (columns) FROM relname
+ * CREATE STATISTICS stats_name [(stat types)]
+ * ON expression-list FROM from_list
+ *
+ * Note: the expectation here is that the clauses after ON are a subset of
+ * SELECT syntax, allowing for expressions and joined tables, and probably
+ * someday a WHERE clause. Much less than that is currently implemented,
+ * but the grammar accepts it and then we'll throw FEATURE_NOT_SUPPORTED
+ * errors as necessary at execution.
*
*****************************************************************************/
-
-CreateStatsStmt: CREATE STATISTICS any_name opt_reloptions ON '(' columnList ')' FROM qualified_name
- {
- CreateStatsStmt *n = makeNode(CreateStatsStmt);
- n->defnames = $3;
- n->relation = $10;
- n->keys = $7;
- n->options = $4;
- n->if_not_exists = false;
- $$ = (Node *)n;
- }
- | CREATE STATISTICS IF_P NOT EXISTS any_name opt_reloptions ON '(' columnList ')' FROM qualified_name
- {
- CreateStatsStmt *n = makeNode(CreateStatsStmt);
- n->defnames = $6;
- n->relation = $13;
- n->keys = $10;
- n->options = $7;
- n->if_not_exists = true;
- $$ = (Node *)n;
- }
+CreateStatsStmt:
+ CREATE opt_if_not_exists STATISTICS any_name
+ opt_name_list ON expr_list FROM from_list
+ {
+ CreateStatsStmt *n = makeNode(CreateStatsStmt);
+ n->defnames = $4;
+ n->stat_types = $5;
+ n->exprs = $7;
+ n->relations = $9;
+ n->if_not_exists = $2;
+ $$ = (Node *)n;
+ }
;
/*****************************************************************************
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index cbde1fff01c..983b9800ccd 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1504,15 +1504,15 @@ pg_get_statisticsext_worker(Oid statextid, bool missing_ok)
}
/*
- * If any option is disabled, then we'll need to append a WITH clause to
- * show which options are enabled. We omit the WITH clause on purpose
+ * If any option is disabled, then we'll need to append the types clause
+ * to show which options are enabled. We omit the types clause on purpose
* when all options are enabled, so a pg_dump/pg_restore will create all
* statistics types on a newer postgres version, if the statistics had all
* options enabled on the original version.
*/
if (!ndistinct_enabled || !dependencies_enabled)
{
- appendStringInfoString(&buf, " WITH (");
+ appendStringInfoString(&buf, " (");
if (ndistinct_enabled)
appendStringInfoString(&buf, "ndistinct");
else if (dependencies_enabled)
@@ -1521,7 +1521,7 @@ pg_get_statisticsext_worker(Oid statextid, bool missing_ok)
appendStringInfoChar(&buf, ')');
}
- appendStringInfoString(&buf, " ON (");
+ appendStringInfoString(&buf, " ON ");
for (colno = 0; colno < statextrec->stxkeys.dim1; colno++)
{
@@ -1536,7 +1536,7 @@ pg_get_statisticsext_worker(Oid statextid, bool missing_ok)
appendStringInfoString(&buf, quote_identifier(attname));
}
- appendStringInfo(&buf, ") FROM %s",
+ appendStringInfo(&buf, " FROM %s",
generate_relation_name(statextrec->stxrelid, NIL));
ReleaseSysCache(statexttup);
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index b62299929f4..9bd400e067b 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -4955,9 +4955,9 @@ qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL WITH FUNCTION pg_catalog
catch_all => 'CREATE ... commands',
create_order => 97,
create_sql => 'CREATE STATISTICS dump_test.test_ext_stats_no_options
- ON (col1, col2) FROM dump_test.test_fifth_table',
+ ON col1, col2 FROM dump_test.test_fifth_table',
regexp => qr/^
- \QCREATE STATISTICS dump_test.test_ext_stats_no_options ON (col1, col2) FROM test_fifth_table;\E
+ \QCREATE STATISTICS dump_test.test_ext_stats_no_options ON col1, col2 FROM test_fifth_table;\E
/xms,
like => {
binary_upgrade => 1,
@@ -4988,10 +4988,10 @@ qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL WITH FUNCTION pg_catalog
all_runs => 1,
catch_all => 'CREATE ... commands',
create_order => 97,
- create_sql => 'CREATE STATISTICS dump_test.test_ext_stats_using
- WITH (ndistinct) ON (col1, col2) FROM dump_test.test_fifth_table',
+ create_sql => 'CREATE STATISTICS dump_test.test_ext_stats_opts
+ (ndistinct) ON col1, col2 FROM dump_test.test_fifth_table',
regexp => qr/^
- \QCREATE STATISTICS dump_test.test_ext_stats_using WITH (ndistinct) ON (col1, col2) FROM test_fifth_table;\E
+ \QCREATE STATISTICS dump_test.test_ext_stats_opts (ndistinct) ON col1, col2 FROM test_fifth_table;\E
/xms,
like => {
binary_upgrade => 1,
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 13395f5ca67..386af6168fe 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2355,8 +2355,9 @@ describeOneTableDetails(const char *schemaname,
{
printfPQExpBuffer(&buf,
"SELECT oid, "
+ "stxrelid::pg_catalog.regclass, "
"stxnamespace::pg_catalog.regnamespace AS nsp, "
- "stxname, stxkeys,\n"
+ "stxname,\n"
" (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')\n"
" FROM pg_catalog.unnest(stxkeys) s(attnum)\n"
" JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND\n"
@@ -2385,9 +2386,9 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf, " ");
/* statistics name (qualified with namespace) */
- appendPQExpBuffer(&buf, "\"%s.%s\" WITH (",
- PQgetvalue(result, i, 1),
- PQgetvalue(result, i, 2));
+ appendPQExpBuffer(&buf, "\"%s\".\"%s\" (",
+ PQgetvalue(result, i, 2),
+ PQgetvalue(result, i, 3));
/* options */
if (strcmp(PQgetvalue(result, i, 5), "t") == 0)
@@ -2401,8 +2402,9 @@ describeOneTableDetails(const char *schemaname,
appendPQExpBuffer(&buf, "%sdependencies", gotone ? ", " : "");
}
- appendPQExpBuffer(&buf, ") ON (%s)",
- PQgetvalue(result, i, 4));
+ appendPQExpBuffer(&buf, ") ON %s FROM %s",
+ PQgetvalue(result, i, 4),
+ PQgetvalue(result, i, 1));
printTableAddFooter(&cont, buf.data);
}
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 92abcc3ac38..09fb30f270c 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -616,6 +616,21 @@ static const SchemaQuery Query_for_list_of_matviews = {
NULL
};
+static const SchemaQuery Query_for_list_of_statistics = {
+ /* catname */
+ "pg_catalog.pg_statistic_ext s",
+ /* selcondition */
+ NULL,
+ /* viscondition */
+ NULL,
+ /* namespace */
+ "s.stxnamespace",
+ /* result */
+ "pg_catalog.quote_ident(s.stxname)",
+ /* qualresult */
+ NULL
+};
+
/*
* Queries to get lists of names of various kinds of things, possibly
@@ -1023,6 +1038,7 @@ static const pgsql_thing_t words_after_create[] = {
{"SCHEMA", Query_for_list_of_schemas},
{"SEQUENCE", NULL, &Query_for_list_of_sequences},
{"SERVER", Query_for_list_of_servers},
+ {"STATISTICS", NULL, &Query_for_list_of_statistics},
{"SUBSCRIPTION", Query_for_list_of_subscriptions},
{"SYSTEM", NULL, NULL, THING_NO_CREATE | THING_NO_DROP},
{"TABLE", NULL, &Query_for_list_of_tables},
@@ -1782,6 +1798,10 @@ psql_completion(const char *text, int start, int end)
else if (Matches5("ALTER", "RULE", MatchAny, "ON", MatchAny))
COMPLETE_WITH_CONST("RENAME TO");
+ /* ALTER STATISTICS <name> */
+ else if (Matches3("ALTER", "STATISTICS", MatchAny))
+ COMPLETE_WITH_LIST3("OWNER TO", "RENAME TO", "SET SCHEMA");
+
/* ALTER TRIGGER <name>, add ON */
else if (Matches3("ALTER", "TRIGGER", MatchAny))
COMPLETE_WITH_CONST("ON");
@@ -2118,7 +2138,8 @@ psql_completion(const char *text, int start, int end)
{"ACCESS METHOD", "CAST", "COLLATION", "CONVERSION", "DATABASE",
"EVENT TRIGGER", "EXTENSION",
"FOREIGN DATA WRAPPER", "FOREIGN TABLE",
- "SERVER", "INDEX", "LANGUAGE", "POLICY", "PUBLICATION", "RULE", "SCHEMA", "SEQUENCE", "SUBSCRIPTION",
+ "SERVER", "INDEX", "LANGUAGE", "POLICY", "PUBLICATION", "RULE",
+ "SCHEMA", "SEQUENCE", "STATISTICS", "SUBSCRIPTION",
"TABLE", "TYPE", "VIEW", "MATERIALIZED VIEW", "COLUMN", "AGGREGATE", "FUNCTION",
"OPERATOR", "TRIGGER", "CONSTRAINT", "DOMAIN", "LARGE OBJECT",
"TABLESPACE", "TEXT SEARCH", "ROLE", NULL};
@@ -2380,6 +2401,19 @@ psql_completion(const char *text, int start, int end)
else if (Matches3("CREATE", "SERVER", MatchAny))
COMPLETE_WITH_LIST3("TYPE", "VERSION", "FOREIGN DATA WRAPPER");
+/* CREATE STATISTICS <name> */
+ else if (Matches3("CREATE", "STATISTICS", MatchAny))
+ COMPLETE_WITH_LIST2("(", "ON");
+ else if (Matches4("CREATE", "STATISTICS", MatchAny, "("))
+ COMPLETE_WITH_LIST2("ndistinct", "dependencies");
+ else if (HeadMatches3("CREATE", "STATISTICS", MatchAny) &&
+ previous_words[0][0] == '(' &&
+ previous_words[0][strlen(previous_words[0]) - 1] == ')')
+ COMPLETE_WITH_CONST("ON");
+ else if (HeadMatches3("CREATE", "STATISTICS", MatchAny) &&
+ TailMatches1("FROM"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
+
/* CREATE TABLE --- is allowed inside CREATE SCHEMA, so use TailMatches */
/* Complete "CREATE TEMP/TEMPORARY" with the possible temp objects */
else if (TailMatches2("CREATE", "TEMP|TEMPORARY"))
@@ -2585,7 +2619,7 @@ psql_completion(const char *text, int start, int end)
/* DROP */
/* Complete DROP object with CASCADE / RESTRICT */
else if (Matches3("DROP",
- "COLLATION|CONVERSION|DOMAIN|EXTENSION|LANGUAGE|PUBLICATION|SCHEMA|SEQUENCE|SERVER|SUBSCRIPTION|TABLE|TYPE|VIEW",
+ "COLLATION|CONVERSION|DOMAIN|EXTENSION|LANGUAGE|PUBLICATION|SCHEMA|SEQUENCE|SERVER|SUBSCRIPTION|STATISTICS|TABLE|TYPE|VIEW",
MatchAny) ||
Matches4("DROP", "ACCESS", "METHOD", MatchAny) ||
(Matches4("DROP", "AGGREGATE|FUNCTION", MatchAny, MatchAny) &&
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 46c23c25306..d396be382b5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2689,10 +2689,10 @@ typedef struct CreateStatsStmt
{
NodeTag type;
List *defnames; /* qualified name (list of Value strings) */
- RangeVar *relation; /* relation to build statistics on */
- List *keys; /* String nodes naming referenced columns */
- List *options; /* list of DefElem */
- bool if_not_exists; /* do nothing if statistics already exists */
+ List *stat_types; /* stat types (list of Value strings) */
+ List *exprs; /* expressions to build statistics on */
+ List *relations; /* rels to build stats on (list of RangeVar) */
+ bool if_not_exists; /* do nothing if stats name already exists */
} CreateStatsStmt;
/* ----------------------
diff --git a/src/test/regress/expected/alter_generic.out b/src/test/regress/expected/alter_generic.out
index a81a4edfb2b..28e69166beb 100644
--- a/src/test/regress/expected/alter_generic.out
+++ b/src/test/regress/expected/alter_generic.out
@@ -501,8 +501,8 @@ DROP OPERATOR FAMILY alt_opf18 USING btree;
--
SET SESSION AUTHORIZATION regress_alter_user1;
CREATE TABLE alt_regress_1 (a INTEGER, b INTEGER);
-CREATE STATISTICS alt_stat1 ON (a, b) FROM alt_regress_1;
-CREATE STATISTICS alt_stat2 ON (a, b) FROM alt_regress_1;
+CREATE STATISTICS alt_stat1 ON a, b FROM alt_regress_1;
+CREATE STATISTICS alt_stat2 ON a, b FROM alt_regress_1;
ALTER STATISTICS alt_stat1 RENAME TO alt_stat2; -- failed (name conflict)
ERROR: statistics "alt_stat2" already exists in schema "alt_nsp1"
ALTER STATISTICS alt_stat1 RENAME TO alt_stat3; -- failed (name conflict)
@@ -511,8 +511,9 @@ ERROR: must be member of role "regress_alter_user2"
ALTER STATISTICS alt_stat2 OWNER TO regress_alter_user3; -- OK
ALTER STATISTICS alt_stat2 SET SCHEMA alt_nsp2; -- OK
SET SESSION AUTHORIZATION regress_alter_user2;
-CREATE STATISTICS alt_stat1 ON (a, b) FROM alt_regress_1;
-CREATE STATISTICS alt_stat2 ON (a, b) FROM alt_regress_1;
+CREATE TABLE alt_regress_2 (a INTEGER, b INTEGER);
+CREATE STATISTICS alt_stat1 ON a, b FROM alt_regress_2;
+CREATE STATISTICS alt_stat2 ON a, b FROM alt_regress_2;
ALTER STATISTICS alt_stat3 RENAME TO alt_stat4; -- failed (not owner)
ERROR: must be owner of statistics alt_stat3
ALTER STATISTICS alt_stat1 RENAME TO alt_stat4; -- OK
@@ -672,54 +673,13 @@ SELECT nspname, prsname
---
--- Cleanup resources
---
+set client_min_messages to warning; -- suppress cascade notices
DROP FOREIGN DATA WRAPPER alt_fdw2 CASCADE;
-NOTICE: drop cascades to server alt_fserv2
DROP FOREIGN DATA WRAPPER alt_fdw3 CASCADE;
-NOTICE: drop cascades to server alt_fserv3
DROP LANGUAGE alt_lang2 CASCADE;
DROP LANGUAGE alt_lang3 CASCADE;
-DROP LANGUAGE alt_lang4 CASCADE;
-ERROR: language "alt_lang4" does not exist
DROP SCHEMA alt_nsp1 CASCADE;
-NOTICE: drop cascades to 27 other objects
-DETAIL: drop cascades to function alt_func3(integer)
-drop cascades to function alt_agg3(integer)
-drop cascades to function alt_func4(integer)
-drop cascades to function alt_func2(integer)
-drop cascades to function alt_agg4(integer)
-drop cascades to function alt_agg2(integer)
-drop cascades to conversion alt_conv3
-drop cascades to conversion alt_conv4
-drop cascades to conversion alt_conv2
-drop cascades to operator @+@(integer,integer)
-drop cascades to operator @-@(integer,integer)
-drop cascades to operator family alt_opf3 for access method hash
-drop cascades to operator family alt_opc1 for access method hash
-drop cascades to operator family alt_opc2 for access method hash
-drop cascades to operator family alt_opf4 for access method hash
-drop cascades to operator family alt_opf2 for access method hash
-drop cascades to table alt_regress_1
-drop cascades to text search dictionary alt_ts_dict3
-drop cascades to text search dictionary alt_ts_dict4
-drop cascades to text search dictionary alt_ts_dict2
-drop cascades to text search configuration alt_ts_conf3
-drop cascades to text search configuration alt_ts_conf4
-drop cascades to text search configuration alt_ts_conf2
-drop cascades to text search template alt_ts_temp3
-drop cascades to text search template alt_ts_temp2
-drop cascades to text search parser alt_ts_prs3
-drop cascades to text search parser alt_ts_prs2
DROP SCHEMA alt_nsp2 CASCADE;
-NOTICE: drop cascades to 9 other objects
-DETAIL: drop cascades to function alt_nsp2.alt_func2(integer)
-drop cascades to function alt_nsp2.alt_agg2(integer)
-drop cascades to conversion alt_conv2
-drop cascades to operator alt_nsp2.@-@(integer,integer)
-drop cascades to operator family alt_nsp2.alt_opf2 for access method hash
-drop cascades to text search dictionary alt_ts_dict2
-drop cascades to text search configuration alt_ts_conf2
-drop cascades to text search template alt_ts_temp2
-drop cascades to text search parser alt_ts_prs2
DROP USER regress_alter_user1;
DROP USER regress_alter_user2;
DROP USER regress_alter_user3;
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index 700f2618271..3ca5c764ca6 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -39,7 +39,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL (
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
-CREATE STATISTICS addr_nsp.gentable_stat ON (a,b) FROM addr_nsp.gentable;
+CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;
-- test some error cases
SELECT pg_get_object_address('stone', '{}', '{}');
ERROR: unrecognized object type "stone"
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 92ac84ac671..4ccdf21a015 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -5,24 +5,49 @@
SET max_parallel_workers = 0;
SET max_parallel_workers_per_gather = 0;
SET work_mem = '128kB';
+-- Verify failures
+CREATE STATISTICS tst;
+ERROR: syntax error at or near ";"
+LINE 1: CREATE STATISTICS tst;
+ ^
+CREATE STATISTICS tst ON a, b;
+ERROR: syntax error at or near ";"
+LINE 1: CREATE STATISTICS tst ON a, b;
+ ^
+CREATE STATISTICS tst FROM sometab;
+ERROR: syntax error at or near "FROM"
+LINE 1: CREATE STATISTICS tst FROM sometab;
+ ^
+CREATE STATISTICS tst ON a, b FROM nonexistant;
+ERROR: relation "nonexistant" does not exist
+CREATE STATISTICS tst ON a, b FROM pg_class;
+ERROR: column "a" referenced in statistics does not exist
+CREATE STATISTICS tst ON relname, relname, relnatts FROM pg_class;
+ERROR: duplicate column name in statistics definition
+CREATE STATISTICS tst ON relnatts + relpages FROM pg_class;
+ERROR: only simple column references are allowed in CREATE STATISTICS
+CREATE STATISTICS tst ON (relpages, reltuples) FROM pg_class;
+ERROR: only simple column references are allowed in CREATE STATISTICS
+CREATE STATISTICS tst (unrecognized) ON relname, relnatts FROM pg_class;
+ERROR: unrecognized statistics type "unrecognized"
-- Ensure stats are dropped sanely
CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);
-CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
DROP STATISTICS ab1_a_b_stats;
CREATE SCHEMA regress_schema_2;
-CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM ab1;
-- Let's also verify the pg_get_statisticsextdef output looks sane.
SELECT pg_get_statisticsextdef(oid) FROM pg_statistic_ext WHERE stxname = 'ab1_a_b_stats';
- pg_get_statisticsextdef
----------------------------------------------------------------------
- CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON (a, b) FROM ab1
+ pg_get_statisticsextdef
+-------------------------------------------------------------------
+ CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM ab1
(1 row)
DROP STATISTICS regress_schema_2.ab1_a_b_stats;
-- Ensure statistics are dropped when columns are
-CREATE STATISTICS ab1_b_c_stats ON (b, c) FROM ab1;
-CREATE STATISTICS ab1_a_b_c_stats ON (a, b, c) FROM ab1;
-CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS ab1_b_c_stats ON b, c FROM ab1;
+CREATE STATISTICS ab1_a_b_c_stats ON a, b, c FROM ab1;
+CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
ALTER TABLE ab1 DROP COLUMN a;
\d ab1
Table "public.ab1"
@@ -31,14 +56,14 @@ ALTER TABLE ab1 DROP COLUMN a;
b | integer | | |
c | integer | | |
Statistics:
- "public.ab1_b_c_stats" WITH (ndistinct, dependencies) ON (b, c)
+ "public"."ab1_b_c_stats" (ndistinct, dependencies) ON b, c FROM ab1
DROP TABLE ab1;
-- Ensure things work sanely with SET STATISTICS 0
CREATE TABLE ab1 (a INTEGER, b INTEGER);
ALTER TABLE ab1 ALTER a SET STATISTICS 0;
INSERT INTO ab1 SELECT a, a%23 FROM generate_series(1, 1000) a;
-CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
ANALYZE ab1;
WARNING: extended statistics "public.ab1_a_b_stats" could not be collected for relation public.ab1
ALTER TABLE ab1 ALTER a SET STATISTICS -1;
@@ -60,24 +85,24 @@ CREATE SERVER extstats_dummy_srv FOREIGN DATA WRAPPER extstats_dummy_fdw;
CREATE FOREIGN TABLE tststats.f (a int, b int, c text) SERVER extstats_dummy_srv;
CREATE TABLE tststats.pt (a int, b int, c text) PARTITION BY RANGE (a, b);
CREATE TABLE tststats.pt1 PARTITION OF tststats.pt FOR VALUES FROM (-10, -10) TO (10, 10);
-CREATE STATISTICS tststats.s1 ON (a, b) FROM tststats.t;
-CREATE STATISTICS tststats.s2 ON (a, b) FROM tststats.ti;
+CREATE STATISTICS tststats.s1 ON a, b FROM tststats.t;
+CREATE STATISTICS tststats.s2 ON a, b FROM tststats.ti;
ERROR: relation "ti" is not a table, foreign table, or materialized view
-CREATE STATISTICS tststats.s3 ON (a, b) FROM tststats.s;
+CREATE STATISTICS tststats.s3 ON a, b FROM tststats.s;
ERROR: relation "s" is not a table, foreign table, or materialized view
-CREATE STATISTICS tststats.s4 ON (a, b) FROM tststats.v;
+CREATE STATISTICS tststats.s4 ON a, b FROM tststats.v;
ERROR: relation "v" is not a table, foreign table, or materialized view
-CREATE STATISTICS tststats.s5 ON (a, b) FROM tststats.mv;
-CREATE STATISTICS tststats.s6 ON (a, b) FROM tststats.ty;
+CREATE STATISTICS tststats.s5 ON a, b FROM tststats.mv;
+CREATE STATISTICS tststats.s6 ON a, b FROM tststats.ty;
ERROR: relation "ty" is not a table, foreign table, or materialized view
-CREATE STATISTICS tststats.s7 ON (a, b) FROM tststats.f;
-CREATE STATISTICS tststats.s8 ON (a, b) FROM tststats.pt;
-CREATE STATISTICS tststats.s9 ON (a, b) FROM tststats.pt1;
+CREATE STATISTICS tststats.s7 ON a, b FROM tststats.f;
+CREATE STATISTICS tststats.s8 ON a, b FROM tststats.pt;
+CREATE STATISTICS tststats.s9 ON a, b FROM tststats.pt1;
DO $$
DECLARE
relname text := reltoastrelid::regclass FROM pg_class WHERE oid = 'tststats.t'::regclass;
BEGIN
- EXECUTE 'CREATE STATISTICS tststats.s10 ON (a, b) FROM ' || relname;
+ EXECUTE 'CREATE STATISTICS tststats.s10 ON a, b FROM ' || relname;
EXCEPTION WHEN wrong_object_type THEN
RAISE NOTICE 'stats on toast table not created';
END;
@@ -158,20 +183,8 @@ EXPLAIN (COSTS off)
-> Seq Scan on ndistinct
(5 rows)
--- unknown column
-CREATE STATISTICS s10 ON (unknown_column) FROM ndistinct;
-ERROR: column "unknown_column" referenced in statistics does not exist
--- single column
-CREATE STATISTICS s10 ON (a) FROM ndistinct;
-ERROR: extended statistics require at least 2 columns
--- single column, duplicated
-CREATE STATISTICS s10 ON (a,a) FROM ndistinct;
-ERROR: duplicate column name in statistics definition
--- two columns, one duplicated
-CREATE STATISTICS s10 ON (a, a, b) FROM ndistinct;
-ERROR: duplicate column name in statistics definition
-- correct command
-CREATE STATISTICS s10 ON (a, b, c) FROM ndistinct;
+CREATE STATISTICS s10 ON a, b, c FROM ndistinct;
ANALYZE ndistinct;
SELECT stxkind, stxndistinct
FROM pg_statistic_ext WHERE stxrelid = 'ndistinct'::regclass;
@@ -352,7 +365,6 @@ EXPLAIN (COSTS off)
-> Seq Scan on ndistinct
(3 rows)
-DROP TABLE ndistinct;
-- functional dependencies tests
CREATE TABLE functional_dependencies (
filler1 TEXT,
@@ -389,7 +401,7 @@ EXPLAIN (COSTS OFF)
(2 rows)
-- create statistics
-CREATE STATISTICS func_deps_stat WITH (dependencies) ON (a, b, c) FROM functional_dependencies;
+CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
ANALYZE functional_dependencies;
EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1';
@@ -432,7 +444,7 @@ EXPLAIN (COSTS OFF)
(2 rows)
-- create statistics
-CREATE STATISTICS func_deps_stat WITH (dependencies) ON (a, b, c) FROM functional_dependencies;
+CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
ANALYZE functional_dependencies;
EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1';
@@ -456,4 +468,3 @@ EXPLAIN (COSTS OFF)
(5 rows)
RESET random_page_cost;
-DROP TABLE functional_dependencies;
diff --git a/src/test/regress/sql/alter_generic.sql b/src/test/regress/sql/alter_generic.sql
index 88e8d7eb86b..342f82856e1 100644
--- a/src/test/regress/sql/alter_generic.sql
+++ b/src/test/regress/sql/alter_generic.sql
@@ -438,8 +438,8 @@ DROP OPERATOR FAMILY alt_opf18 USING btree;
--
SET SESSION AUTHORIZATION regress_alter_user1;
CREATE TABLE alt_regress_1 (a INTEGER, b INTEGER);
-CREATE STATISTICS alt_stat1 ON (a, b) FROM alt_regress_1;
-CREATE STATISTICS alt_stat2 ON (a, b) FROM alt_regress_1;
+CREATE STATISTICS alt_stat1 ON a, b FROM alt_regress_1;
+CREATE STATISTICS alt_stat2 ON a, b FROM alt_regress_1;
ALTER STATISTICS alt_stat1 RENAME TO alt_stat2; -- failed (name conflict)
ALTER STATISTICS alt_stat1 RENAME TO alt_stat3; -- failed (name conflict)
@@ -448,8 +448,9 @@ ALTER STATISTICS alt_stat2 OWNER TO regress_alter_user3; -- OK
ALTER STATISTICS alt_stat2 SET SCHEMA alt_nsp2; -- OK
SET SESSION AUTHORIZATION regress_alter_user2;
-CREATE STATISTICS alt_stat1 ON (a, b) FROM alt_regress_1;
-CREATE STATISTICS alt_stat2 ON (a, b) FROM alt_regress_1;
+CREATE TABLE alt_regress_2 (a INTEGER, b INTEGER);
+CREATE STATISTICS alt_stat1 ON a, b FROM alt_regress_2;
+CREATE STATISTICS alt_stat2 ON a, b FROM alt_regress_2;
ALTER STATISTICS alt_stat3 RENAME TO alt_stat4; -- failed (not owner)
ALTER STATISTICS alt_stat1 RENAME TO alt_stat4; -- OK
@@ -572,12 +573,13 @@ SELECT nspname, prsname
---
--- Cleanup resources
---
+set client_min_messages to warning; -- suppress cascade notices
+
DROP FOREIGN DATA WRAPPER alt_fdw2 CASCADE;
DROP FOREIGN DATA WRAPPER alt_fdw3 CASCADE;
DROP LANGUAGE alt_lang2 CASCADE;
DROP LANGUAGE alt_lang3 CASCADE;
-DROP LANGUAGE alt_lang4 CASCADE;
DROP SCHEMA alt_nsp1 CASCADE;
DROP SCHEMA alt_nsp2 CASCADE;
diff --git a/src/test/regress/sql/object_address.sql b/src/test/regress/sql/object_address.sql
index 8a738e20d68..f25ed735e1b 100644
--- a/src/test/regress/sql/object_address.sql
+++ b/src/test/regress/sql/object_address.sql
@@ -41,7 +41,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL (
TO SQL WITH FUNCTION int4recv(internal));
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
-CREATE STATISTICS addr_nsp.gentable_stat ON (a,b) FROM addr_nsp.gentable;
+CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;
-- test some error cases
SELECT pg_get_object_address('stone', '{}', '{}');
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 72c7659c4b1..4050f33c088 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -7,13 +7,24 @@ SET max_parallel_workers = 0;
SET max_parallel_workers_per_gather = 0;
SET work_mem = '128kB';
+-- Verify failures
+CREATE STATISTICS tst;
+CREATE STATISTICS tst ON a, b;
+CREATE STATISTICS tst FROM sometab;
+CREATE STATISTICS tst ON a, b FROM nonexistant;
+CREATE STATISTICS tst ON a, b FROM pg_class;
+CREATE STATISTICS tst ON relname, relname, relnatts FROM pg_class;
+CREATE STATISTICS tst ON relnatts + relpages FROM pg_class;
+CREATE STATISTICS tst ON (relpages, reltuples) FROM pg_class;
+CREATE STATISTICS tst (unrecognized) ON relname, relnatts FROM pg_class;
+
-- Ensure stats are dropped sanely
CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);
-CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
DROP STATISTICS ab1_a_b_stats;
CREATE SCHEMA regress_schema_2;
-CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM ab1;
-- Let's also verify the pg_get_statisticsextdef output looks sane.
SELECT pg_get_statisticsextdef(oid) FROM pg_statistic_ext WHERE stxname = 'ab1_a_b_stats';
@@ -21,9 +32,9 @@ SELECT pg_get_statisticsextdef(oid) FROM pg_statistic_ext WHERE stxname = 'ab1_a
DROP STATISTICS regress_schema_2.ab1_a_b_stats;
-- Ensure statistics are dropped when columns are
-CREATE STATISTICS ab1_b_c_stats ON (b, c) FROM ab1;
-CREATE STATISTICS ab1_a_b_c_stats ON (a, b, c) FROM ab1;
-CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS ab1_b_c_stats ON b, c FROM ab1;
+CREATE STATISTICS ab1_a_b_c_stats ON a, b, c FROM ab1;
+CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
ALTER TABLE ab1 DROP COLUMN a;
\d ab1
DROP TABLE ab1;
@@ -32,7 +43,7 @@ DROP TABLE ab1;
CREATE TABLE ab1 (a INTEGER, b INTEGER);
ALTER TABLE ab1 ALTER a SET STATISTICS 0;
INSERT INTO ab1 SELECT a, a%23 FROM generate_series(1, 1000) a;
-CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
+CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
ANALYZE ab1;
ALTER TABLE ab1 ALTER a SET STATISTICS -1;
-- partial analyze doesn't build stats either
@@ -55,20 +66,20 @@ CREATE FOREIGN TABLE tststats.f (a int, b int, c text) SERVER extstats_dummy_srv
CREATE TABLE tststats.pt (a int, b int, c text) PARTITION BY RANGE (a, b);
CREATE TABLE tststats.pt1 PARTITION OF tststats.pt FOR VALUES FROM (-10, -10) TO (10, 10);
-CREATE STATISTICS tststats.s1 ON (a, b) FROM tststats.t;
-CREATE STATISTICS tststats.s2 ON (a, b) FROM tststats.ti;
-CREATE STATISTICS tststats.s3 ON (a, b) FROM tststats.s;
-CREATE STATISTICS tststats.s4 ON (a, b) FROM tststats.v;
-CREATE STATISTICS tststats.s5 ON (a, b) FROM tststats.mv;
-CREATE STATISTICS tststats.s6 ON (a, b) FROM tststats.ty;
-CREATE STATISTICS tststats.s7 ON (a, b) FROM tststats.f;
-CREATE STATISTICS tststats.s8 ON (a, b) FROM tststats.pt;
-CREATE STATISTICS tststats.s9 ON (a, b) FROM tststats.pt1;
+CREATE STATISTICS tststats.s1 ON a, b FROM tststats.t;
+CREATE STATISTICS tststats.s2 ON a, b FROM tststats.ti;
+CREATE STATISTICS tststats.s3 ON a, b FROM tststats.s;
+CREATE STATISTICS tststats.s4 ON a, b FROM tststats.v;
+CREATE STATISTICS tststats.s5 ON a, b FROM tststats.mv;
+CREATE STATISTICS tststats.s6 ON a, b FROM tststats.ty;
+CREATE STATISTICS tststats.s7 ON a, b FROM tststats.f;
+CREATE STATISTICS tststats.s8 ON a, b FROM tststats.pt;
+CREATE STATISTICS tststats.s9 ON a, b FROM tststats.pt1;
DO $$
DECLARE
relname text := reltoastrelid::regclass FROM pg_class WHERE oid = 'tststats.t'::regclass;
BEGIN
- EXECUTE 'CREATE STATISTICS tststats.s10 ON (a, b) FROM ' || relname;
+ EXECUTE 'CREATE STATISTICS tststats.s10 ON a, b FROM ' || relname;
EXCEPTION WHEN wrong_object_type THEN
RAISE NOTICE 'stats on toast table not created';
END;
@@ -113,20 +124,8 @@ EXPLAIN (COSTS off)
EXPLAIN (COSTS off)
SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
--- unknown column
-CREATE STATISTICS s10 ON (unknown_column) FROM ndistinct;
-
--- single column
-CREATE STATISTICS s10 ON (a) FROM ndistinct;
-
--- single column, duplicated
-CREATE STATISTICS s10 ON (a,a) FROM ndistinct;
-
--- two columns, one duplicated
-CREATE STATISTICS s10 ON (a, a, b) FROM ndistinct;
-
-- correct command
-CREATE STATISTICS s10 ON (a, b, c) FROM ndistinct;
+CREATE STATISTICS s10 ON a, b, c FROM ndistinct;
ANALYZE ndistinct;
@@ -202,8 +201,6 @@ EXPLAIN (COSTS off)
EXPLAIN (COSTS off)
SELECT COUNT(*) FROM ndistinct GROUP BY a, d;
-DROP TABLE ndistinct;
-
-- functional dependencies tests
CREATE TABLE functional_dependencies (
filler1 TEXT,
@@ -233,7 +230,7 @@ EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1' AND c = 1;
-- create statistics
-CREATE STATISTICS func_deps_stat WITH (dependencies) ON (a, b, c) FROM functional_dependencies;
+CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
ANALYZE functional_dependencies;
@@ -259,7 +256,7 @@ EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1' AND c = 1;
-- create statistics
-CREATE STATISTICS func_deps_stat WITH (dependencies) ON (a, b, c) FROM functional_dependencies;
+CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies;
ANALYZE functional_dependencies;
@@ -270,4 +267,3 @@ EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1' AND c = 1;
RESET random_page_cost;
-DROP TABLE functional_dependencies;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 10:44:34 +0000