Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite49ae8d

Browse files
committed
Recognize functional dependency on primary keys. This allows a table's
other columns to be referenced without listing them in GROUP BY, so long asthe primary key column(s) are listed in GROUP BY.Eventually we should also allow functional dependency on a UNIQUE constraintwhen the columns are marked NOT NULL, but that has to wait until NOT NULLconstraints are represented in pg_constraint, because we need to havepg_constraint OIDs for all the conditions needed to ensure functionaldependency.Peter Eisentraut, reviewed by Alex Hunsaker and Tom Lane
1 parentce6ce1a commite49ae8d

File tree

16 files changed

+684
-35
lines changed

16 files changed

+684
-35
lines changed

‎doc/src/sgml/queries.sgml

Lines changed: 14 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.58 2010/07/03 02:57:46 rhaas Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.59 2010/08/07 02:44:05 tgl Exp $ -->
22

33
<chapter id="queries">
44
<title>Queries</title>
@@ -886,17 +886,26 @@ SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
886886
In this example, the columns <literal>product_id</literal>,
887887
<literal>p.name</literal>, and <literal>p.price</literal> must be
888888
in the <literal>GROUP BY</> clause since they are referenced in
889-
the query select list. (Depending on how the products
890-
table is set up, name and price might be fully dependent on the
891-
product ID, so the additional groupings could theoretically be
892-
unnecessary, though this is not implemented.) The column
889+
the query select list (but see below). The column
893890
<literal>s.units</> does not have to be in the <literal>GROUP
894891
BY</> list since it is only used in an aggregate expression
895892
(<literal>sum(...)</literal>), which represents the sales
896893
of a product. For each product, the query returns a summary row about
897894
all sales of the product.
898895
</para>
899896

897+
<para>
898+
If the products table is set up so that,
899+
say, <literal>product_id</literal> is the primary key, then it
900+
would be enough to group by <literal>product_id</literal> in the
901+
above example, since name and price would
902+
be <firstterm>functionally
903+
dependent</firstterm><indexterm><primary>functional
904+
dependency</primary></indexterm> on the product ID, and so there
905+
would be no ambiguity about which name and price value to return
906+
for each product ID group.
907+
</para>
908+
900909
<para>
901910
In strict SQL, <literal>GROUP BY</> can only group by columns of
902911
the source table but <productname>PostgreSQL</productname> extends

‎doc/src/sgml/ref/select.sgml

Lines changed: 19 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.131 2010/06/15 20:04:53 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.132 2010/08/07 02:44:05 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -520,9 +520,12 @@ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...]
520520
produces a single value computed across all the selected rows).
521521
When <literal>GROUP BY</literal> is present, it is not valid for
522522
the <command>SELECT</command> list expressions to refer to
523-
ungrouped columns except within aggregate functions, since there
524-
would be more than one possible value to return for an ungrouped
525-
column.
523+
ungrouped columns except within aggregate functions or if the
524+
ungrouped column is functionally dependent on the grouped columns,
525+
since there would otherwise be more than one possible value to
526+
return for an ungrouped column. A functional dependency exists if
527+
the grouped columns (or a subset thereof) are the primary key of
528+
the table containing the ungrouped column.
526529
</para>
527530
</refsect2>
528531

@@ -1590,6 +1593,18 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
15901593
</para>
15911594
</refsect2>
15921595

1596+
<refsect2>
1597+
<title>Functional Dependencies</title>
1598+
1599+
<para>
1600+
<productname>PostgreSQL</productname> recognizes functional dependency
1601+
(allowing columns to be omitted from <literal>GROUP BY</>) only when
1602+
a table's primary key is included in the <literal>GROUP BY</> list.
1603+
The SQL standard specifies additional conditions that should be
1604+
recognized.
1605+
</para>
1606+
</refsect2>
1607+
15931608
<refsect2>
15941609
<title><literal>WINDOW</literal> Clause Restrictions</title>
15951610

‎src/backend/catalog/dependency.c

Lines changed: 13 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/dependency.c,v 1.96 2010/02/26 02:00:36 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/dependency.c,v 1.97 2010/08/07 02:44:06 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1594,7 +1594,7 @@ find_expr_references_walker(Node *node,
15941594
{
15951595
/* Recurse into RTE subquery or not-yet-planned sublink subquery */
15961596
Query*query= (Query*)node;
1597-
ListCell*rtable;
1597+
ListCell*lc;
15981598
boolresult;
15991599

16001600
/*
@@ -1604,9 +1604,9 @@ find_expr_references_walker(Node *node,
16041604
* of recursing into RTE_FUNCTION RTEs, subqueries, etc, so no need to
16051605
* do that here. But keep it from looking at join alias lists.)
16061606
*/
1607-
foreach(rtable,query->rtable)
1607+
foreach(lc,query->rtable)
16081608
{
1609-
RangeTblEntry*rte= (RangeTblEntry*)lfirst(rtable);
1609+
RangeTblEntry*rte= (RangeTblEntry*)lfirst(lc);
16101610
ListCell*ct;
16111611

16121612
switch (rte->rtekind)
@@ -1627,6 +1627,15 @@ find_expr_references_walker(Node *node,
16271627
}
16281628
}
16291629

1630+
/*
1631+
* Add dependencies on constraints listed in query's constraintDeps
1632+
*/
1633+
foreach(lc,query->constraintDeps)
1634+
{
1635+
add_object_address(OCLASS_CONSTRAINT,lfirst_oid(lc),0,
1636+
context->addrs);
1637+
}
1638+
16301639
/* query_tree_walker ignores ORDER BY etc, but we need those opers */
16311640
find_expr_references_walker((Node*)query->sortClause,context);
16321641
find_expr_references_walker((Node*)query->groupClause,context);

‎src/backend/catalog/pg_constraint.c

Lines changed: 110 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_constraint.c,v 1.54 2010/08/05 15:25:35 rhaas Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_constraint.c,v 1.55 2010/08/07 02:44:06 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -783,3 +783,112 @@ get_constraint_oid(Oid relid, const char *conname, bool missing_ok)
783783

784784
returnconOid;
785785
}
786+
787+
/*
788+
* Determine whether a relation can be proven functionally dependent on
789+
* a set of grouping columns. If so, return TRUE and add the pg_constraint
790+
* OIDs of the constraints needed for the proof to the *constraintDeps list.
791+
*
792+
* grouping_columns is a list of grouping expressions, in which columns of
793+
* the rel of interest are Vars with the indicated varno/varlevelsup.
794+
*
795+
* Currently we only check to see if the rel has a primary key that is a
796+
* subset of the grouping_columns. We could also use plain unique constraints
797+
* if all their columns are known not null, but there's a problem: we need
798+
* to be able to represent the not-null-ness as part of the constraints added
799+
* to *constraintDeps. FIXME whenever not-null constraints get represented
800+
* in pg_constraint.
801+
*/
802+
bool
803+
check_functional_grouping(Oidrelid,
804+
Indexvarno,Indexvarlevelsup,
805+
List*grouping_columns,
806+
List**constraintDeps)
807+
{
808+
boolresult= false;
809+
Relationpg_constraint;
810+
HeapTupletuple;
811+
SysScanDescscan;
812+
ScanKeyDataskey[1];
813+
814+
/* Scan pg_constraint for constraints of the target rel */
815+
pg_constraint=heap_open(ConstraintRelationId,AccessShareLock);
816+
817+
ScanKeyInit(&skey[0],
818+
Anum_pg_constraint_conrelid,
819+
BTEqualStrategyNumber,F_OIDEQ,
820+
ObjectIdGetDatum(relid));
821+
822+
scan=systable_beginscan(pg_constraint,ConstraintRelidIndexId, true,
823+
SnapshotNow,1,skey);
824+
825+
while (HeapTupleIsValid(tuple=systable_getnext(scan)))
826+
{
827+
Form_pg_constraintcon= (Form_pg_constraint)GETSTRUCT(tuple);
828+
Datumadatum;
829+
boolisNull;
830+
ArrayType*arr;
831+
int16*attnums;
832+
intnumkeys;
833+
inti;
834+
boolfound_col;
835+
836+
/* Only PK constraints are of interest for now, see comment above */
837+
if (con->contype!=CONSTRAINT_PRIMARY)
838+
continue;
839+
840+
/* Extract the conkey array, ie, attnums of PK's columns */
841+
adatum=heap_getattr(tuple,Anum_pg_constraint_conkey,
842+
RelationGetDescr(pg_constraint),&isNull);
843+
if (isNull)
844+
elog(ERROR,"null conkey for constraint %u",
845+
HeapTupleGetOid(tuple));
846+
arr=DatumGetArrayTypeP(adatum);/* ensure not toasted */
847+
numkeys=ARR_DIMS(arr)[0];
848+
if (ARR_NDIM(arr)!=1||
849+
numkeys<0||
850+
ARR_HASNULL(arr)||
851+
ARR_ELEMTYPE(arr)!=INT2OID)
852+
elog(ERROR,"conkey is not a 1-D smallint array");
853+
attnums= (int16*)ARR_DATA_PTR(arr);
854+
855+
found_col= false;
856+
for (i=0;i<numkeys;i++)
857+
{
858+
AttrNumberattnum=attnums[i];
859+
ListCell*gl;
860+
861+
found_col= false;
862+
foreach(gl,grouping_columns)
863+
{
864+
Var*gvar= (Var*)lfirst(gl);
865+
866+
if (IsA(gvar,Var)&&
867+
gvar->varno==varno&&
868+
gvar->varlevelsup==varlevelsup&&
869+
gvar->varattno==attnum)
870+
{
871+
found_col= true;
872+
break;
873+
}
874+
}
875+
if (!found_col)
876+
break;
877+
}
878+
879+
if (found_col)
880+
{
881+
/* The PK is a subset of grouping_columns, so we win */
882+
*constraintDeps=lappend_oid(*constraintDeps,
883+
HeapTupleGetOid(tuple));
884+
result= true;
885+
break;
886+
}
887+
}
888+
889+
systable_endscan(scan);
890+
891+
heap_close(pg_constraint,AccessShareLock);
892+
893+
returnresult;
894+
}

‎src/backend/nodes/copyfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.466 2010/07/25 23:21:21 rhaas Exp $
18+
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.467 2010/08/07 02:44:06 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -2272,6 +2272,7 @@ _copyQuery(Query *from)
22722272
COPY_NODE_FIELD(limitCount);
22732273
COPY_NODE_FIELD(rowMarks);
22742274
COPY_NODE_FIELD(setOperations);
2275+
COPY_NODE_FIELD(constraintDeps);
22752276

22762277
returnnewnode;
22772278
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@
2222
* Portions Copyright (c) 1994, Regents of the University of California
2323
*
2424
* IDENTIFICATION
25-
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.386 2010/07/25 23:21:21 rhaas Exp $
25+
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.387 2010/08/07 02:44:06 tgl Exp $
2626
*
2727
*-------------------------------------------------------------------------
2828
*/
@@ -877,6 +877,7 @@ _equalQuery(Query *a, Query *b)
877877
COMPARE_NODE_FIELD(limitCount);
878878
COMPARE_NODE_FIELD(rowMarks);
879879
COMPARE_NODE_FIELD(setOperations);
880+
COMPARE_NODE_FIELD(constraintDeps);
880881

881882
return true;
882883
}

‎src/backend/nodes/outfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.387 2010/07/25 23:21:21 rhaas Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.388 2010/08/07 02:44:06 tgl Exp $
1212
*
1313
* NOTES
1414
* Every node type that can appear in stored rules' parsetrees *must*
@@ -2020,6 +2020,7 @@ _outQuery(StringInfo str, Query *node)
20202020
WRITE_NODE_FIELD(limitCount);
20212021
WRITE_NODE_FIELD(rowMarks);
20222022
WRITE_NODE_FIELD(setOperations);
2023+
WRITE_NODE_FIELD(constraintDeps);
20232024
}
20242025

20252026
staticvoid

‎src/backend/nodes/readfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.232 2010/02/16 22:34:43 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.233 2010/08/07 02:44:07 tgl Exp $
1212
*
1313
* NOTES
1414
* Path and Plan nodes do not have any readfuncs support, because we
@@ -218,6 +218,7 @@ _readQuery(void)
218218
READ_NODE_FIELD(limitCount);
219219
READ_NODE_FIELD(rowMarks);
220220
READ_NODE_FIELD(setOperations);
221+
READ_NODE_FIELD(constraintDeps);
221222

222223
READ_DONE();
223224
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp