postgrespro
diff --git a/‎doc/src/sgml/plperl.sgml
Lines changed: 54 additions & 0 deletions b/‎doc/src/sgml/plperl.sgml
Lines changed: 54 additions & 0 deletions
diff --git a/‎doc/src/sgml/plpgsql.sgml
Lines changed: 47 additions & 44 deletions b/‎doc/src/sgml/plpgsql.sgml
Lines changed: 47 additions & 44 deletions
diff --git a/‎doc/src/sgml/plpython.sgml
Lines changed: 41 additions & 0 deletions b/‎doc/src/sgml/plpython.sgml
Lines changed: 41 additions & 0 deletions
diff --git a/‎doc/src/sgml/pltcl.sgml
Lines changed: 41 additions & 0 deletions b/‎doc/src/sgml/pltcl.sgml
Lines changed: 41 additions & 0 deletions
diff --git a/‎doc/src/sgml/ref/call.sgml
Lines changed: 7 additions & 0 deletions b/‎doc/src/sgml/ref/call.sgml
Lines changed: 7 additions & 0 deletions
diff --git a/‎doc/src/sgml/ref/create_procedure.sgml
Lines changed: 7 additions & 0 deletions b/‎doc/src/sgml/ref/create_procedure.sgml
Lines changed: 7 additions & 0 deletions
diff --git a/‎doc/src/sgml/ref/do.sgml
Lines changed: 7 additions & 0 deletions b/‎doc/src/sgml/ref/do.sgml
Lines changed: 7 additions & 0 deletions
@@ -661,6 +661,60 @@ SELECT release_hosts_query();
     </para>
     </listitem>
     </varlistentry>
+
+    <varlistentry>
+     <term>
+      <literal><function>spi_commit()</function></literal>
+      <indexterm>
+       <primary>spi_commit</primary>
+       <secondary>in PL/Perl</secondary>
+     </indexterm>
+     </term>
+     <term>
+      <literal><function>spi_rollback()</function></literal>
+      <indexterm>
+       <primary>spi_rollback</primary>
+       <secondary>in PL/Perl</secondary>
+      </indexterm>
+     </term>
+     <listitem>
+      <para>
+       Commit or roll back the current transaction.  This can only be called
+       in a procedure or anonymous code block (<command>DO</command> command)
+       called from the top level.  (Note that it is not possible to run the
+       SQL commands <command>COMMIT</command> or <command>ROLLBACK</command>
+       via <function>spi_exec_query</function> or similar.  It has to be done
+       using these functions.)  After a transaction is ended, a new
+       transaction is automatically started, so there is no separate function
+       for that.
+      </para>
+
+      <para>
+       Here is an example:
+<programlisting>
+CREATE PROCEDURE transaction_test1()
+LANGUAGE plperl
+AS $$
+foreach my $i (0..9) {
+    spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
+    if ($i % 2 == 0) {
+        spi_commit();
+    } else {
+        spi_rollback();
+    }
+}
+$$;
+
+CALL transaction_test1();
+</programlisting>
+      </para>
+
+      <para>
+       Transactions cannot be ended when a cursor created by
+       <function>spi_query</function> is open.
+      </para>
+     </listitem>
+    </varlistentry>
    </variablelist>
  </sect2>
 
 
@@ -3449,6 +3449,48 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
 
   </sect1>
 
+  <sect1 id="plpgsql-transactions">
+   <title>Transaction Management</title>
+
+   <para>
+    In procedures invoked by the <command>CALL</command> command from the top
+    level as well as in anonymous code blocks (<command>DO</command> command)
+    called from the top level, it is possible to end transactions using the
+    commands <command>COMMIT</command> and <command>ROLLBACK</command>.  A new
+    transaction is started automatically after a transaction is ended using
+    these commands, so there is no separate <command>START
+    TRANSACTION</command> command.  (Note that <command>BEGIN</command> and
+    <command>END</command> have different meanings in PL/pgSQL.)
+   </para>
+
+   <para>
+    Here is a simple example:
+<programlisting>
+CREATE PROCEDURE transaction_test1()
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    FOR i IN 0..9 LOOP
+        INSERT INTO test1 (a) VALUES (i);
+        IF i % 2 = 0 THEN
+            COMMIT;
+        ELSE
+            ROLLBACK;
+        END IF;
+    END LOOP;
+END
+$$;
+
+CALL transaction_test1();
+</programlisting>
+   </para>
+
+   <para>
+    A transaction cannot be ended inside a loop over a query result, nor
+    inside a block with exception handlers.
+   </para>
+  </sect1>
+
   <sect1 id="plpgsql-errors-and-messages">
    <title>Errors and Messages</title>
 
@@ -5432,14 +5474,13 @@ SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
 <programlisting>
 CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
     a_running_job_count INTEGER;
-    PRAGMA AUTONOMOUS_TRANSACTION; -- <co id="co.plpgsql-porting-pragma"/>
 BEGIN
-    LOCK TABLE cs_jobs IN EXCLUSIVE MODE; -- <co id="co.plpgsql-porting-locktable"/>
+    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
 
     SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
 
     IF a_running_job_count &gt; 0 THEN
-        COMMIT; -- free lock <co id="co.plpgsql-porting-commit"/>
+        COMMIT; -- free lock
         raise_application_error(-20000,
                  'Unable to create a new job: a job is currently running.');
     END IF;
@@ -5459,45 +5500,11 @@ show errors
 </programlisting>
    </para>
 
-   <para>
-    Procedures like this can easily be converted into <productname>PostgreSQL</productname>
-    functions returning <type>void</type>. This procedure in
-    particular is interesting because it can teach us some things:
-
-    <calloutlist>
-     <callout arearefs="co.plpgsql-porting-pragma">
-      <para>
-       There is no <literal>PRAGMA</literal> statement in <productname>PostgreSQL</productname>.
-      </para>
-     </callout>
-
-     <callout arearefs="co.plpgsql-porting-locktable">
-      <para>
-       If you do a <command>LOCK TABLE</command> in <application>PL/pgSQL</application>,
-       the lock will not be released until the calling transaction is
-       finished.
-      </para>
-     </callout>
-
-     <callout arearefs="co.plpgsql-porting-commit">
-      <para>
-       You cannot issue <command>COMMIT</command> in a
-       <application>PL/pgSQL</application> function.  The function is
-       running within some outer transaction and so <command>COMMIT</command>
-       would imply terminating the function's execution.  However, in
-       this particular case it is not necessary anyway, because the lock
-       obtained by the <command>LOCK TABLE</command> will be released when
-       we raise an error.
-      </para>
-     </callout>
-    </calloutlist>
-   </para>
-
    <para>
     This is how we could port this procedure to <application>PL/pgSQL</application>:
 
 <programlisting>
-CREATE OR REPLACE FUNCTION cs_create_job(v_job_id integer) RETURNS void AS $$
+CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$
 DECLARE
     a_running_job_count integer;
 BEGIN
@@ -5506,6 +5513,7 @@ BEGIN
     SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
 
     IF a_running_job_count &gt; 0 THEN
+        COMMIT; -- free lock
         RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- <co id="co.plpgsql-porting-raise"/>
     END IF;
 
@@ -5518,6 +5526,7 @@ BEGIN
         WHEN unique_violation THEN -- <co id="co.plpgsql-porting-exception"/>
             -- don't worry if it already exists
     END;
+    COMMIT;
 END;
 $$ LANGUAGE plpgsql;
 </programlisting>
@@ -5541,12 +5550,6 @@ $$ LANGUAGE plpgsql;
       </para>
      </callout>
     </calloutlist>
-
-    The main functional difference between this procedure and the
-    Oracle equivalent is that the exclusive lock on the <literal>cs_jobs</literal>
-    table will be held until the calling transaction completes.  Also, if
-    the caller later aborts (for example due to an error), the effects of
-    this procedure will be rolled back.
    </para>
    </example>
   </sect2>
 
@@ -1370,6 +1370,47 @@ $$ LANGUAGE plpythonu;
   </sect2>
  </sect1>
 
+ <sect1 id="plpython-transactions">
+  <title>Transaction Management</title>
+
+  <para>
+   In a procedure called from the top level or an anonymous code block
+   (<command>DO</command> command) called from the top level it is possible to
+   control transactions.  To commit the current transaction, call
+   <literal>plpy.commit()</literal>.  To roll back the current transaction,
+   call <literal>plpy.rollback()</literal>.  (Note that it is not possible to
+   run the SQL commands <command>COMMIT</command> or
+   <command>ROLLBACK</command> via <function>plpy.execute</function> or
+   similar.  It has to be done using these functions.)  After a transaction is
+   ended, a new transaction is automatically started, so there is no separate
+   function for that.
+  </para>
+
+  <para>
+   Here is an example:
+<programlisting>
+CREATE PROCEDURE transaction_test1()
+LANGUAGE plpythonu
+AS $$
+for i in range(0, 10):
+    plpy.execute("INSERT INTO test1 (a) VALUES (%d)" % i)
+    if i % 2 == 0:
+        plpy.commit()
+    else:
+        plpy.rollback()
+$$;
+
+CALL transaction_test1();
+</programlisting>
+  </para>
+
+  &
10000
lt;para>
+   Transactions cannot be ended when a cursor created by
+   <literal>plpy.cursor</literal> is open or when an explicit subtransaction
+   is active.
+  </para>
+ </sect1>
+
  <sect1 id="plpython-util">
   <title>Utility Functions</title>
   <para>
 
@@ -1002,6 +1002,47 @@ $$ LANGUAGE pltcl;
     </para>
    </sect1>
 
+   <sect1 id="pltcl-transactions">
+    <title>Transaction Management</title>
+
+    <para>
+     In a procedure called from the top level or an anonymous code block
+     (<command>DO</command> command) called from the top level it is possible
+     to control transactions.  To commit the current transaction, call the
+     <literal>commit</literal> command.  To roll back the current transaction,
+     call the <literal>rollback</literal> command.  (Note that it is not
+     possible to run the SQL commands <command>COMMIT</command> or
+     <command>ROLLBACK</command> via <function>spi_exec</function> or similar.
+     It has to be done using these functions.)  After a transaction is ended,
+     a new transaction is automatically started, so there is no separate
+     command for that.
+    </para>
+
+    <para>
+     Here is an example:
+<programlisting>
+CREATE PROCEDURE transaction_test1()
+LANGUAGE pltcl
+AS $$
+for {set i 0} {$i &lt; 10} {incr i} {
+    spi_exec "INSERT INTO test1 (a) VALUES ($i)"
+    if {$i % 2 == 0} {
+        commit
+    } else {
+        rollback
+    }
+}
+$$;
+
+CALL transaction_test1();
+</programlisting>
+    </para>
+
+    <para>
+     Transactions cannot be ended when an explicit subtransaction is active.
+    </para>
+   </sect1>
+
    <sect1 id="pltcl-config">
     <title>PL/Tcl Configuration</title>
 
 
@@ -70,6 +70,13 @@ CALL <replaceable class="parameter">name</replaceable> ( [ <replaceable class="p
   <para>
    To call a function (not a procedure), use <command>SELECT</command> instead.
   </para>
+
+  <para>
+   If <command>CALL</command> is executed in a transaction block, then the
+   called procedure cannot execute transaction control statements.
+   Transaction control statements are only allowed if <command>CALL</command>
+   is executed in its own transaction.
+  </para>
  </refsect1>
 
  <refsect1>
 
@@ -228,6 +228,13 @@ CREATE [ OR REPLACE ] PROCEDURE
        procedure exit, unless the current transaction is rolled back.
       </para>
 
+      <para>
+       If a <literal>SET</literal> clause is attached to a procedure, then
+       that procedure cannot execute transaction control statements (fo
C417
r
+       example, <command>COMMIT</command> and <command>ROLLBACK</command>,
+       depending on the language).
+      </para>
+
       <para>
        See <xref linkend="sql-set"/> and
        <xref linkend="runtime-config"/>
 
@@ -91,6 +91,13 @@ DO [ LANGUAGE <replaceable class="parameter">lang_name</replaceable> ] <replacea
    This is the same privilege requirement as for creating a function
    in the language.
   </para>
+
+  <para>
+   If <command>DO</command> is executed in a transaction block, then the
+   procedure code cannot execute transaction control statements.  Transaction
+   control statements are only allowed if <command>DO</command> is executed in
+   its own transaction.
+  </para>
  </refsect1>
 
  <refsect1 id="sql-do-examples">