On mån, 2010-07-26 at 10:46 -0600, Alex Hunsaker wrote: > On Sat, Jul 24, 2010 at 06:23, Peter Eisentraut <pete...@gmx.net> wrote: > > > Another open question I thought of was whether we should put the > > dependency record on the pg_index row, or the pg_constraint row, or > > perhaps the pg_class row. Right now, it is using pg_index, because that > > was easiest to code up, but I suspect that once we have not-null > > constraints in pg_constraint, it will be more consistent to make all > > dependencies go against pg_constraint rather than a mix of several > > catalogs. > > I think for primary keys pg_index is OK. However for the not-null > case we have to use pg_constraint... So given that we end up having to > code that anyways, it seems like it will end up being > cleaner/consistent to always use the pg_constraint row(s). So +1 for > using pg_constraint instead of pg_index from me.
Next version. Changed dependencies to pg_constraint, removed handling of unique constraints for now, and made some enhancements so that views track dependencies on constraints even in subqueries. Should be close to final now. :-)
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index 416e599..a103229 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -886,10 +886,7 @@ SELECT product_id, p.name, (sum(s.units) * p.price) AS sales In this example, the columns <literal>product_id</literal>, <literal>p.name</literal>, and <literal>p.price</literal> must be in the <literal>GROUP BY</> clause since they are referenced in - the query select list. (Depending on how the products - table is set up, name and price might be fully dependent on the - product ID, so the additional groupings could theoretically be - unnecessary, though this is not implemented.) The column + the query select list (but see below). The column <literal>s.units</> does not have to be in the <literal>GROUP BY</> list since it is only used in an aggregate expression (<literal>sum(...)</literal>), which represents the sales @@ -898,6 +895,18 @@ SELECT product_id, p.name, (sum(s.units) * p.price) AS sales </para> <para> + If the products table is set up so that, + say, <literal>product_id</literal> is the primary key, then it + would be enough to group by <literal>product_id</literal> in the + above example, since name and price would + be <firstterm>functionally + dependent</firstterm><indexterm><primary>functional + dependency</primary></indexterm> on the product ID, and so there + would be no ambiguity about which name and price value to return + for each product ID group. + </para> + + <para> In strict SQL, <literal>GROUP BY</> can only group by columns of the source table but <productname>PostgreSQL</productname> extends this to also allow <literal>GROUP BY</> to group by columns in the diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 74021e8..8436d85 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -520,9 +520,12 @@ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] produces a single value computed across all the selected rows). When <literal>GROUP BY</literal> is present, it is not valid for the <command>SELECT</command> list expressions to refer to - ungrouped columns except within aggregate functions, since there - would be more than one possible value to return for an ungrouped - column. + ungrouped columns except within aggregate functions or if the + ungrouped column is functionally dependent on the grouped columns, + since there would otherwise be more than one possible value to + return for an ungrouped column. A functional dependency exists if + the grouped columns (or a subset thereof) are the primary key of + the table containing the ungrouped column. </para> </refsect2> diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index 67c90a2..455fc6e 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -16,7 +16,9 @@ #include "access/heapam.h" #include "access/xact.h" +#include "catalog/dependency.h" #include "catalog/namespace.h" +#include "catalog/pg_constraint.h" #include "commands/defrem.h" #include "commands/tablecmds.h" #include "commands/view.h" @@ -390,6 +392,28 @@ UpdateRangeTableOfViewParse(Oid viewOid, Query *viewParse) } /* + * Walker to collect the constraintDeps fields of all Query nodes in a + * query tree into a single list. + */ +static bool +collectConstraintDeps_walker(Node *node, void *context) +{ + if (node == NULL) + return false; + else if (IsA(node, Query)) + { + Query *query = (Query *) node; + List **listp = (List **) context; + + *listp = list_concat_unique_oid(*listp, query->constraintDeps); + + return query_tree_walker(query, collectConstraintDeps_walker, context, 0); + } + else + return expression_tree_walker(node, collectConstraintDeps_walker, context); +} + +/* * DefineView * Execute a CREATE VIEW command. */ @@ -399,6 +423,7 @@ DefineView(ViewStmt *stmt, const char *queryString) Query *viewParse; Oid viewOid; RangeVar *view; + List *allConstraintDeps; /* * Run parse analysis to convert the raw parse tree to a Query. Note this @@ -479,6 +504,30 @@ DefineView(ViewStmt *stmt, const char *queryString) */ CommandCounterIncrement(); + allConstraintDeps = NIL; + collectConstraintDeps_walker((Node *) viewParse, &allConstraintDeps); + + if (list_length(allConstraintDeps) > 0) + { + ObjectAddress myself, referenced; + ListCell *lc; + + myself.classId = RelationRelationId; + myself.objectId = viewOid; + myself.objectSubId = 0; + + foreach(lc, allConstraintDeps) + { + Oid constraint_oid = lfirst_oid(lc); + + referenced.classId = ConstraintRelationId; + referenced.objectId = constraint_oid; + referenced.objectSubId = 0; + + recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); + } + } + /* * The range table of 'viewParse' does not contain entries for the "OLD" * and "NEW" relations. So... add them! diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 9af1217..69262d6 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2272,6 +2272,7 @@ _copyQuery(Query *from) COPY_NODE_FIELD(limitCount); COPY_NODE_FIELD(rowMarks); COPY_NODE_FIELD(setOperations); + COPY_NODE_FIELD(constraintDeps); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 70b3c62..667057b 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -877,6 +877,7 @@ _equalQuery(Query *a, Query *b) COMPARE_NODE_FIELD(limitCount); COMPARE_NODE_FIELD(rowMarks); COMPARE_NODE_FIELD(setOperations); + COMPARE_NODE_FIELD(constraintDeps); return true; } diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 0454aa5..04a6647 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -2020,6 +2020,7 @@ _outQuery(StringInfo str, Query *node) WRITE_NODE_FIELD(limitCount); WRITE_NODE_FIELD(rowMarks); WRITE_NODE_FIELD(setOperations); + WRITE_NODE_FIELD(constraintDeps); } static void diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index bc6e2a6..0a2edcb 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -218,6 +218,7 @@ _readQuery(void) READ_NODE_FIELD(limitCount); READ_NODE_FIELD(rowMarks); READ_NODE_FIELD(setOperations); + READ_NODE_FIELD(constraintDeps); READ_DONE(); } diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 0a69bde..6e60563 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -14,6 +14,11 @@ */ #include "postgres.h" +#include "access/genam.h" +#include "access/heapam.h" +#include "catalog/indexing.h" +#include "catalog/pg_constraint.h" +#include "catalog/pg_index.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "optimizer/tlist.h" @@ -23,21 +28,26 @@ #include "parser/parsetree.h" #include "rewrite/rewriteManip.h" #include "utils/builtins.h" +#include "utils/fmgroids.h" #include "utils/lsyscache.h" +#include "utils/syscache.h" +#include "utils/tqual.h" typedef struct { ParseState *pstate; + Query *qry; List *groupClauses; bool have_non_var_grouping; int sublevels_up; } check_ungrouped_columns_context; -static void check_ungrouped_columns(Node *node, ParseState *pstate, +static void check_ungrouped_columns(Node *node, ParseState *pstate, Query *qry, List *groupClauses, bool have_non_var_grouping); static bool check_ungrouped_columns_walker(Node *node, check_ungrouped_columns_context *context); +static bool funcdeps_check_pk(List *group_clauses, Oid relid, Index rteno, Oid *constraint_oid); /* @@ -408,13 +418,13 @@ parseCheckAggregates(ParseState *pstate, Query *qry) clause = (Node *) qry->targetList; if (hasJoinRTEs) clause = flatten_join_alias_vars(root, clause); - check_ungrouped_columns(clause, pstate, + check_ungrouped_columns(clause, pstate, qry, groupClauses, have_non_var_grouping); clause = (Node *) qry->havingQual; if (hasJoinRTEs) clause = flatten_join_alias_vars(root, clause); - check_ungrouped_columns(clause, pstate, + check_ungrouped_columns(clause, pstate, qry, groupClauses, have_non_var_grouping); /* @@ -535,12 +545,13 @@ parseCheckWindowFuncs(ParseState *pstate, Query *qry) * way more pain than the feature seems worth. */ static void -check_ungrouped_columns(Node *node, ParseState *pstate, +check_ungrouped_columns(Node *node, ParseState *pstate, Query *qry, List *groupClauses, bool have_non_var_grouping) { check_ungrouped_columns_context context; context.pstate = pstate; + context.qry = qry; context.groupClauses = groupClauses; context.have_non_var_grouping = have_non_var_grouping; context.sublevels_up = 0; @@ -617,6 +628,19 @@ check_ungrouped_columns_walker(Node *node, gvar->varlevelsup == 0) return false; /* acceptable, we're okay */ } + + /* Check whether primary key of var's table is subset of group clauses. */ + rte = rt_fetch(var->varno, context->pstate->p_rtable); + if (rte->rtekind == RTE_RELATION) + { + Oid constraint_oid; + + if (funcdeps_check_pk(context->groupClauses, rte->relid, var->varno, &constraint_oid)) + { + context->qry->constraintDeps = list_append_unique_oid(context->qry->constraintDeps, constraint_oid); + return false; + } + } } /* Found an ungrouped local variable; generate error message */ @@ -656,6 +680,134 @@ check_ungrouped_columns_walker(Node *node, } /* + * Search pg_constraint for the constraint associated with the given + * index. To make this not too painfully slow, we use the index on + * conrelid, which holds the parent relation's OID, so pass that in as + * well. + */ +static Oid +get_constraint_oid_for_index(Oid index_oid, Oid rel_oid) +{ + ScanKeyData skey[1]; + Relation conrel; + SysScanDesc conscan; + HeapTuple htup; + Oid result; + + ScanKeyInit(&skey[0], + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(rel_oid)); + + conrel = heap_open(ConstraintRelationId, AccessShareLock); + conscan = systable_beginscan(conrel, ConstraintRelidIndexId, true, + SnapshotNow, 1, skey); + + result = InvalidOid; + + while (HeapTupleIsValid(htup = systable_getnext(conscan))) + { + Form_pg_constraint conform = (Form_pg_constraint) GETSTRUCT(htup); + + if (conform->contype == CONSTRAINT_PRIMARY && + conform->conindid == index_oid) + { + result = HeapTupleGetOid(htup); + break; + } + } + systable_endscan(conscan); + heap_close(conrel, AccessShareLock); + + if (!result) + elog(ERROR, "primary key constraint record missing for relation %u", + rel_oid); + + return result; +} + +/* + * Check whether the attributes of the primary key or a not-null + * unique constraint of relid with range table index rteno appear as a + * subset of the group_clauses. (If so, a functional dependency + * exists between the group clauses and any attribute of the relation, + * and so attributes of the relation can appear ungrouped.) + */ +static bool +funcdeps_check_pk(List *group_clauses, Oid relid, Index rteno, Oid *constraint_oid) +{ + Relation rel; + ListCell *indexoidcell; + + rel = heap_open(relid, AccessShareLock); + + foreach(indexoidcell, RelationGetIndexList(rel)) + { + Oid indexoid = lfirst_oid(indexoidcell); + HeapTuple indexTuple; + Form_pg_index indexStruct; + int i; + bool found_col; + bool found_all_cols; + + indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid)); + if (!HeapTupleIsValid(indexTuple)) + elog(ERROR, "cache lookup failed for index %u", indexoid); + indexStruct = (Form_pg_index) GETSTRUCT(indexTuple); + + /* TODO: also allow indisunique if we have a way to record + * dependencies on not-null constraints */ + if (!indexStruct->indisprimary + || !indexStruct->indimmediate + || !indexStruct->indisvalid + || !indexStruct->indisready) + continue; + + /* + * Check that the group columns are a superset of the + * primary key columns. + */ + for (i = 0; i < indexStruct->indnatts; i++) + { + int2 attnum; + ListCell *gl; + + attnum = indexStruct->indkey.values[i]; + found_col = false; + + foreach(gl, group_clauses) + { + Var *gvar = (Var *) lfirst(gl); + + if (IsA(gvar, Var) && + gvar->varno == rteno && + gvar->varattno == attnum && + gvar->varlevelsup == 0) + { + found_col = true; + break; + } + } + if (!found_col) + break; + } + found_all_cols = (i == indexStruct->indnatts && found_col); + + ReleaseSysCache(indexTuple); + if (found_all_cols) + { + heap_close(rel, NoLock); + *constraint_oid = get_constraint_oid_for_index(indexoid, relid); + return true; + } + } + + heap_close(rel, NoLock); + + return false; +} + +/* * Create expression trees for the transition and final functions * of an aggregate. These are needed so that polymorphic functions * can be used within an aggregate --- without the expression trees, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index fec8d3c..254062b 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -146,6 +146,9 @@ typedef struct Query Node *setOperations; /* set-operation tree if this is top level of * a UNION/INTERSECT/EXCEPT query */ + List *constraintDeps; /* list of constraint OIDs that are + * required for the query to be + * valid */ } Query; diff --git a/src/test/regress/expected/functional_deps.out b/src/test/regress/expected/functional_deps.out new file mode 100644 index 0000000..64f29e3 --- /dev/null +++ b/src/test/regress/expected/functional_deps.out @@ -0,0 +1,228 @@ +-- from http://www.depesz.com/index.php/2010/04/19/getting-unique-elements/ +CREATE TABLE articles ( + id int CONSTRAINT articles_pkey PRIMARY KEY, + keywords text, + title text UNIQUE NOT NULL, + body text UNIQUE, + created date +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "articles_pkey" for table "articles" +NOTICE: CREATE TABLE / UNIQUE will create implicit index "articles_title_key" for table "articles" +NOTICE: CREATE TABLE / UNIQUE will create implicit index "articles_body_key" for table "articles" +CREATE TABLE articles_in_category ( + article_id int, + category_id int, + changed date, + PRIMARY KEY (article_id, category_id) +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "articles_in_category_pkey" for table "articles_in_category" +-- test functional dependencies based on primary keys/unique constraints +-- base tables +-- group by primary key (OK) +SELECT id, keywords, title, body, created +FROM articles +GROUP BY id; + id | keywords | title | body | created +----+----------+-------+------+--------- +(0 rows) + +-- group by unique not null (fail/todo) +SELECT id, keywords, title, body, created +FROM articles +GROUP BY title; +ERROR: column "articles.id" must appear in the GROUP BY clause or be used in an aggregate function +LINE 1: SELECT id, keywords, title, body, created + ^ +-- group by unique nullable (fail) +SELECT id, keywords, title, body, created +FROM articles +GROUP BY body; +ERROR: column "articles.id" must appear in the GROUP BY clause or be used in an aggregate function +LINE 1: SELECT id, keywords, title, body, created + ^ +-- group by something else (fail) +SELECT id, keywords, title, body, created +FROM articles +GROUP BY keywords; +ERROR: column "articles.id" must appear in the GROUP BY clause or be used in an aggregate function +LINE 1: SELECT id, keywords, title, body, created + ^ +-- multiple tables +-- group by primary key (OK) +SELECT a.id, a.keywords, a.title, a.body, a.created +FROM articles AS a, articles_in_category AS aic +WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138) +GROUP BY a.id; + id | keywords | title | body | created +----+----------+-------+------+--------- +(0 rows) + +-- group by something else (fail) +SELECT a.id, a.keywords, a.title, a.body, a.created +FROM articles AS a, articles_in_category AS aic +WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138) +GROUP BY aic.article_id, aic.category_id; +ERROR: column "a.id" must appear in the GROUP BY clause or be used in an aggregate function +LINE 1: SELECT a.id, a.keywords, a.title, a.body, a.created + ^ +-- JOIN syntax +-- group by left table's primary key (OK) +SELECT a.id, a.keywords, a.title, a.body, a.created +FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id +WHERE aic.category_id in (14,62,70,53,138) +GROUP BY a.id; + id | keywords | title | body | created +----+----------+-------+------+--------- +(0 rows) + +-- group by something else (fail) +SELECT a.id, a.keywords, a.title, a.body, a.created +FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id +WHERE aic.category_id in (14,62,70,53,138) +GROUP BY aic.article_id, aic.category_id; +ERROR: column "a.id" must appear in the GROUP BY clause or be used in an aggregate function +LINE 1: SELECT a.id, a.keywords, a.title, a.body, a.created + ^ +-- group by right table's (composite) primary key (OK) +SELECT aic.changed +FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id +WHERE aic.category_id in (14,62,70,53,138) +GROUP BY aic.category_id, aic.article_id; + changed +--------- +(0 rows) + +-- group by right table's partial primary key (fail) +SELECT aic.changed +FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id +WHERE aic.category_id in (14,62,70,53,138) +GROUP BY aic.article_id; +ERROR: column "aic.changed" must appear in the GROUP BY clause or be used in an aggregate function +LINE 1: SELECT aic.changed + ^ +-- example from documentation +CREATE TABLE products (product_id int, name text, price numeric); +CREATE TABLE sales (product_id int, units int); +-- OK +SELECT product_id, p.name, (sum(s.units) * p.price) AS sales + FROM products p LEFT JOIN sales s USING (product_id) + GROUP BY product_id, p.name, p.price; + product_id | name | sales +------------+------+------- +(0 rows) + +-- fail +SELECT product_id, p.name, (sum(s.units) * p.price) AS sales + FROM products p LEFT JOIN sales s USING (product_id) + GROUP BY product_id; +ERROR: column "p.name" must appear in the GROUP BY clause or be used in an aggregate function +LINE 1: SELECT product_id, p.name, (sum(s.units) * p.price) AS sales + ^ +ALTER TABLE products ADD PRIMARY KEY (product_id); +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "products_pkey" for table "products" +-- OK now +SELECT product_id, p.name, (sum(s.units) * p.price) AS sales + FROM products p LEFT JOIN sales s USING (product_id) + GROUP BY product_id; + product_id | name | sales +------------+------+------- +(0 rows) + +-- Drupal example, http://drupal.org/node/555530 +CREATE TABLE node ( + nid SERIAL, + vid integer NOT NULL default '0', + type varchar(32) NOT NULL default '', + title varchar(128) NOT NULL default '', + uid integer NOT NULL default '0', + status integer NOT NULL default '1', + created integer NOT NULL default '0', + -- snip + PRIMARY KEY (nid, vid) +); +NOTICE: CREATE TABLE will create implicit sequence "node_nid_seq" for serial column "node.nid" +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "node_pkey" for table "node" +CREATE TABLE users ( + uid integer NOT NULL default '0', + name varchar(60) NOT NULL default '', + pass varchar(32) NOT NULL default '', + -- snip + PRIMARY KEY (uid), + UNIQUE (name) +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users" +NOTICE: CREATE TABLE / UNIQUE will create implicit index "users_name_key" for table "users" +-- OK +SELECT u.uid, u.name FROM node n +INNER JOIN users u ON u.uid = n.uid +WHERE n.type = 'blog' AND n.status = 1 +GROUP BY u.uid, u.name; + uid | name +-----+------ +(0 rows) + +-- OK +SELECT u.uid, u.name FROM node n +INNER JOIN users u ON u.uid = n.uid +WHERE n.type = 'blog' AND n.status = 1 +GROUP BY u.uid; + uid | name +-----+------ +(0 rows) + +-- views and dependencies +-- fail +CREATE VIEW fdv1 AS +SELECT id, keywords, title, body, created +FROM articles +GROUP BY body; +ERROR: column "articles.id" must appear in the GROUP BY clause or be used in an aggregate function +LINE 2: SELECT id, keywords, title, body, created + ^ +-- OK +CREATE VIEW fdv1 AS +SELECT id, keywords, title, body, created +FROM articles +GROUP BY id; +-- fail +ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; +ERROR: cannot drop constraint articles_pkey on table articles because other objects depend on it +DETAIL: view fdv1 depends on constraint articles_pkey on table articles +HINT: Use DROP ... CASCADE to drop the dependent objects too. +DROP VIEW fdv1; +-- multiple dependencies +CREATE VIEW fdv2 AS +SELECT a.id, a.keywords, a.title, aic.category_id, aic.changed +FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id +WHERE aic.category_id in (14,62,70,53,138) +GROUP BY a.id, aic.category_id, aic.article_id; +ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail +ERROR: cannot drop constraint articles_pkey on table articles because other objects depend on it +DETAIL: view fdv2 depends on constraint articles_pkey on table articles +HINT: Use DROP ... CASCADE to drop the dependent objects too. +ALTER TABLE articles_in_category DROP CONSTRAINT articles_in_category_pkey RESTRICT; --fail +ERROR: cannot drop constraint articles_in_category_pkey on table articles_in_category because other objects depend on it +DETAIL: view fdv2 depends on constraint articles_in_category_pkey on table articles_in_category +HINT: Use DROP ... CASCADE to drop the dependent objects too. +DROP VIEW fdv2; +-- nested queries +CREATE VIEW fdv3 AS +SELECT id, keywords, title, body, created +FROM articles +GROUP BY id +UNION +SELECT id, keywords, title, body, created +FROM articles +GROUP BY id; +ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail +ERROR: cannot drop constraint articles_pkey on table articles because other objects depend on it +DETAIL: view fdv3 depends on constraint articles_pkey on table articles +HINT: Use DROP ... CASCADE to drop the dependent objects too. +DROP VIEW fdv3; +CREATE VIEW fdv4 AS +SELECT * FROM articles WHERE title IN (SELECT title FROM articles GROUP BY id); +ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail +ERROR: cannot drop constraint articles_pkey on table articles because other objects depend on it +DETAIL: view fdv4 depends on constraint articles_pkey on table articles +HINT: Use DROP ... CASCADE to drop the dependent objects too. +DROP VIEW fdv4; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 7529777..191d1fe 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -84,7 +84,7 @@ test: rules # ---------- # Another group of parallel tests # ---------- -test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap +test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 5f185f9..e38d5f0 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -76,6 +76,7 @@ test: union test: case test: join test: aggregates +test: functional_deps test: transactions ignore: random test: random diff --git a/src/test/regress/sql/functional_deps.sql b/src/test/regress/sql/functional_deps.sql new file mode 100644 index 0000000..b090a37 --- /dev/null +++ b/src/test/regress/sql/functional_deps.sql @@ -0,0 +1,196 @@ +-- from http://www.depesz.com/index.php/2010/04/19/getting-unique-elements/ + +CREATE TABLE articles ( + id int CONSTRAINT articles_pkey PRIMARY KEY, + keywords text, + title text UNIQUE NOT NULL, + body text UNIQUE, + created date +); + +CREATE TABLE articles_in_category ( + article_id int, + category_id int, + changed date, + PRIMARY KEY (article_id, category_id) +); + +-- test functional dependencies based on primary keys/unique constraints + +-- base tables + +-- group by primary key (OK) +SELECT id, keywords, title, body, created +FROM articles +GROUP BY id; + +-- group by unique not null (fail/todo) +SELECT id, keywords, title, body, created +FROM articles +GROUP BY title; + +-- group by unique nullable (fail) +SELECT id, keywords, title, body, created +FROM articles +GROUP BY body; + +-- group by something else (fail) +SELECT id, keywords, title, body, created +FROM articles +GROUP BY keywords; + +-- multiple tables + +-- group by primary key (OK) +SELECT a.id, a.keywords, a.title, a.body, a.created +FROM articles AS a, articles_in_category AS aic +WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138) +GROUP BY a.id; + +-- group by something else (fail) +SELECT a.id, a.keywords, a.title, a.body, a.created +FROM articles AS a, articles_in_category AS aic +WHERE a.id = aic.article_id AND aic.category_id in (14,62,70,53,138) +GROUP BY aic.article_id, aic.category_id; + +-- JOIN syntax + +-- group by left table's primary key (OK) +SELECT a.id, a.keywords, a.title, a.body, a.created +FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id +WHERE aic.category_id in (14,62,70,53,138) +GROUP BY a.id; + +-- group by something else (fail) +SELECT a.id, a.keywords, a.title, a.body, a.created +FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id +WHERE aic.category_id in (14,62,70,53,138) +GROUP BY aic.article_id, aic.category_id; + +-- group by right table's (composite) primary key (OK) +SELECT aic.changed +FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id +WHERE aic.category_id in (14,62,70,53,138) +GROUP BY aic.category_id, aic.article_id; + +-- group by right table's partial primary key (fail) +SELECT aic.changed +FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id +WHERE aic.category_id in (14,62,70,53,138) +GROUP BY aic.article_id; + + +-- example from documentation + +CREATE TABLE products (product_id int, name text, price numeric); +CREATE TABLE sales (product_id int, units int); + +-- OK +SELECT product_id, p.name, (sum(s.units) * p.price) AS sales + FROM products p LEFT JOIN sales s USING (product_id) + GROUP BY product_id, p.name, p.price; + +-- fail +SELECT product_id, p.name, (sum(s.units) * p.price) AS sales + FROM products p LEFT JOIN sales s USING (product_id) + GROUP BY product_id; + +ALTER TABLE products ADD PRIMARY KEY (product_id); + +-- OK now +SELECT product_id, p.name, (sum(s.units) * p.price) AS sales + FROM products p LEFT JOIN sales s USING (product_id) + GROUP BY product_id; + + +-- Drupal example, http://drupal.org/node/555530 + +CREATE TABLE node ( + nid SERIAL, + vid integer NOT NULL default '0', + type varchar(32) NOT NULL default '', + title varchar(128) NOT NULL default '', + uid integer NOT NULL default '0', + status integer NOT NULL default '1', + created integer NOT NULL default '0', + -- snip + PRIMARY KEY (nid, vid) +); + +CREATE TABLE users ( + uid integer NOT NULL default '0', + name varchar(60) NOT NULL default '', + pass varchar(32) NOT NULL default '', + -- snip + PRIMARY KEY (uid), + UNIQUE (name) +); + +-- OK +SELECT u.uid, u.name FROM node n +INNER JOIN users u ON u.uid = n.uid +WHERE n.type = 'blog' AND n.status = 1 +GROUP BY u.uid, u.name; + +-- OK +SELECT u.uid, u.name FROM node n +INNER JOIN users u ON u.uid = n.uid +WHERE n.type = 'blog' AND n.status = 1 +GROUP BY u.uid; + + +-- views and dependencies + +-- fail +CREATE VIEW fdv1 AS +SELECT id, keywords, title, body, created +FROM articles +GROUP BY body; + +-- OK +CREATE VIEW fdv1 AS +SELECT id, keywords, title, body, created +FROM articles +GROUP BY id; + +-- fail +ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; + +DROP VIEW fdv1; + + +-- multiple dependencies +CREATE VIEW fdv2 AS +SELECT a.id, a.keywords, a.title, aic.category_id, aic.changed +FROM articles AS a JOIN articles_in_category AS aic ON a.id = aic.article_id +WHERE aic.category_id in (14,62,70,53,138) +GROUP BY a.id, aic.category_id, aic.article_id; + +ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail +ALTER TABLE articles_in_category DROP CONSTRAINT articles_in_category_pkey RESTRICT; --fail + +DROP VIEW fdv2; + + +-- nested queries + +CREATE VIEW fdv3 AS +SELECT id, keywords, title, body, created +FROM articles +GROUP BY id +UNION +SELECT id, keywords, title, body, created +FROM articles +GROUP BY id; + +ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail + +DROP VIEW fdv3; + + +CREATE VIEW fdv4 AS +SELECT * FROM articles WHERE title IN (SELECT title FROM articles GROUP BY id); + +ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT; -- fail + +DROP VIEW fdv4;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers