On mån, 2010-07-26 at 10:46 -0600, Alex Hunsaker wrote:
> On Sat, Jul 24, 2010 at 06:23, Peter Eisentraut <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
