diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index b10b734..32e17ee 100644 *** a/doc/src/sgml/perform.sgml --- b/doc/src/sgml/perform.sgml *************** WHERE tablename = 'road'; *** 1132,1139 **** To inspect functional dependencies on a statistics stts, you may do this: ! CREATE STATISTICS stts WITH (dependencies) ! ON (zip, city) FROM zipcodes; ANALYZE zipcodes; SELECT stxname, stxkeys, stxdependencies FROM pg_statistic_ext --- 1132,1139 ---- To inspect functional dependencies on a statistics stts, you may do this: ! CREATE STATISTICS stts (dependencies) ! ON zip, city FROM zipcodes; ANALYZE zipcodes; SELECT stxname, stxkeys, stxdependencies FROM pg_statistic_ext *************** EXPLAIN (ANALYZE, TIMING OFF) SELECT * F *** 1219,1226 **** Continuing the above example, the n-distinct coefficients in a ZIP code table may look like the following: ! CREATE STATISTICS stts2 WITH (ndistinct) ! ON (zip, state, city) FROM zipcodes; ANALYZE zipcodes; SELECT stxkeys AS k, stxndistinct AS nd FROM pg_statistic_ext --- 1219,1226 ---- Continuing the above example, the n-distinct coefficients in a ZIP code table may look like the following: ! 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 11580bf..ef847b9 100644 *** a/doc/src/sgml/planstats.sgml --- b/doc/src/sgml/planstats.sgml *************** EXPLAIN (ANALYZE, TIMING OFF) SELECT * F *** 526,532 **** multivariate statistics on the two columns: ! 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: ! 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 *************** EXPLAIN (ANALYZE, TIMING OFF) SELECT COU *** 569,575 **** calculation, the estimate is much improved: 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: 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 diff --git a/doc/src/sgml/ref/alter_statistics.sgml b/doc/src/sgml/ref/alter_statistics.sgml index 3e4d286..4f25669 100644 *** a/doc/src/sgml/ref/alter_statistics.sgml --- b/doc/src/sgml/ref/alter_statistics.sgml *************** PostgreSQL documentation *** 17,23 **** ALTER STATISTICS ! change the definition of a extended statistics --- 17,23 ---- ALTER STATISTICS ! change the definition of an extended statistics object *************** ALTER STATISTICS name ! The name (optionally schema-qualified) of the statistics to be altered. --- 60,67 ---- name ! The name (optionally schema-qualified) of the statistics object to be ! altered. *************** ALTER STATISTICS new_owner ! The user name of the new owner of the statistics. --- 70,76 ---- new_owner ! The user name of the new owner of the statistics object. *************** ALTER STATISTICS new_name ! The new name for the statistics. --- 79,85 ---- new_name ! The new name for the statistics object. *************** ALTER STATISTICS new_schema ! The new schema for the statistics. --- 88,94 ---- new_schema ! The new schema for the statistics object. *************** ALTER STATISTICS statistics_name ! WITH ( option [= value] [, ... ] ) ! ON ( column_name, column_name [, ...]) FROM table_name --- 22,29 ---- CREATE STATISTICS [ IF NOT EXISTS ] statistics_name ! [ ( statistic_type [, ... ] ) ] ! ON column_name, column_name [, ...] FROM table_name *************** CREATE STATISTICS [ IF NOT EXISTS ] CREATE STATISTICS 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. If a schema name is given (for example, 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. --- 34,50 ---- CREATE STATISTICS will create a new extended statistics ! 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. If a schema name is given (for example, CREATE STATISTICS ! 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. *************** CREATE STATISTICS [ IF NOT EXISTS ] IF NOT EXISTS ! 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. --- 57,66 ---- IF NOT EXISTS ! 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. *************** CREATE STATISTICS [ IF NOT EXISTS ] statistics_name ! The name (optionally schema-qualified) of the statistics to be created. ! ! ! ! ! ! column_name ! ! ! The name of a column to be included in the statistics. ! table_name ! The name (optionally schema-qualified) of the table the statistics should ! be created on. - - - - Parameters - - - statistics parameters - - - - The WITH clause can specify options - for the statistics. Available options are listed below. - - - - ! dependencies (boolean) ! Enables functional dependencies for the statistics. ! ndistinct (boolean) ! Enables ndistinct coefficients for the statistics. ! ! ! --- 69,113 ---- statistics_name ! The name (optionally schema-qualified) of the statistics object to be ! created. ! statistic_type ! A statistic type to be computed in this statistics object. Currently ! supported types are ndistinct, which enables ! n-distinct coefficient tracking, ! and dependencies, which enables functional ! dependencies. ! column_name ! The name of a table column to be included in the statistics object. ! table_name ! The name (optionally schema-qualified) of the table containing the ! column(s) the statistics are computed on. ! *************** CREATE TABLE t1 ( *** 158,164 **** 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; ANALYZE t1; --- 136,142 ---- INSERT INTO t1 SELECT i/100, i/500 FROM generate_series(1,1000000) s(i); ! CREATE STATISTICS s1 (dependencies) ON a, b FROM t1; ANALYZE t1; *************** EXPLAIN ANALYZE SELECT * FROM t1 WHERE ( *** 168,173 **** --- 146,156 ---- -- invalid combination of values EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 1); + + 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. *************** EXPLAIN ANALYZE SELECT * FROM t1 WHERE ( *** 176,182 **** Compatibility ! There's no CREATE STATISTICS command in the SQL standard. --- 159,165 ---- Compatibility ! There is no CREATE STATISTICS command in the SQL standard. diff --git a/doc/src/sgml/ref/drop_statistics.sgml b/doc/src/sgml/ref/drop_statistics.sgml index 98c3381..ef659fc 100644 *** a/doc/src/sgml/ref/drop_statistics.sgml --- b/doc/src/sgml/ref/drop_statistics.sgml *************** DROP STATISTICS [ IF EXISTS ] Description ! DROP STATISTICS removes statistics from the database. ! Only the statistics owner, the schema owner, and superuser can drop a ! statistics. --- 29,37 ---- Description ! DROP STATISTICS removes statistics object(s) from the ! database. Only the statistics object's owner, the schema owner, or a ! superuser can drop a statistics object. *************** DROP STATISTICS [ IF EXISTS ] IF EXISTS ! Do not throw an error if the statistics do not exist. A notice is ! issued in this case. --- 44,51 ---- IF EXISTS ! Do not throw an error if the statistics object does not exist. A notice ! is issued in this case. *************** DROP STATISTICS [ IF EXISTS ] name ! The name (optionally schema-qualified) of the statistics to drop. --- 54,60 ---- name ! The name (optionally schema-qualified) of the statistics object to drop. *************** DROP STATISTICS [ IF EXISTS ] Examples ! To destroy two statistics objects on different schemas, without failing if they don't exist: --- 66,72 ---- Examples ! To destroy two statistics objects in different schemas, without failing if they don't exist: *************** DROP STATISTICS IF EXISTS *** 82,88 **** Compatibility ! There's no DROP STATISTICS command in the SQL standard. --- 82,88 ---- Compatibility ! There is no DROP STATISTICS command in the SQL standard. diff --git a/src/backend/commands/statscmds.c b/src/backend/commands/statscmds.c index 0b9c33e..662b4fa 100644 *** a/src/backend/commands/statscmds.c --- b/src/backend/commands/statscmds.c *************** CreateStatistics(CreateStatsStmt *stmt) *** 60,66 **** bool nulls[Natts_pg_statistic_ext]; int2vector *stxkeys; Relation statrel; ! Relation rel; Oid relid; ObjectAddress parentobject, childobject; --- 60,66 ---- bool nulls[Natts_pg_statistic_ext]; int2vector *stxkeys; Relation statrel; ! Relation rel = NULL; Oid relid; ObjectAddress parentobject, childobject; *************** CreateStatistics(CreateStatsStmt *stmt) *** 71,77 **** bool build_dependencies; bool requested_type = false; int i; ! ListCell *l; Assert(IsA(stmt, CreateStatsStmt)); --- 71,77 ---- bool build_dependencies; bool requested_type = false; int i; ! ListCell *cell; Assert(IsA(stmt, CreateStatsStmt)); *************** CreateStatistics(CreateStatsStmt *stmt) *** 101,135 **** } /* ! * 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(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) ereport(ERROR, ! (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("relation \"%s\" is not a table, foreign table, or materialized view", ! RelationGetRelationName(rel)))); /* ! * Transform column names to array of attnums. While at it, enforce some ! * constraints. */ ! foreach(l, stmt->keys) { ! char *attname = strVal(lfirst(l)); HeapTuple atttuple; Form_pg_attribute attForm; TypeCacheEntry *type; atttuple = SearchSysCacheAttName(relid, attname); if (!HeapTupleIsValid(atttuple)) ereport(ERROR, --- 101,181 ---- } /* ! * 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. */ ! if (list_length(stmt->relations) != 1) ereport(ERROR, ! (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); /* ! * 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(cell, stmt->exprs) { ! 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, *************** CreateStatistics(CreateStatsStmt *stmt) *** 194,223 **** stxkeys = buildint2vector(attnums, numcols); /* ! * Parse the statistics options. Currently only statistics types are ! * recognized. */ 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) --- 240,268 ---- stxkeys = buildint2vector(attnums, numcols); /* ! * Parse the statistics types. */ build_ndistinct = false; build_dependencies = false; ! foreach(cell, stmt->stat_types) { ! char *type = strVal((Value *) lfirst(cell)); ! 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) *************** CreateStatistics(CreateStatsStmt *stmt) *** 268,273 **** --- 313,320 ---- /* * 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 2d2a9d0..d13a6fc 100644 *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** _copyCreateStatsStmt(const CreateStatsSt *** 3389,3397 **** CreateStatsStmt *newnode = makeNode(CreateStatsStmt); COPY_NODE_FIELD(defnames); ! COPY_NODE_FIELD(relation); ! COPY_NODE_FIELD(keys); ! COPY_NODE_FIELD(options); COPY_SCALAR_FIELD(if_not_exists); return newnode; --- 3389,3397 ---- CreateStatsStmt *newnode = makeNode(CreateStatsStmt); COPY_NODE_FIELD(defnames); ! 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 b5459cd..c9a8c34 100644 *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** static bool *** 1349,1357 **** _equalCreateStatsStmt(const CreateStatsStmt *a, const CreateStatsStmt *b) { COMPARE_NODE_FIELD(defnames); ! COMPARE_NODE_FIELD(relation); ! COMPARE_NODE_FIELD(keys); ! COMPARE_NODE_FIELD(options); COMPARE_SCALAR_FIELD(if_not_exists); return true; --- 1349,1357 ---- _equalCreateStatsStmt(const CreateStatsStmt *a, const CreateStatsStmt *b) { COMPARE_NODE_FIELD(defnames); ! 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 98f6768..3d5b09a 100644 *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** _outCreateStatsStmt(StringInfo str, cons *** 2639,2647 **** WRITE_NODE_TYPE("CREATESTATSSTMT"); WRITE_NODE_FIELD(defnames); ! WRITE_NODE_FIELD(relation); ! WRITE_NODE_FIELD(keys); ! WRITE_NODE_FIELD(options); WRITE_BOOL_FIELD(if_not_exists); } --- 2639,2647 ---- WRITE_NODE_TYPE("CREATESTATSSTMT"); WRITE_NODE_FIELD(defnames); ! 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 65c004c..c60b048 100644 *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** ExistingIndex: USING INDEX index_name *** 3836,3866 **** /***************************************************************************** * * 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; ! } ; /***************************************************************************** --- 3836,3864 ---- /***************************************************************************** * * QUERY : ! * 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 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 cbde1ff..983b980 100644 *** a/src/backend/utils/adt/ruleutils.c --- b/src/backend/utils/adt/ruleutils.c *************** pg_get_statisticsext_worker(Oid statexti *** 1504,1518 **** } /* ! * 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) *************** pg_get_statisticsext_worker(Oid statexti *** 1521,1527 **** appendStringInfoChar(&buf, ')'); } ! appendStringInfoString(&buf, " ON ("); for (colno = 0; colno < statextrec->stxkeys.dim1; colno++) { --- 1521,1527 ---- appendStringInfoChar(&buf, ')'); } ! appendStringInfoString(&buf, " ON "); for (colno = 0; colno < statextrec->stxkeys.dim1; colno++) { *************** pg_get_statisticsext_worker(Oid statexti *** 1536,1542 **** appendStringInfoString(&buf, quote_identifier(attname)); } ! appendStringInfo(&buf, ") FROM %s", generate_relation_name(statextrec->stxrelid, NIL)); ReleaseSysCache(statexttup); --- 1536,1542 ---- appendStringInfoString(&buf, quote_identifier(attname)); } ! 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 ce0c9ef..f88832e 100644 *** a/src/bin/pg_dump/t/002_pg_dump.pl --- b/src/bin/pg_dump/t/002_pg_dump.pl *************** qr/CREATE TRANSFORM FOR integer LANGUAGE *** 4957,4965 **** 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', regexp => qr/^ ! \QCREATE STATISTICS dump_test.test_ext_stats_no_options ON (col1, col2) FROM test_fifth_table;\E /xms, like => { binary_upgrade => 1, --- 4957,4965 ---- 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', regexp => qr/^ ! \QCREATE STATISTICS dump_test.test_ext_stats_no_options ON col1, col2 FROM test_fifth_table;\E /xms, like => { binary_upgrade => 1, *************** qr/CREATE TRANSFORM FOR integer LANGUAGE *** 4990,4999 **** 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, diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 13395f5..386af61 100644 *** a/src/bin/psql/describe.c --- b/src/bin/psql/describe.c *************** describeOneTableDetails(const char *sche *** 2355,2362 **** { printfPQExpBuffer(&buf, "SELECT oid, " "stxnamespace::pg_catalog.regnamespace AS nsp, " ! "stxname, stxkeys,\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" --- 2355,2363 ---- { printfPQExpBuffer(&buf, "SELECT oid, " + "stxrelid::pg_catalog.regclass, " "stxnamespace::pg_catalog.regnamespace AS nsp, " ! "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" *************** describeOneTableDetails(const char *sche *** 2385,2393 **** printfPQExpBuffer(&buf, " "); /* statistics name (qualified with namespace) */ ! appendPQExpBuffer(&buf, "\"%s.%s\" WITH (", ! PQgetvalue(result, i, 1), ! PQgetvalue(result, i, 2)); /* options */ if (strcmp(PQgetvalue(result, i, 5), "t") == 0) --- 2386,2394 ---- printfPQExpBuffer(&buf, " "); /* statistics name (qualified with namespace) */ ! appendPQExpBuffer(&buf, "\"%s\".\"%s\" (", ! PQgetvalue(result, i, 2), ! PQgetvalue(result, i, 3)); /* options */ if (strcmp(PQgetvalue(result, i, 5), "t") == 0) *************** describeOneTableDetails(const char *sche *** 2401,2408 **** appendPQExpBuffer(&buf, "%sdependencies", gotone ? ", " : ""); } ! appendPQExpBuffer(&buf, ") ON (%s)", ! PQgetvalue(result, i, 4)); printTableAddFooter(&cont, buf.data); } --- 2402,2410 ---- appendPQExpBuffer(&buf, "%sdependencies", gotone ? ", " : ""); } ! 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 3bd5277..7853c25 100644 *** a/src/bin/psql/tab-complete.c --- b/src/bin/psql/tab-complete.c *************** static const SchemaQuery Query_for_list_ *** 453,458 **** --- 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", *************** static const pgsql_thing_t words_after_c *** 1023,1028 **** --- 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}, *************** psql_completion(const char *text, int st *** 1783,1788 **** --- 1799,1808 ---- else if (Matches5("ALTER", "RULE", MatchAny, "ON", MatchAny)) COMPLETE_WITH_CONST("RENAME TO"); + /* ALTER STATISTICS */ + else if (Matches3("ALTER", "STATISTICS", MatchAny)) + COMPLETE_WITH_LIST3("OWNER TO", "RENAME TO", "SET SCHEMA"); + /* ALTER TRIGGER , add ON */ else if (Matches3("ALTER", "TRIGGER", MatchAny)) COMPLETE_WITH_CONST("ON"); *************** psql_completion(const char *text, int st *** 2119,2125 **** {"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}; --- 2139,2146 ---- {"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}; *************** psql_completion(const char *text, int st *** 2383,2388 **** --- 2404,2422 ---- else if (Matches3("CREATE", "SERVER", MatchAny)) COMPLETE_WITH_LIST3("TYPE", "VERSION", "FOREIGN DATA WRAPPER"); + /* CREATE STATISTICS */ + 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")) *************** psql_completion(const char *text, int st *** 2589,2595 **** /* 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) && --- 2623,2629 ---- /* 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) && diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 46c23c2..d396be3 100644 *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** typedef struct CreateStatsStmt *** 2689,2698 **** { 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 */ } CreateStatsStmt; /* ---------------------- --- 2689,2698 ---- { NodeTag type; List *defnames; /* qualified name (list of Value strings) */ ! 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 a81a4ed..28e6916 100644 *** a/src/test/regress/expected/alter_generic.out --- b/src/test/regress/expected/alter_generic.out *************** DROP OPERATOR FAMILY alt_opf18 USING btr *** 501,508 **** -- 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 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) *************** ERROR: must be member of role "regress_ *** 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 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,519 ---- 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 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 *************** SELECT nspname, prsname *** 672,725 **** --- --- Cleanup resources --- 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; --- 673,685 ---- --- --- 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 SCHEMA alt_nsp1 CASCADE; DROP SCHEMA alt_nsp2 CASCADE; 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 40eeeed..dce8508 100644 *** a/src/test/regress/expected/object_address.out --- b/src/test/regress/expected/object_address.out *************** CREATE TRANSFORM FOR int LANGUAGE SQL ( *** 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 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 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 92ac84a..4ccdf21 100644 *** 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 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; 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" *************** ALTER TABLE ab1 DROP COLUMN a; *** 31,44 **** 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) 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; 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; *************** CREATE SERVER extstats_dummy_srv FOREIGN *** 60,83 **** 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 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; *************** EXPLAIN (COSTS off) *** 158,177 **** -> 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; ANALYZE ndistinct; SELECT stxkind, stxndistinct FROM pg_statistic_ext WHERE stxrelid = 'ndistinct'::regclass; --- 183,190 ---- -> Seq Scan on ndistinct (5 rows) -- correct command ! CREATE STATISTICS s10 ON a, b, c FROM ndistinct; ANALYZE ndistinct; SELECT stxkind, stxndistinct FROM pg_statistic_ext WHERE stxrelid = 'ndistinct'::regclass; *************** EXPLAIN (COSTS off) *** 352,358 **** -> Seq Scan on ndistinct (3 rows) - DROP TABLE ndistinct; -- functional dependencies tests CREATE TABLE functional_dependencies ( filler1 TEXT, --- 365,370 ---- *************** EXPLAIN (COSTS OFF) *** 389,395 **** (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'; --- 401,407 ---- (2 rows) -- create statistics ! 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'; *************** EXPLAIN (COSTS OFF) *** 432,438 **** (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'; --- 444,450 ---- (2 rows) -- create statistics ! 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'; *************** EXPLAIN (COSTS OFF) *** 456,459 **** (5 rows) RESET random_page_cost; - DROP TABLE functional_dependencies; --- 468,470 ---- diff --git a/src/test/regress/sql/alter_generic.sql b/src/test/regress/sql/alter_generic.sql index 88e8d7e..342f828 100644 *** a/src/test/regress/sql/alter_generic.sql --- b/src/test/regress/sql/alter_generic.sql *************** DROP OPERATOR FAMILY alt_opf18 USING btr *** 438,445 **** -- 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 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) *************** ALTER STATISTICS alt_stat2 OWNER TO regr *** 448,455 **** 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,456 ---- ALTER STATISTICS alt_stat2 SET SCHEMA alt_nsp2; -- OK SET SESSION AUTHORIZATION regress_alter_user2; ! 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 *************** SELECT nspname, prsname *** 572,583 **** --- --- Cleanup resources --- 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; --- 573,585 ---- --- --- 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 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 6940392..70f5f1c 100644 *** a/src/test/regress/sql/object_address.sql --- b/src/test/regress/sql/object_address.sql *************** CREATE TRANSFORM FOR int LANGUAGE SQL ( *** 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 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 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 72c7659..4050f33 100644 *** a/src/test/regress/sql/stats_ext.sql --- b/src/test/regress/sql/stats_ext.sql *************** SET max_parallel_workers = 0; *** 7,19 **** 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,30 ---- 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; 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'; *************** SELECT pg_get_statisticsextdef(oid) FROM *** 21,29 **** 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; --- 32,40 ---- 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; *************** DROP TABLE ab1; *** 32,38 **** 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 --- 43,49 ---- 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 *************** CREATE FOREIGN TABLE tststats.f (a int, *** 55,74 **** 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; --- 66,85 ---- 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; *************** EXPLAIN (COSTS off) *** 113,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; --- 124,131 ---- EXPLAIN (COSTS off) SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d; -- correct command ! CREATE STATISTICS s10 ON a, b, c FROM ndistinct; ANALYZE ndistinct; *************** EXPLAIN (COSTS off) *** 202,209 **** EXPLAIN (COSTS off) SELECT COUNT(*) FROM ndistinct GROUP BY a, d; - DROP TABLE ndistinct; - -- functional dependencies tests CREATE TABLE functional_dependencies ( filler1 TEXT, --- 201,206 ---- *************** EXPLAIN (COSTS OFF) *** 233,239 **** 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; --- 230,236 ---- SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1' AND c = 1; -- create statistics ! CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies; ANALYZE functional_dependencies; *************** EXPLAIN (COSTS OFF) *** 259,265 **** 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; --- 256,262 ---- SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1' AND c = 1; -- create statistics ! CREATE STATISTICS func_deps_stat (dependencies) ON a, b, c FROM functional_dependencies; ANALYZE functional_dependencies; *************** EXPLAIN (COSTS OFF) *** 270,273 **** SELECT * FROM functional_dependencies WHERE a = 1 AND b = '1' AND c = 1; RESET random_page_cost; - DROP TABLE functional_dependencies; --- 267,269 ----