|The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 478 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
7.1 The Query Tree
To understand how the rule system works it is necessary to know when it is invoked and what its input and results are.
The rule system is located between the parser and the planner. It takes the output of the parser, one query tree, and the user-defined rewrite rules, which are also query trees with some extra information, and creates zero or more query trees as result. So its input and output are always things the parser itself could have produced and thus, anything it sees is basically representable as an SQL statement.
Now what is a query tree? It is an internal representation of an
SQL statement where the single parts that it is
built from are stored separately. These query trees can be shown
in the server log if you set the configuration parameters
debug_print_plan. The rule actions are also
stored as query trees, in the system catalog
pg_rewrite. They are not formatted like
the log output, but they contain exactly the same information.
Reading a raw query tree requires some experience. But since SQL representations of query trees are sufficient to understand the rule system, this chapter will not teach how to read them.
When reading the SQL representations of the query trees in this chapter it is necessary to be able to identify the parts the statement is broken into when it is in the query tree structure. The parts of a query tree are
- the command type
This is a simple value telling which command
DELETE) produced the query tree.
- the range table
The range table is a list of relations that are used in the query.
SELECTstatement these are the relations given after the
FROMkey word. Every range table entry identifies a table or view and tells by which name it is called in the other parts of the query. In the query tree, the range table entries are referenced by number rather than by name, so here it doesn't matter if there are duplicate names as it would in an SQL statement. This can happen after the range tables of rules have been merged in. The examples in this chapter will not have this situation.
- the result relation
This is an index into the range table that identifies the
relation where the results of the query go.
SELECTqueries normally don't have a result relation. The special case of a
SELECT INTOis mostly identical to a
CREATE TABLEfollowed by a
INSERT ... SELECTand is not discussed separately here. For
DELETEcommands, the result relation is the table (or view!) where the changes are to take effect.
- the target list
The target list is a list of expressions that define the
result of the query. In the case of a
SELECT, these expressions are the ones that build the final output of the query. They correspond to the expressions between the key words
*is just an abbreviation for all the column names of a relation. It is expanded by the parser into the individual columns, so the rule system never sees it.)
DELETEcommands don't need a target list because they don't produce any result. In fact, the planner will add a special CTID entry to the empty target list, but this is after the rule system and will be discussed later; for the rule system, the target list is empty. For
INSERTcommands, the target list describes the new rows that should go into the result relation. It consists of the expressions in the
VALUESclause or the ones from the
INSERT ... SELECT. The first step of the rewrite process adds target list entries for any columns that were not assigned to by the original command but have defaults. Any remaining columns (with neither a given value nor a default) will be filled in by the planner with a constant null expression. For
UPDATEcommands, the target list describes the new rows that should replace the old ones. In the rule system, it contains just the expressions from the
SET column = expressionpart of the command. The planner will handle missing columns by inserting expressions that copy the values from the old row into the new one. And it will add the special CTID entry just as for
DELETE, too. Every entry in the target list contains an expression that can be a constant value, a variable pointing to a column of one of the relations in the range table, a parameter, or an expression tree made of function calls, constants, variables, operators, etc.
- the qualification
The query's qualification is an expression much like one of
those contained in the target list entries. The result value of
this expression is a Boolean that tells whether the operation
SELECT) for the final result row should be executed or not. It corresponds to the
WHEREclause of an SQL statement.
- the join tree
The query's join tree shows the structure of the
FROMclause. For a simple query like
SELECT ... FROM a, b, c, the join tree is just a list of the
FROMitems, because we are allowed to join them in any order. But when
JOINexpressions, particularly outer joins, are used, we have to join in the order shown by the joins. In that case, the join tree shows the structure of the
JOINexpressions. The restrictions associated with particular
USINGexpressions) are stored as qualification expressions attached to those join-tree nodes. It turns out to be convenient to store the top-level
WHEREexpression as a qualification attached to the top-level join-tree item, too. So really the join tree represents both the
WHEREclauses of a
- the others
The other parts of the query tree like the
ORDER BYclause aren't of interest here. The rule system substitutes some entries there while applying rules, but that doesn't have much to do with the fundamentals of the rule system.
|ISBN 9781906966065||The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide||See the print edition|