| Server IP : 103.119.228.120 / Your IP : 18.227.72.24 Web Server : Apache System : Linux v8.techscape8.com 3.10.0-1160.119.1.el7.tuxcare.els2.x86_64 #1 SMP Mon Jul 15 12:09:18 UTC 2024 x86_64 User : nobody ( 99) PHP Version : 5.6.40 Disable Function : shell_exec,symlink,system,exec,proc_get_status,proc_nice,proc_terminate,define_syslog_variables,syslog,openlog,closelog,escapeshellcmd,passthru,ocinum cols,ini_alter,leak,listen,chgrp,apache_note,apache_setenv,debugger_on,debugger_off,ftp_exec,dl,dll,myshellexec,proc_open,socket_bind,proc_close,escapeshellarg,parse_ini_filepopen,fpassthru,exec,passthru,escapeshellarg,escapeshellcmd,proc_close,proc_open,ini_alter,popen,show_source,proc_nice,proc_terminate,proc_get_status,proc_close,pfsockopen,leak,apache_child_terminate,posix_kill,posix_mkfifo,posix_setpgid,posix_setsid,posix_setuid,dl,symlink,shell_exec,system,dl,passthru,escapeshellarg,escapeshellcmd,myshellexec,c99_buff_prepare,c99_sess_put,fpassthru,getdisfunc,fx29exec,fx29exec2,is_windows,disp_freespace,fx29sh_getupdate,fx29_buff_prepare,fx29_sess_put,fx29shexit,fx29fsearch,fx29ftpbrutecheck,fx29sh_tools,fx29sh_about,milw0rm,imagez,sh_name,myshellexec,checkproxyhost,dosyayicek,c99_buff_prepare,c99_sess_put,c99getsource,c99sh_getupdate,c99fsearch,c99shexit,view_perms,posix_getpwuid,posix_getgrgid,posix_kill,parse_perms,parsesort,view_perms_color,set_encoder_input,ls_setcheckboxall,ls_reverse_all,rsg_read,rsg_glob,selfURL,dispsecinfo,unix2DosTime,addFile,system,get_users,view_size,DirFiles,DirFilesWide,DirPrintHTMLHeaders,GetFilesTotal,GetTitles,GetTimeTotal,GetMatchesCount,GetFileMatchesCount,GetResultFiles,fs_copy_dir,fs_copy_obj,fs_move_dir,fs_move_obj,fs_rmdir,SearchText,getmicrotime MySQL : ON | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /usr/share/doc/postgresql-9.2.24/html/ |
Upload File : |
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>The Query Tree</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.2.24 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="The Rule System"
HREF="rules.html"><LINK
REL="PREVIOUS"
TITLE="The Rule System"
HREF="rules.html"><LINK
REL="NEXT"
TITLE="Views and the Rule System"
HREF="rules-views.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2017-11-06T22:43:11"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.2.24 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="The Rule System"
HREF="rules.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="rules.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 37. The Rule System</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Views and the Rule System"
HREF="rules-views.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="QUERYTREE"
>37.1. The Query Tree</A
></H1
><P
> To understand how the rule system works it is necessary to know
when it is invoked and what its input and results are.</P
><P
> 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 <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> statement.</P
><P
> Now what is a query tree? It is an internal representation of an
<ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> 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
<TT
CLASS="VARNAME"
>debug_print_parse</TT
>,
<TT
CLASS="VARNAME"
>debug_print_rewritten</TT
>, or
<TT
CLASS="VARNAME"
>debug_print_plan</TT
>. The rule actions are also
stored as query trees, in the system catalog
<TT
CLASS="STRUCTNAME"
>pg_rewrite</TT
>. They are not formatted like
the log output, but they contain exactly the same information.</P
><P
> Reading a raw query tree requires some experience. But since
<ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> representations of query trees are
sufficient to understand the rule system, this chapter will not
teach how to read them.</P
><P
> When reading the <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> 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
<P
></P
></P><DIV
CLASS="VARIABLELIST"
><DL
><DT
>the command type</DT
><DD
><P
> This is a simple value telling which command
(<TT
CLASS="COMMAND"
>SELECT</TT
>, <TT
CLASS="COMMAND"
>INSERT</TT
>,
<TT
CLASS="COMMAND"
>UPDATE</TT
>, <TT
CLASS="COMMAND"
>DELETE</TT
>) produced
the query tree.
</P
></DD
><DT
>the range table</DT
><DD
><P
> The range table is a list of relations that are used in the query.
In a <TT
CLASS="COMMAND"
>SELECT</TT
> statement these are the relations given after
the <TT
CLASS="LITERAL"
>FROM</TT
> key word.
</P
><P
> 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 <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
>
statement. This can happen after the range tables of rules
have been merged in. The examples in this chapter will not have
this situation.
</P
></DD
><DT
>the result relation</DT
><DD
><P
> This is an index into the range table that identifies the
relation where the results of the query go.
</P
><P
> <TT
CLASS="COMMAND"
>SELECT</TT
> queries don't have a result
relation. (The special case of <TT
CLASS="COMMAND"
>SELECT INTO</TT
> is
mostly identical to <TT
CLASS="COMMAND"
>CREATE TABLE</TT
> followed by
<TT
CLASS="LITERAL"
>INSERT ... SELECT</TT
>, and is not discussed
separately here.)
</P
><P
> For <TT
CLASS="COMMAND"
>INSERT</TT
>, <TT
CLASS="COMMAND"
>UPDATE</TT
>, and
<TT
CLASS="COMMAND"
>DELETE</TT
> commands, the result relation is the table
(or view!) where the changes are to take effect.
</P
></DD
><DT
>the target list</DT
><DD
><P
> The target list is a list of expressions that define the
result of the query. In the case of a
<TT
CLASS="COMMAND"
>SELECT</TT
>, these expressions are the ones that
build the final output of the query. They correspond to the
expressions between the key words <TT
CLASS="COMMAND"
>SELECT</TT
>
and <TT
CLASS="COMMAND"
>FROM</TT
>. (<TT
CLASS="LITERAL"
>*</TT
> 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.)
</P
><P
> <TT
CLASS="COMMAND"
>DELETE</TT
> commands don't need a normal target list
because they don't produce any result. Instead, the rule system
adds a special <ACRONYM
CLASS="ACRONYM"
>CTID</ACRONYM
> entry to the empty target list,
to allow the executor to find the row to be deleted.
(<ACRONYM
CLASS="ACRONYM"
>CTID</ACRONYM
> is added when the result relation is an ordinary
table. If it is a view, a whole-row variable is added instead,
as described in <A
HREF="rules-views.html#RULES-VIEWS-UPDATE"
>Section 37.2.4</A
>.)
</P
><P
> For <TT
CLASS="COMMAND"
>INSERT</TT
> commands, the target list describes
the new rows that should go into the result relation. It consists of the
expressions in the <TT
CLASS="LITERAL"
>VALUES</TT
> clause or the ones from the
<TT
CLASS="COMMAND"
>SELECT</TT
> clause in <TT
CLASS="LITERAL"
>INSERT
... SELECT</TT
>. 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.
</P
><P
> For <TT
CLASS="COMMAND"
>UPDATE</TT
> commands, the target list
describes the new rows that should replace the old ones. In the
rule system, it contains just the expressions from the <TT
CLASS="LITERAL"
>SET
column = expression</TT
> part of the command. The planner will
handle missing columns by inserting expressions that copy the values
from the old row into the new one. Just as for <TT
CLASS="COMMAND"
>DELETE</TT
>,
the rule system adds a <ACRONYM
CLASS="ACRONYM"
>CTID</ACRONYM
> or whole-row variable so that
the executor can identify the old row to be updated.
</P
><P
> 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.
</P
></DD
><DT
>the qualification</DT
><DD
><P
> 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
(<TT
CLASS="COMMAND"
>INSERT</TT
>, <TT
CLASS="COMMAND"
>UPDATE</TT
>,
<TT
CLASS="COMMAND"
>DELETE</TT
>, or <TT
CLASS="COMMAND"
>SELECT</TT
>) for the
final result row should be executed or not. It corresponds to the <TT
CLASS="LITERAL"
>WHERE</TT
> clause
of an <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> statement.
</P
></DD
><DT
>the join tree</DT
><DD
><P
> The query's join tree shows the structure of the <TT
CLASS="LITERAL"
>FROM</TT
> clause.
For a simple query like <TT
CLASS="LITERAL"
>SELECT ... FROM a, b, c</TT
>, the join tree is just
a list of the <TT
CLASS="LITERAL"
>FROM</TT
> items, because we are allowed to join them in
any order. But when <TT
CLASS="LITERAL"
>JOIN</TT
> expressions, 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 <TT
CLASS="LITERAL"
>JOIN</TT
> expressions. The
restrictions associated with particular <TT
CLASS="LITERAL"
>JOIN</TT
> clauses (from <TT
CLASS="LITERAL"
>ON</TT
> or
<TT
CLASS="LITERAL"
>USING</TT
> expressions) are stored as qualification expressions attached
to those join-tree nodes. It turns out to be convenient to store
the top-level <TT
CLASS="LITERAL"
>WHERE</TT
> expression as a qualification attached to the
top-level join-tree item, too. So really the join tree represents
both the <TT
CLASS="LITERAL"
>FROM</TT
> and <TT
CLASS="LITERAL"
>WHERE</TT
> clauses of a <TT
CLASS="COMMAND"
>SELECT</TT
>.
</P
></DD
><DT
>the others</DT
><DD
><P
> The other parts of the query tree like the <TT
CLASS="LITERAL"
>ORDER BY</TT
>
clause 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.
</P
></DD
></DL
></DIV
><P></P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="rules.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="rules-views.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>The Rule System</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="rules.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Views and the Rule System</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>