Tom Lane wrote:
> Hmm ... I'm not sure that I buy that particular argument. If you're
> concerned that the grammar could not handle "FROM x JOIN y USING (z)",
> wouldn't it also have a problem with "FROM x JOIN y ON (z)"?
>
> It might work anyway, since the grammar should know whether ON or USING
> is needed to complete the JOIN clause. But I think you'd better check
> whether the complete join syntax works there, even if we're not going
> to support it now.
Tomas spent some time trying to shoehorn the whole join syntax into the
FROM clause, but stopped once he realized that the joined_table
production uses table_ref, which allow things like TABLESAMPLE, SRFs,
LATERAL, etc which presumably we don't want to accept in CREATE STATS.
I didn't look into it any further. But because of the other
considerations, I did end up changing the ON to FOR.
So the attached is the final version which I intend to push shortly.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
*** a/doc/src/sgml/perform.sgml
--- b/doc/src/sgml/perform.sgml
***************
*** 1132,1138 **** 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;
ANALYZE zipcodes;
SELECT stxname, stxkeys, stxdependencies
--- 1132,1138 ----
To inspect functional dependencies on a statistics
<literal>stts</literal>, you may do this:
<programlisting>
! CREATE STATISTICS stts (dependencies)
ON (zip, city) FROM zipcodes;
ANALYZE zipcodes;
SELECT stxname, stxkeys, stxdependencies
***************
*** 1219,1225 **** 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;
ANALYZE zipcodes;
SELECT stxkeys AS k, stxndistinct AS nd
--- 1219,1225 ----
Continuing the above example, the n-distinct coefficients in a ZIP
code table may look like the following:
<programlisting>
! CREATE STATISTICS stts2 (ndistinct)
ON (zip, state, city) FROM zipcodes;
ANALYZE zipcodes;
SELECT stxkeys AS k, stxndistinct AS nd
*** a/doc/src/sgml/planstats.sgml
--- b/doc/src/sgml/planstats.sgml
***************
*** 526,532 **** 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;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
QUERY PLAN
--- 526,532 ----
multivariate statistics on the two columns:
<programlisting>
! 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,575 **** 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;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
QUERY PLAN
--- 569,575 ----
calculation, the estimate is much improved:
<programlisting>
DROP STATISTICS stts;
! 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
*** a/doc/src/sgml/ref/create_statistics.sgml
--- b/doc/src/sgml/ref/create_statistics.sgml
***************
*** 22,29 **** 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> [, ...])
FROM <replaceable class="PARAMETER">table_name</replaceable>
</synopsis>
--- 22,29 ----
<refsynopsisdiv>
<synopsis>
CREATE STATISTICS [ IF NOT EXISTS ] <replaceable
class="PARAMETER">statistics_name</replaceable>
! [ ( <replaceable class="PARAMETER">statistic_type</replaceable> [, ... ]
) ]
! FOR ( <replaceable class="PARAMETER">column_name</replaceable>,
<replaceable class="PARAMETER">column_name</replaceable> [, ...])
FROM <replaceable class="PARAMETER">table_name</replaceable>
</synopsis>
***************
*** 75,80 **** CREATE STATISTICS [ IF NOT EXISTS ] <replaceable
class="PARAMETER">statistics_na
--- 75,93 ----
</varlistentry>
<varlistentry>
+ <term><replaceable class="PARAMETER">statistic_type</replaceable></term>
+ <listitem>
+ <para>
+ A statistic type to be enabled for this statistics. 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">column_name</replaceable></term>
<listitem>
<para>
***************
*** 94,135 **** CREATE STATISTICS [ IF NOT EXISTS ] <replaceable
class="PARAMETER">statistics_na
</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>
- <listitem>
- <para>
- Enables functional dependencies for the statistics.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>ndistinct</> (<type>boolean</>)</term>
- <listitem>
- <para>
- Enables ndistinct coefficients for the statistics.
- </para>
- </listitem>
- </varlistentry>
-
- </variablelist>
-
- </refsect2>
</refsect1>
<refsect1>
--- 107,112 ----
*** a/src/backend/commands/statscmds.c
--- b/src/backend/commands/statscmds.c
***************
*** 199,223 **** CreateStatistics(CreateStatsStmt *stmt)
*/
build_ndistinct = false;
build_dependencies = false;
! foreach(l, stmt->options)
{
! DefElem *opt = (DefElem *) lfirst(l);
! if (strcmp(opt->defname, "ndistinct") == 0)
{
! build_ndistinct = defGetBoolean(opt);
requested_type = true;
}
! else if (strcmp(opt->defname, "dependencies") == 0)
{
! build_dependencies = defGetBoolean(opt);
requested_type = true;
}
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("unrecognized STATISTICS option
\"%s\"",
! opt->defname)));
}
/* If no statistic type was specified, build them all. */
if (!requested_type)
--- 199,223 ----
*/
build_ndistinct = false;
build_dependencies = false;
! foreach(l, stmt->stat_types)
{
! char *type = strVal((Value *) lfirst(l));
! if (strcmp(type, "ndistinct") == 0)
{
! build_ndistinct = true;
requested_type = true;
}
! else if (strcmp(type, "dependencies") == 0)
{
! build_dependencies = true;
requested_type = true;
}
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("unrecognized statistics type
\"%s\"",
! type)));
}
/* If no statistic type was specified, build them all. */
if (!requested_type)
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 3397,3402 **** _copyCreateStatsStmt(const CreateStatsStmt *from)
--- 3397,3413 ----
return newnode;
}
+ static CreateStatsArgument *
+ _copyCreateStatsArgument(const CreateStatsArgument *from)
+ {
+ CreateStatsArgument *newnode = makeNode(CreateStatsArgument);
+
+ COPY_SCALAR_FIELD(subtype);
+ COPY_NODE_FIELD(elements);
+
+ return newnode;
+ }
+
static CreateFunctionStmt *
_copyCreateFunctionStmt(const CreateFunctionStmt *from)
{
***************
*** 5121,5126 **** copyObjectImpl(const void *from)
--- 5132,5140 ----
case T_CreateStatsStmt:
retval = _copyCreateStatsStmt(from);
break;
+ case T_CreateStatsArgument:
+ retval = _copyCreateStatsArgument(from);
+ break;
case T_CreateFunctionStmt:
retval = _copyCreateFunctionStmt(from);
break;
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 1358,1363 **** _equalCreateStatsStmt(const CreateStatsStmt *a, const
CreateStatsStmt *b)
--- 1358,1372 ----
}
static bool
+ _equalCreateStatsArgument(const CreateStatsArgument *a, const
CreateStatsArgument *b)
+ {
+ COMPARE_SCALAR_FIELD(subtype);
+ COMPARE_NODE_FIELD(elements);
+
+ return true;
+ }
+
+ static bool
_equalCreateFunctionStmt(const CreateFunctionStmt *a, const
CreateFunctionStmt *b)
{
COMPARE_SCALAR_FIELD(replace);
***************
*** 3270,3275 **** equal(const void *a, const void *b)
--- 3279,3287 ----
case T_CreateStatsStmt:
retval = _equalCreateStatsStmt(a, b);
break;
+ case T_CreateStatsArgument:
+ retval = _equalCreateStatsArgument(a, b);
+ break;
case T_CreateFunctionStmt:
retval = _equalCreateFunctionStmt(a, b);
break;
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 2646,2651 **** _outCreateStatsStmt(StringInfo str, const CreateStatsStmt
*node)
--- 2646,2660 ----
}
static void
+ _outCreateStatsArgument(StringInfo str, const CreateStatsArgument *node)
+ {
+ WRITE_NODE_TYPE("CREATESTATSARG");
+
+ WRITE_INT_FIELD(subtype);
+ WRITE_NODE_FIELD(elements);
+ }
+
+ static void
_outNotifyStmt(StringInfo str, const NotifyStmt *node)
{
WRITE_NODE_TYPE("NOTIFY");
***************
*** 4051,4056 **** outNode(StringInfo str, const void *obj)
--- 4060,4068 ----
case T_CreateStatsStmt:
_outCreateStatsStmt(str, obj);
break;
+ case T_CreateStatsArgument:
+ _outCreateStatsArgument(str, obj);
+ break;
case T_NotifyStmt:
_outNotifyStmt(str, obj);
break;
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 183,188 **** static RangeVar *makeRangeVarFromAnyName(List *names, int
position, core_yyscan_
--- 183,189 ----
static void SplitColQualList(List *qualList,
List **constraintList,
CollateClause **collClause,
core_yyscan_t
yyscanner);
+ static void SplitStatsArgList(CreateStatsStmt *stmt, List *arguments);
static void processCASbits(int cas_bits, int location, const char *constrType,
bool *deferrable, bool *initdeferred, bool
*not_valid,
bool *no_inherit, core_yyscan_t yyscanner);
***************
*** 236,241 **** static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
--- 237,243 ----
AccessPriv *accesspriv;
struct ImportQual *importqual;
InsertStmt *istmt;
+ CreateStatsArgument *cstatarg;
VariableSetStmt *vsetstmt;
PartitionElem *partelem;
PartitionSpec *partspec;
***************
*** 397,402 **** static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
--- 399,406 ----
transform_element_list transform_type_list
TriggerTransitions TriggerReferencing
publication_name_list
+ opt_stats_type_list stats_type_list
StatisticArgList
+ %type <cstatarg> StatisticArgument
%type <list> group_by_list
%type <node> group_by_item empty_grouping_set rollup_clause cube_clause
***************
*** 3836,3868 **** ExistingIndex: USING INDEX index_name
{ $$ = $3; }
/*****************************************************************************
*
* QUERY :
! * CREATE STATISTICS stats_name WITH (options) ON
(columns) FROM relname
*
*****************************************************************************/
! 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;
! }
;
/*****************************************************************************
*
* QUERY :
--- 3840,3913 ----
/*****************************************************************************
*
* QUERY :
! * CREATE STATISTICS stats_name [(stat types)]
arguments
!
! * where 'arguments' can be one or more of:
! * { FOR (columns)
! * | FROM relations
! * | WITH (options)
! * | WHERE expression }
*
*****************************************************************************/
+ CreateStatsStmt:
+ CREATE opt_if_not_exists STATISTICS any_name
+ opt_stats_type_list StatisticArgList
+ {
+ CreateStatsStmt *n =
makeNode(CreateStatsStmt);
+ n->defnames = $4;
+ n->stat_types = $5;
+ n->if_not_exists = $2;
! SplitStatsArgList(n, $6);
! $$ = (Node *)n;
! }
;
+ opt_stats_type_list:
+ '(' stats_type_list ')' { $$ = $2; }
+ | /* EMPTY */ { $$ =
NULL; }
+ ;
+
+ stats_type_list:
+ ColId
{ $$ = list_make1(makeString($1)); }
+ | stats_type_list ',' ColId { $$ =
lappend($1, makeString($3)); }
+ ;
+
+ StatisticArgList:
+ StatisticArgument
{ $$ = list_make1($1); }
+ | StatisticArgList StatisticArgument { $$ =
lappend($1, $2); }
+ ;
+
+ StatisticArgument:
+ FOR '(' name_list ')'
+ {
+ CreateStatsArgument *n =
makeNode(CreateStatsArgument);
+ n->subtype = CSA_Expressions;
+ n->elements = $3;
+ $$ = n;
+ }
+ | FROM qualified_name_list
+ {
+ CreateStatsArgument *n =
makeNode(CreateStatsArgument);
+ n->subtype = CSA_Relations;
+ n->elements = $2;
+ $$ = n;
+ }
+ | WITH reloptions
+ {
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("WITH clause is
not yet implemented")));
+ }
+ | WHERE a_expr
+ {
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("WHERE clause
is not yet implemented")));
+ }
+ ;
+
/*****************************************************************************
*
* QUERY :
***************
*** 15871,15876 **** processCASbits(int cas_bits, int location, const char
*constrType,
--- 15916,15968 ----
}
}
+ /*
+ * Split out CREATE STATISTICS arguments.
+ */
+ static void
+ SplitStatsArgList(CreateStatsStmt *stmt, List *arguments)
+ {
+ ListCell *cell;
+
+ foreach(cell, arguments)
+ {
+ CreateStatsArgument *n = lfirst_node(CreateStatsArgument, cell);
+
+ switch (n->subtype)
+ {
+ case CSA_Relations:
+ if (stmt->relation)
+ ereport(ERROR,
+
(errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("redundant or
conflicting FROM clauses")));
+ if (list_length(n->elements) > 1)
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("statistics
across multiple relations are not supported yet")));
+ stmt->relation = linitial_node(RangeVar,
n->elements);
+ break;
+ case CSA_Expressions:
+ if (stmt->keys)
+ ereport(ERROR,
+
(errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("redundant or
conflicting ON clauses")));
+ stmt->keys = n->elements;
+ break;
+ default:
+ elog(ERROR, "unsupported node type %d",
n->subtype);
+ }
+ }
+
+ if (!stmt->relation)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("missing FROM clause")));
+ if (!stmt->keys)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("missing FOR clause")));
+ }
+
/*----------
* Recursive view transformation
*
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 1504,1518 **** 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
* 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 (");
if (ndistinct_enabled)
appendStringInfoString(&buf, "ndistinct");
else if (dependencies_enabled)
--- 1504,1518 ----
}
/*
! * 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, " (");
if (ndistinct_enabled)
appendStringInfoString(&buf, "ndistinct");
else if (dependencies_enabled)
***************
*** 1521,1527 **** pg_get_statisticsext_worker(Oid statextid, bool missing_ok)
appendStringInfoChar(&buf, ')');
}
! appendStringInfoString(&buf, " ON (");
for (colno = 0; colno < statextrec->stxkeys.dim1; colno++)
{
--- 1521,1527 ----
appendStringInfoChar(&buf, ')');
}
! appendStringInfoString(&buf, " FOR (");
for (colno = 0; colno < statextrec->stxkeys.dim1; colno++)
{
*** a/src/bin/pg_dump/t/002_pg_dump.pl
--- b/src/bin/pg_dump/t/002_pg_dump.pl
***************
*** 4990,4999 **** 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',
regexp => qr/^
! \QCREATE STATISTICS dump_test.test_ext_stats_using WITH
(ndistinct) ON (col1, col2) FROM test_fifth_table;\E
/xms,
like => {
binary_upgrade => 1,
--- 4990,4999 ----
all_runs => 1,
catch_all => 'CREATE ... commands',
create_order => 97,
! 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_opts
(ndistinct) ON (col1, col2) FROM test_fifth_table;\E
/xms,
like => {
binary_upgrade => 1,
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***************
*** 2385,2391 **** describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf, " ");
/* statistics name (qualified with
namespace) */
! appendPQExpBuffer(&buf, "\"%s.%s\" WITH
(",
PQgetvalue(result, i, 1),
PQgetvalue(result, i, 2));
--- 2385,2391 ----
printfPQExpBuffer(&buf, " ");
/* statistics name (qualified with
namespace) */
! appendPQExpBuffer(&buf, "\"%s.%s\" (",
PQgetvalue(result, i, 1),
PQgetvalue(result, i, 2));
***************
*** 2401,2407 **** describeOneTableDetails(const char *schemaname,
appendPQExpBuffer(&buf,
"%sdependencies", gotone ? ", " : "");
}
! appendPQExpBuffer(&buf, ") ON (%s)",
PQgetvalue(result, i, 4));
printTableAddFooter(&cont, buf.data);
--- 2401,2407 ----
appendPQExpBuffer(&buf,
"%sdependencies", gotone ? ", " : "");
}
! appendPQExpBuffer(&buf, ") FOR (%s)",
PQgetvalue(result, i, 4));
printTableAddFooter(&cont, buf.data);
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***************
*** 453,458 **** static const SchemaQuery Query_for_list_of_foreign_tables = {
--- 453,473 ----
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
+ };
+
static const SchemaQuery Query_for_list_of_tables = {
/* catname */
"pg_catalog.pg_class c",
***************
*** 1023,1028 **** static const pgsql_thing_t words_after_create[] = {
--- 1038,1044 ----
{"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},
***************
*** 1386,1391 **** psql_completion(const char *text, int start, int end)
--- 1402,1414 ----
word_matches(p2, previous_words[previous_words_count - 2]) && \
word_matches(p3, previous_words[previous_words_count - 3]))
+ #define HeadMatches4(p1, p2, p3, p4) \
+ (previous_words_count >= 4 && \
+ word_matches(p1, previous_words[previous_words_count - 1]) && \
+ word_matches(p2, previous_words[previous_words_count - 2]) && \
+ word_matches(p3, previous_words[previous_words_count - 3]) && \
+ word_matches(p4, previous_words[previous_words_count - 4]))
+
/* Known command-starting keywords. */
static const char *const sql_commands[] = {
"ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE",
"CLUSTER",
***************
*** 1783,1788 **** psql_completion(const char *text, int start, int end)
--- 1806,1815 ----
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");
***************
*** 2119,2125 **** 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",
"TABLE", "TYPE", "VIEW", "MATERIALIZED VIEW", "COLUMN",
"AGGREGATE", "FUNCTION",
"OPERATOR", "TRIGGER", "CONSTRAINT", "DOMAIN", "LARGE
OBJECT",
"TABLESPACE", "TEXT SEARCH", "ROLE", NULL};
--- 2146,2153 ----
{"ACCESS METHOD", "CAST", "COLLATION", "CONVERSION", "DATABASE",
"EVENT TRIGGER", "EXTENSION",
"FOREIGN DATA WRAPPER", "FOREIGN TABLE",
! "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};
***************
*** 2383,2388 **** psql_completion(const char *text, int start, int end)
--- 2411,2428 ----
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_LIST3("(", "FOR", "FROM");
+ else if (Matches4("CREATE", "STATISTICS", MatchAny, "("))
+ COMPLETE_WITH_LIST2("ndistinct", "dependencies");
+ else if (HeadMatches3("CREATE", "STATISTICS", MatchAny) &&
+ TailMatches1("FOR"))
+ COMPLETE_WITH_CONST("(");
+ 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"))
***************
*** 2589,2595 **** 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",
MatchAny) ||
Matches4("DROP", "ACCESS", "METHOD", MatchAny) ||
(Matches4("DROP", "AGGREGATE|FUNCTION", MatchAny,
MatchAny) &&
--- 2629,2635 ----
/* DROP */
/* Complete DROP object with CASCADE / RESTRICT */
else if (Matches3("DROP",
!
"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) &&
*** a/src/include/nodes/nodes.h
--- b/src/include/nodes/nodes.h
***************
*** 462,467 **** typedef enum NodeTag
--- 462,468 ----
T_InferClause,
T_OnConflictClause,
T_CommonTableExpr,
+ T_CreateStatsArgument,
T_RoleSpec,
T_TriggerTransition,
T_PartitionElem,
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 2689,2700 **** typedef struct CreateStatsStmt
--- 2689,2714 ----
{
NodeTag type;
List *defnames; /* qualified name (list of Value
strings) */
+ List *stat_types; /* stat types (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 */
} CreateStatsStmt;
+ typedef enum CSA_Type
+ {
+ CSA_Relations,
+ CSA_Expressions
+ } CSA_Type;
+
+ typedef struct CreateStatsArgument
+ {
+ NodeTag type;
+ CSA_Type subtype;
+ List *elements; /* elements (list of Node) */
+ } CreateStatsArgument;
+
/* ----------------------
* Create Function Statement
* ----------------------
*** a/src/test/regress/expected/alter_generic.out
--- b/src/test/regress/expected/alter_generic.out
***************
*** 501,508 **** 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;
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)
--- 501,508 ----
--
SET SESSION AUTHORIZATION regress_alter_user1;
CREATE TABLE alt_regress_1 (a INTEGER, b INTEGER);
! CREATE STATISTICS alt_stat1 FOR (a, b) FROM alt_regress_1;
! CREATE STATISTICS alt_stat2 FOR (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,518 **** 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;
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
--- 511,518 ----
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 FOR (a, b) FROM alt_regress_1;
! CREATE STATISTICS alt_stat2 FOR (a, b) FROM alt_regress_1;
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
*** a/src/test/regress/expected/object_address.out
--- b/src/test/regress/expected/object_address.out
***************
*** 39,45 **** CREATE TRANSFORM FOR int LANGUAGE SQL (
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (NOCONNECT,
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;
-- test some error cases
SELECT pg_get_object_address('stone', '{}', '{}');
ERROR: unrecognized object type "stone"
--- 39,45 ----
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (NOCONNECT,
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 FOR (a,b) FROM addr_nsp.gentable;
-- test some error cases
SELECT pg_get_object_address('stone', '{}', '{}');
ERROR: unrecognized object type "stone"
*** a/src/test/regress/expected/stats_ext.out
--- b/src/test/regress/expected/stats_ext.out
***************
*** 5,28 ****
SET max_parallel_workers = 0;
SET max_parallel_workers_per_gather = 0;
SET work_mem = '128kB';
-- 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;
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;
-- 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
(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;
ALTER TABLE ab1 DROP COLUMN a;
\d ab1
Table "public.ab1"
--- 5,53 ----
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 FOR (a, b);
+ ERROR: missing FROM clause
+ CREATE STATISTICS tst FOR (a + b);
+ ERROR: syntax error at or near "+"
+ LINE 1: CREATE STATISTICS tst FOR (a + b);
+ ^
+ CREATE STATISTICS tst FROM sometab;
+ ERROR: missing FOR clause
+ CREATE STATISTICS tst FROM sometab, othertab;
+ ERROR: statistics across multiple relations are not supported yet
+ CREATE STATISTICS tst WITH (fillfactor = 80);
+ ERROR: WITH clause is not yet implemented
+ CREATE STATISTICS tst WHERE mars > earth;
+ ERROR: WHERE clause is not yet implemented
+ CREATE STATISTICS tst FOR (a, b) FROM nonexistant;
+ ERROR: relation "nonexistant" does not exist
+ CREATE STATISTICS tst FOR (a, b) FROM pg_class;
+ ERROR: column "a" referenced in statistics does not exist
+ CREATE STATISTICS tst (unrecognized) FOR (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 FOR (a, b) FROM ab1;
DROP STATISTICS ab1_a_b_stats;
CREATE SCHEMA regress_schema_2;
! CREATE STATISTICS regress_schema_2.ab1_a_b_stats FOR (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 FOR (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 FOR (b, c) FROM ab1;
! CREATE STATISTICS ab1_a_b_c_stats FOR (a, b, c) FROM ab1;
! CREATE STATISTICS ab1_a_b_stats FOR (a, b) FROM ab1;
ALTER TABLE ab1 DROP COLUMN a;
\d ab1
Table "public.ab1"
***************
*** 31,44 **** ALTER TABLE ab1 DROP COLUMN a;
b | integer | | |
c | integer | | |
Statistics:
! "public.ab1_b_c_stats" WITH (ndistinct, dependencies) ON (b, c)
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;
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;
--- 56,69 ----
b | integer | | |
c | integer | | |
Statistics:
! "public.ab1_b_c_stats" (ndistinct, dependencies) FOR (b, c)
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 FOR (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,83 **** 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;
ERROR: relation "ti" is not a table, foreign table, or materialized view
! 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;
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;
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;
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;
EXCEPTION WHEN wrong_object_type THEN
RAISE NOTICE 'stats on toast table not created';
END;
--- 85,108 ----
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 FOR (a, b) FROM tststats.t;
! CREATE STATISTICS tststats.s2 FOR (a, b) FROM tststats.ti;
ERROR: relation "ti" is not a table, foreign table, or materialized view
! CREATE STATISTICS tststats.s3 FOR (a, b) FROM tststats.s;
ERROR: relation "s" is not a table, foreign table, or materialized view
! CREATE STATISTICS tststats.s4 FOR (a, b) FROM tststats.v;
ERROR: relation "v" is not a table, foreign table, or materialized view
! CREATE STATISTICS tststats.s5 FOR (a, b) FROM tststats.mv;
! CREATE STATISTICS tststats.s6 FOR (a, b) FROM tststats.ty;
ERROR: relation "ty" is not a table, foreign table, or materialized view
! CREATE STATISTICS tststats.s7 FOR (a, b) FROM tststats.f;
! CREATE STATISTICS tststats.s8 FOR (a, b) FROM tststats.pt;
! CREATE STATISTICS tststats.s9 FOR (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 FOR (a, b) FROM ' || relname;
EXCEPTION WHEN wrong_object_type THEN
RAISE NOTICE 'stats on toast table not created';
END;
***************
*** 159,177 **** EXPLAIN (COSTS off)
(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;
ANALYZE ndistinct;
SELECT stxkind, stxndistinct
FROM pg_statistic_ext WHERE stxrelid = 'ndistinct'::regclass;
--- 184,202 ----
(5 rows)
-- unknown column
! CREATE STATISTICS s10 FOR (unknown_column) FROM ndistinct;
ERROR: column "unknown_column" referenced in statistics does not exist
-- single column
! CREATE STATISTICS s10 FOR (a) FROM ndistinct;
ERROR: extended statistics require at least 2 columns
-- single column, duplicated
! CREATE STATISTICS s10 FOR (a,a) FROM ndistinct;
ERROR: duplicate column name in statistics definition
-- two columns, one duplicated
! CREATE STATISTICS s10 FOR (a, a, b) FROM ndistinct;
ERROR: duplicate column name in statistics definition
-- correct command
! CREATE STATISTICS s10 FOR (a, b, c) FROM ndistinct;
ANALYZE ndistinct;
SELECT stxkind, stxndistinct
FROM pg_statistic_ext WHERE stxrelid = 'ndistinct'::regclass;
***************
*** 389,395 **** EXPLAIN (COSTS OFF)
(2 rows)
-- create statistics
! CREATE STATISTICS func_deps_stat WITH (dependencies) ON (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1';
--- 414,420 ----
(2 rows)
-- create statistics
! CREATE STATISTICS func_deps_stat (dependencies) FOR (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1';
***************
*** 432,438 **** EXPLAIN (COSTS OFF)
(2 rows)
-- create statistics
! CREATE STATISTICS func_deps_stat WITH (dependencies) ON (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1';
--- 457,463 ----
(2 rows)
-- create statistics
! CREATE STATISTICS func_deps_stat (dependencies) FOR (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
EXPLAIN (COSTS OFF)
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1';
*** a/src/test/regress/sql/alter_generic.sql
--- b/src/test/regress/sql/alter_generic.sql
***************
*** 438,445 **** 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;
ALTER STATISTICS alt_stat1 RENAME TO alt_stat2; -- failed (name conflict)
ALTER STATISTICS alt_stat1 RENAME TO alt_stat3; -- failed (name conflict)
--- 438,445 ----
--
SET SESSION AUTHORIZATION regress_alter_user1;
CREATE TABLE alt_regress_1 (a INTEGER, b INTEGER);
! CREATE STATISTICS alt_stat1 FOR (a, b) FROM alt_regress_1;
! CREATE STATISTICS alt_stat2 FOR (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,455 **** 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;
ALTER STATISTICS alt_stat3 RENAME TO alt_stat4; -- failed (not owner)
ALTER STATISTICS alt_stat1 RENAME TO alt_stat4; -- OK
--- 448,455 ----
ALTER STATISTICS alt_stat2 SET SCHEMA alt_nsp2; -- OK
SET SESSION AUTHORIZATION regress_alter_user2;
! CREATE STATISTICS alt_stat1 FOR (a, b) FROM alt_regress_1;
! CREATE STATISTICS alt_stat2 FOR (a, b) FROM alt_regress_1;
ALTER STATISTICS alt_stat3 RENAME TO alt_stat4; -- failed (not owner)
ALTER STATISTICS alt_stat1 RENAME TO alt_stat4; -- OK
*** a/src/test/regress/sql/object_address.sql
--- b/src/test/regress/sql/object_address.sql
***************
*** 41,47 **** 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 (NOCONNECT,
SLOT NAME = NONE);
! CREATE STATISTICS addr_nsp.gentable_stat ON (a,b) FROM addr_nsp.gentable;
-- test some error cases
SELECT pg_get_object_address('stone', '{}', '{}');
--- 41,47 ----
TO SQL WITH FUNCTION int4recv(internal));
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (NOCONNECT,
SLOT NAME = NONE);
! CREATE STATISTICS addr_nsp.gentable_stat FOR (a,b) FROM addr_nsp.gentable;
-- test some error cases
SELECT pg_get_object_address('stone', '{}', '{}');
*** a/src/test/regress/sql/stats_ext.sql
--- b/src/test/regress/sql/stats_ext.sql
***************
*** 7,19 **** SET max_parallel_workers = 0;
SET max_parallel_workers_per_gather = 0;
SET work_mem = '128kB';
-- 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;
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;
-- 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';
--- 7,31 ----
SET max_parallel_workers_per_gather = 0;
SET work_mem = '128kB';
+ -- Verify failures
+ CREATE STATISTICS tst;
+ CREATE STATISTICS tst FOR (a, b);
+ CREATE STATISTICS tst FOR (a + b);
+ CREATE STATISTICS tst FROM sometab;
+ CREATE STATISTICS tst FROM sometab, othertab;
+ CREATE STATISTICS tst WITH (fillfactor = 80);
+ CREATE STATISTICS tst WHERE mars > earth;
+ CREATE STATISTICS tst FOR (a, b) FROM nonexistant;
+ CREATE STATISTICS tst FOR (a, b) FROM pg_class;
+ CREATE STATISTICS tst (unrecognized) FOR (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 FOR (a, b) FROM ab1;
DROP STATISTICS ab1_a_b_stats;
CREATE SCHEMA regress_schema_2;
! CREATE STATISTICS regress_schema_2.ab1_a_b_stats FOR (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,29 **** 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;
ALTER TABLE ab1 DROP COLUMN a;
\d ab1
DROP TABLE ab1;
--- 33,41 ----
DROP STATISTICS regress_schema_2.ab1_a_b_stats;
-- Ensure statistics are dropped when columns are
! CREATE STATISTICS ab1_b_c_stats FOR (b, c) FROM ab1;
! CREATE STATISTICS ab1_a_b_c_stats FOR (a, b, c) FROM ab1;
! CREATE STATISTICS ab1_a_b_stats FOR (a, b) FROM ab1;
ALTER TABLE ab1 DROP COLUMN a;
\d ab1
DROP TABLE ab1;
***************
*** 32,38 **** 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;
ANALYZE ab1;
ALTER TABLE ab1 ALTER a SET STATISTICS -1;
-- partial analyze doesn't build stats either
--- 44,50 ----
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 FOR (a, b) FROM ab1;
ANALYZE ab1;
ALTER TABLE ab1 ALTER a SET STATISTICS -1;
-- partial analyze doesn't build stats either
***************
*** 55,74 **** 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;
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;
EXCEPTION WHEN wrong_object_type THEN
RAISE NOTICE 'stats on toast table not created';
END;
--- 67,86 ----
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 FOR (a, b) FROM tststats.t;
! CREATE STATISTICS tststats.s2 FOR (a, b) FROM tststats.ti;
! CREATE STATISTICS tststats.s3 FOR (a, b) FROM tststats.s;
! CREATE STATISTICS tststats.s4 FOR (a, b) FROM tststats.v;
! CREATE STATISTICS tststats.s5 FOR (a, b) FROM tststats.mv;
! CREATE STATISTICS tststats.s6 FOR (a, b) FROM tststats.ty;
! CREATE STATISTICS tststats.s7 FOR (a, b) FROM tststats.f;
! CREATE STATISTICS tststats.s8 FOR (a, b) FROM tststats.pt;
! CREATE STATISTICS tststats.s9 FOR (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 FOR (a, b) FROM ' || relname;
EXCEPTION WHEN wrong_object_type THEN
RAISE NOTICE 'stats on toast table not created';
END;
***************
*** 114,132 **** 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;
ANALYZE ndistinct;
--- 126,144 ----
SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
-- unknown column
! CREATE STATISTICS s10 FOR (unknown_column) FROM ndistinct;
-- single column
! CREATE STATISTICS s10 FOR (a) FROM ndistinct;
-- single column, duplicated
! CREATE STATISTICS s10 FOR (a,a) FROM ndistinct;
-- two columns, one duplicated
! CREATE STATISTICS s10 FOR (a, a, b) FROM ndistinct;
-- correct command
! CREATE STATISTICS s10 FOR (a, b, c) FROM ndistinct;
ANALYZE ndistinct;
***************
*** 233,239 **** 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;
ANALYZE functional_dependencies;
--- 245,251 ----
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1' AND c = 1;
-- create statistics
! CREATE STATISTICS func_deps_stat (dependencies) FOR (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
***************
*** 259,265 **** 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;
ANALYZE functional_dependencies;
--- 271,277 ----
SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1' AND c = 1;
-- create statistics
! CREATE STATISTICS func_deps_stat (dependencies) FOR (a, b, c) FROM
functional_dependencies;
ANALYZE functional_dependencies;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
