arch-dev.sgml

关系型数据库 Postgresql 6.5.2

         from sells 
         where sno > 2
         group by sno
         having count(pno) > 1)
  group by pno_count;
\end{verbatim}
%
(which is part of the SQL92 standard) will automatically also solve
these problems. \\
\\
In the next part of the current section we will present the changes
applied to the source code in order to realize the above described
items. Note that it is not necessary to understand the meaning of
every single source line here and therefore we will not discuss
detailed questions like "Why has the variable {\tt varno} to be
increased by 3?". Questions like that belong to a chapter dealing
with the implementation of {\it views} in <productname>Postgres</productname> and to be able to
answer them it would be necessary to know all the functions and not
only those described here. The fact important for us is to make sure,
that whatever is applied to the {\it targetlist} and the data
structures representing the {\it where clause} is also applied to the
data structures for the {\it having clause}. There are three files
affected: \\
\\
\indent {\tt $\ldots$/src/backend/rewrite/rewriteHandler.c} \\
\indent {\tt $\ldots$/src/backend/rewrite/rewriteManip.c} \\
\indent {\tt $\ldots$/src/backend/commands/view.c} \\
\\
Here is a description of the changes made to the functions contained
in the file {\tt $\ldots$/src/backend/rewrite/rewriteHandler.c}:
%
\pagebreak
%
\begin{itemize}
<step> {\tt ApplyRetrieveRule()} \\
This function becomes invoked whenever a {\tt select} statement
against a {\it view} is recognized and applies the {\it rewrite rule}
stored in the {\it system catalogs}. The additional source lines given
in the listing below make sure that the functions {\tt
OffsetVarNodes()} and {\tt ChangeVarNodes()} that are invoked for the
{\it where clause} and the {\it targetlist} of the query given in the
{\it view definition} are also called for the {\it having clause} and
the {\it group clause} of the query in the {\it view
definition}. These functions adapt the {\tt varno} and {\tt varattno}
fields of the {\tt VAR} nodes involved.

The additional source lines at the end of {\tt ApplyRetrieveRule()}
attach the data structures representing the {\it having clause} and
the {\it group clause} of the query in the {\it view definition} to
the rewritten {\it parsetree}. As mentioned earlier, a {\it view
definition} involving a {\it group clause} will cause troubles
whenever a query using a different {\it group clause} against this
{\it view} is executed. There is no mechanism preventing these
troubles included at the moment.

Note that the functions {\tt OffsetVarNodes()} , {\tt ChangeVarNodes()}
and {\tt AddHavingQual()} appearing in {\tt ApplyRetrieveRule()} are
described at a later point in time.
%
\begin{verbatim}
  static void
  ApplyRetrieveRule(Query *parsetree, RewriteRule *rule,
                    int rt_index, int relation_level,
                    Relation relation, int *modified)
  {
    Query  *rule_action = NULL;
    Node   *rule_qual;
    List   *rtable,
                            .
                            .
                            .
    OffsetVarNodes((Node *) rule_action->targetList, 
                   rt_length);
    OffsetVarNodes(rule_qual, rt_length);
        
+   OffsetVarNodes((Node *) rule_action->groupClause, 
+                  rt_length);
+   OffsetVarNodes((Node *) rule_action->havingQual, 
+                  rt_length);
                            .
                            .
                            .
    ChangeVarNodes(rule_qual, 
                   PRS2_CURRENT_VARNO + rt_length, 
                   rt_index, 0);

+   ChangeVarNodes((Node *) rule_action->groupClause,
+                  PRS2_CURRENT_VARNO + rt_length, 
+                  rt_index, 0);
+   ChangeVarNodes((Node *) rule_action->havingQual,
+                  PRS2_CURRENT_VARNO + rt_length, 
+                  rt_index, 0);
                            .
                            .
                            .
\end{verbatim}
\pagebreak
\begin{verbatim}
    if (*modified && !badsql) 
    {
      AddQual(parsetree, rule_action->qual);
+     /* This will only work if the query made to the 
+      * view defined by the following groupClause 
+      * groups by the same attributes or does not use 
+      * groups at all! 
+      */
+      if (parsetree->groupClause == NULL)
+         parsetree->groupClause = 
+                    rule_action->groupClause;
+      AddHavingQual(parsetree, 
+                    rule_action->havingQual);
+      parsetree->hasAggs = 
+         (rule_action->hasAggs || parsetree->hasAggs);
+      parsetree->hasSubLinks = 
+         (rule_action->hasSubLinks || 
+          parsetree->hasSubLinks);
    }
  }
\end{verbatim}
%
<step> {\tt QueryRewriteSubLink()} \\
This function is called by {\tt QueryRewrite()} to process possibly
contained subqueries first. It searches for nested queries by
recursively tracing through the {\it parsetree} given as argument. The
additional statement makes sure that the {\it having clause} is also
examined.
%
\begin{verbatim}
  static void
  QueryRewriteSubLink(Node *node)
  {
    if (node == NULL)
       return;
    switch (nodeTag(node))
    {
      case T_SubLink:
      {
                            .
                            .
                            .
         QueryRewriteSubLink((Node *) query->qual);
+        QueryRewriteSubLink((Node *) 
+                            query->havingQual);
                            .
                            .
                            .
      }
                            .
                            .
                            .
    }
    return;
  }
\end{verbatim}
%
\pagebreak
%
<step> {\tt QueryRewrite()} \\
This function takes the {\it parsetree} of a query and rewrites it
using <productname>Postgres</productname>'s {\it rewrite system}. Before the query itself can be
rewritten, subqueries that are possibly part of the query have to be
processed. Therefore the function {\tt  QueryRewriteSubLink()} is
called for the {\it where clause} and for the {\it having clause}.
%
\begin{verbatim}
  List *
  QueryRewrite(Query *parsetree)
  {
    QueryRewriteSubLink(parsetree->qual);       
+   QueryRewriteSubLink(parsetree->havingQual);
    return QueryRewriteOne(parsetree);
  } 
\end{verbatim}
%
\end{itemize}
%
Here we present the changes applied to the functions that are contained
in the file {\tt $\ldots$/src/backend/rewrite/rewriteManip.c}:
%
\begin{itemize}
%
<step> {\tt OffsetVarNodes()} \\
Recursively steps through the {\it parsetree} given as the first
argument and increments the {\tt varno} and {\tt varnoold} fields of
every {\tt VAR} node found by the {\it offset} given as the second
argument. The additional statements are necessary to be able to handle
{\tt GroupClause} nodes and {\tt Sublink} nodes that may appear in the {\it
parsetree} from now on.
%
\begin{verbatim}
  void
  OffsetVarNodes(Node *node, int offset)
  {
     if (node == NULL)
        return;
     switch (nodeTag(node))
     {
                            .
                            .
                            .
+       /* This has to be done to make queries using 
+        * groupclauses work on views 
+        */
+        case T_GroupClause:
+        {
+          GroupClause *group = (GroupClause *) node;
+                         
+          OffsetVarNodes((Node *)(group->entry), 
+                         offset);
+        }
+        break;
                            .
                            .
                            .
+        case T_SubLink:
+        {
+          SubLink *sublink = (SubLink *) node;
+          List *tmp_oper, *tmp_lefthand;                               
+
\end{verbatim}
\pagebreak
\begin{verbatim}
+          /* We also have to adapt the variables used 
+           * in sublink->lefthand and sublink->oper 
+           */
+          OffsetVarNodes((Node *)(sublink->lefthand), 
+                         offset);
+
+          /* Make sure the first argument of 
+           * sublink->oper points to the same var as 
+           * sublink->lefthand does otherwise we will 
+           * run into troubles using aggregates (aggno 
+           * will not be set correctly) 
+           */
+          tmp_lefthand = sublink->lefthand;                            
+          foreach(tmp_oper, sublink->oper)
+          {                                
+            lfirst(((Expr *)lfirst(tmp_oper))->args) = 
+                                  lfirst(tmp_lefthand);
+            tmp_lefthand = lnext(tmp_lefthand);
+          }                                                            
+        }
+        break;
                            .
                            .
                            .
     }
  }
\end{verbatim}
%
<step> {\tt ChangeVarNodes()} \\
This function is similar to the above described function {\tt
OffsetVarNodes()} but instead of incrementing the fields {\tt varno}
and {\tt varnoold} of {\it all} {\tt VAR} nodes found, it processes
only those {\tt VAR} nodes whose {\tt varno} value matches the
parameter {\tt old\_varno} given as argument and whose {\tt
varlevelsup} value matches the parameter {\tt sublevels\_up}. Whenever
such a node is found, the {\tt varno} and {\tt varnoold} fields are
set to the value given in the parameter {\tt new\_varno}. The
additional statements are necessary to be able to handle {\tt GroupClause}
and {\tt Sublink} nodes.
%
\begin{verbatim}
  void
  ChangeVarNodes(Node *node, int old_varno, 
                 int new_varno, int sublevels_up)
  {
    if (node == NULL)
       return;
    switch (nodeTag(node))
    {
                            .
                            .
                            .
+     /* This has to be done to make queries using 
+      * groupclauses work on views */
+     case T_GroupClause:
+     {
+       GroupClause  *group = (GroupClause *) node;
+                       
\end{verbatim}
\pagebreak
\begin{verbatim}  
+       ChangeVarNodes((Node *)(group->entry),
+                      old_varno, new_varno, 
+                      sublevels_up);
+     }
+     break;
                            .
                            .
                            .
      case T_Var:
      {
                            .
                            .
                            .
        /* This is a hack: Whenever an attribute
         * from the "outside" query is used within
         * a nested subquery, the varlevelsup will 
         * be >0. Nodes having varlevelsup > 0 are  
         * forgotten to be processed. The call to 
         * OffsetVarNodes() should really be done at 
         * another place but this hack makes sure 
         * that also those VAR nodes are processed.
         */
+       if (var->varlevelsup > 0) 
+          OffsetVarNodes((Node *)var,3);
      }
      break;
                            .
                            .
                            .
      case T_SubLink:
      {
                            .
                            .
                            .
+       ChangeVarNodes((Node *) query->havingQual, 
+                      old_varno, new_varno,
+                      sublevels_up);
+       ChangeVarNodes((Node *) query->targetList, 
+                      old_varno, new_varno,
+                      sublevels_up);
+
+       /* We also have to adapt the variables used in 
+        * sublink->lefthand and sublink->oper 
+        */
+       ChangeVarNodes((Node *) (sublink->lefthand), 
+                      old_varno, new_varno,
+                      sublevels_up);
      }
      break;
                            .
                            .
                            .
    }
  }
\end{verbatim}
%
<step> {\tt AddHavingQual()} \\
This function adds the {\it operator tree} given by the parameter {\tt
havingQual} to the one attached to the field {\tt havingQual} of the
{\it parsetree} given by the parame