query.  Such a statement will be executed nonetheless.
    A not-particularly-useful example is:

<programlisting>
WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;
</programlisting>

    This example would remove all rows from tables <structname>foo</structname> and
    <structname>bar</structname>.  The number of affected rows reported to the client
    would only include rows removed from <structname>bar</structname>.
   </para>

   <para>
    Recursive self-references in data-modifying statements are not
    allowed.  In some cases it is possible to work around this limitation by
    referring to the output of a recursive <literal>WITH</literal>, for example:

<programlisting>
WITH RECURSIVE included_parts(sub_part, part) AS (
    SELECT sub_part, part FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
DELETE FROM parts
  WHERE part IN (SELECT part FROM included_parts);
</programlisting>

    This query would remove all direct and indirect subparts of a product.
   </para>

   <para>
    Data-modifying statements in <literal>WITH</literal> are executed exactly once,
    and always to completion, independently of whether the primary query
    reads all (or indeed any) of their output.  Notice that this is different
    from the rule for <command>SELECT</command> in <literal>WITH</literal>: as stated in the
    previous section, execution of a <command>SELECT</command> is carried only as far
    as the primary query demands its output.
   </para>

   <para>
    The sub-statements in <literal>WITH</literal> are executed concurrently with
    each other and with the main query.  Therefore, when using data-modifying
    statements in <literal>WITH</literal>, the order in which the specified updates
    actually happen is unpredictable.  All the statements are executed with
    the same <firstterm>snapshot</firstterm> (see <xref linkend="mvcc"/>), so they
    cannot <quote>see</quote> one another's effects on the target tables.  This
    alleviates the effects of the unpredictability of the actual order of row
    updates, and means that <literal>RETURNING</literal> data is the only way to
    communicate changes between different <literal>WITH</literal> sub-statements and
    the main query.  An example of this is that in

<programlisting>
WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;
</programlisting>

    the outer <command>SELECT</command> would return the original prices before the
    action of the <command>UPDATE</command>, while in

<programlisting>
WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;
</programlisting>

    the outer <command>SELECT</command> would return the updated data.
   </para>

   <para>
    Trying to update the same row twice in a single statement is not
    supported.  Only one of the modifications takes place, but it is not easy
    (and sometimes not possible) to reliably predict which one.  This also
    applies to deleting a row that was already updated in the same statement:
    only the update is performed.  Therefore you should generally avoid trying
    to modify a single row twice in a single statement.  In particular avoid
    writing <literal>WITH</literal> sub-statements that could affect the same rows
    changed by the main statement or a sibling sub-statement.  The effects
    of such a statement will not be predictable.
   </para>

   <para>
    At present, any table used as the target of a data-modifying statement in
    <literal>WITH</literal> must not have a conditional rule, nor an <literal>ALSO</literal>
    rule, nor an <literal>INSTEAD</literal> rule that expands to multiple statements.
   </para>

  </sect2>

 </sect1>

</chapter>