Home • Explore • Blog • CI
☰



postgresql
9th chunk of `doc/src/sgml/arch-dev.sgml`
9810def6fd75e4b167092a978292622af7473bde3c569c430000000100000cce
 output row, namely
    the first one in sorted order.  It keeps the remaining rows stored so
    that it can deliver them in sorted order in response to later demands.
   </para>

   <para>
    The <literal>MergeJoin</literal> node similarly demands the first row
    from its right subplan.  Then it compares the two rows to see if they
    can be joined; if so, it returns a join row to its caller.  On the next
    call, or immediately if it cannot join the current pair of inputs,
    it advances to the next row of one table
    or the other (depending on how the comparison came out), and again
    checks for a match.  Eventually, one subplan or the other is exhausted,
    and the <literal>MergeJoin</literal> node returns NULL to indicate that
    no more join rows can be formed.
   </para>

   <para>
    Complex queries can involve many levels of plan nodes, but the general
    approach is the same: each node computes and returns its next output
    row each time it is called.  Each node is also responsible for applying
    any selection or projection expressions that were assigned to it by
    the planner.
   </para>

   <para>
    The executor mechanism is used to evaluate all five basic SQL query
    types: <command>SELECT</command>, <command>INSERT</command>,
    <command>UPDATE</command>, <command>DELETE</command>, and
    <command>MERGE</command>.
    For <command>SELECT</command>, the top-level executor code
    only needs to send each row returned by the query plan tree
    off to the client.  <command>INSERT ... SELECT</command>,
    <command>UPDATE</command>, <command>DELETE</command>, and
    <command>MERGE</command>
    are effectively <command>SELECT</command>s under a special
    top-level plan node called <literal>ModifyTable</literal>.
   </para>

   <para>
    <command>INSERT ... SELECT</command> feeds the rows up
    to <literal>ModifyTable</literal> for insertion.  For
    <command>UPDATE</command>, the planner arranges that each
    computed row includes all the updated column values, plus the
    <firstterm>TID</firstterm> (tuple ID, or row ID) of the original
    target row; this data is fed up to the <literal>ModifyTable</literal>
    node, which uses the information to create a new updated row and
    mark the old row deleted.  For <command>DELETE</command>, the only
    column that is actually returned by the plan is the TID, and the
    <literal>ModifyTable</literal> node simply uses the TID to visit each
    target row and mark it deleted.  For <command>MERGE</command>, the
    planner joins the source and target relations, and includes all
    column values required by any of the <literal>WHEN</literal> clauses,
    plus the TID of the target row; this data is fed up to the
    <literal>ModifyTable</literal> node, which uses the information to
    work out which <literal>WHEN</literal> clause to execute, and then
    inserts, updates or deletes the target row, as required.
   </para>

   <para>
    A simple <command>INSERT ... VALUES</command> command creates a
    trivial plan tree consisting of a single <literal>Result</literal>
    node, which computes just one result row, feeding that up
    to <literal>ModifyTable</literal> to perform the insertion.
   </para>

  </sect1>

 </chapter>
Title: Executor Responsibilities and SQL Query Types
Summary
Each plan node is responsible for applying selection and projection expressions assigned by the planner. The executor mechanism is used for SELECT, INSERT, UPDATE, DELETE, and MERGE queries. SELECT queries send each row to the client. INSERT ... SELECT, UPDATE, DELETE, and MERGE are SELECT queries under a ModifyTable node. INSERT ... SELECT feeds rows to ModifyTable for insertion. UPDATE includes updated column values and the TID of the original row, allowing ModifyTable to create a new row and mark the old one as deleted. DELETE returns only the TID, which ModifyTable uses to mark the target row as deleted. MERGE joins the source and target relations, including column values and the TID, to determine which WHEN clause to execute and then inserts, updates, or deletes the target row. A simple INSERT ... VALUES command creates a Result node that computes one row, which is fed to ModifyTable for insertion.