Home Explore Blog CI



postgresql

48th chunk of `doc/src/sgml/spi.sgml`
5c3c3f7630e81d311e3abd462cbf0270523a33c3c9843ff20000000100000fa1
 <title>Description</title>

  <para>
   <function>SPI_freeplan</function> releases a prepared statement
   previously returned by <function>SPI_prepare</function> or saved by
   <function>SPI_keepplan</function> or <function>SPI_saveplan</function>.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
    <listitem>
     <para>
      pointer to statement to free
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Return Value</title>

  <para>
   0 on success;
   <symbol>SPI_ERROR_ARGUMENT</symbol> if <parameter>plan</parameter>
   is <symbol>NULL</symbol> or invalid
  </para>
 </refsect1>
</refentry>

 </sect1>

 <sect1 id="spi-transaction">
  <title>Transaction Management</title>

  <para>
   It is not possible to run transaction control commands such
   as <command>COMMIT</command> and <command>ROLLBACK</command> through SPI
   functions such as <function>SPI_execute</function>.  There are, however,
   separate interface functions that allow transaction control through SPI.
  </para>

  <para>
   It is not generally safe and sensible to start and end transactions in
   arbitrary user-defined SQL-callable functions without taking into account
   the context in which they are called.  For example, a transaction boundary
   in the middle of a function that is part of a complex SQL expression that
   is part of some SQL command will probably result in obscure internal errors
   or crashes.  The interface functions presented here are primarily intended
   to be used by procedural language implementations to support transaction
   management in SQL-level procedures that are invoked by the <command>CALL</command>
   command, taking the context of the <command>CALL</command> invocation into
   account.  SPI-using procedures implemented in C can implement the same logic, but
   the details of that are beyond the scope of this documentation.
  </para>

<!-- *********************************************** -->

<refentry id="spi-spi-commit">
 <indexterm><primary>SPI_commit</primary></indexterm>
 <indexterm><primary>SPI_commit_and_chain</primary></indexterm>

 <refmeta>
  <refentrytitle>SPI_commit</refentrytitle>
  <manvolnum>3</manvolnum>
 </refmeta>

 <refnamediv>
  <refname>SPI_commit</refname>
  <refname>SPI_commit_and_chain</refname>
  <refpurpose>commit the current transaction</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
void SPI_commit(void)
</synopsis>

<synopsis>
void SPI_commit_and_chain(void)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_commit</function> commits the current transaction.  It is
   approximately equivalent to running the SQL
   command <command>COMMIT</command>.  After the transaction is committed, a
   new transaction is automatically started using default transaction
   characteristics, so that the caller can continue using SPI facilities.
   If there is a failure during commit, the current transaction is instead
   rolled back and a new transaction is started, after which the error is
   thrown in the usual way.
  </para>

  <para>
   <function>SPI_commit_and_chain</function> is the same, but the new
   transaction is started with the same transaction
   characteristics as the just finished one, like with the SQL command
   <command>COMMIT AND CHAIN</command>.
  </para>

  <para>
   These functions can only be executed if the SPI connection has been set as
   nonatomic in the call to <function>SPI_connect_ext</function>.
  </para>
 </refsect1>
</refentry>

<!-- *********************************************** -->

<refentry id="spi-spi-rollback">
 <indexterm><primary>SPI_rollback</primary></indexterm>
 <indexterm><primary>SPI_rollback_and_chain</primary></indexterm>

 <refmeta>
  <refentrytitle>SPI_rollback</refentrytitle>
  <manvolnum>3</manvolnum>
 </refmeta>

 <refnamediv>

Title: SPI Transaction Management: Committing and Rolling Back Transactions
Summary
This section discusses transaction management within SPI, noting that direct transaction control commands like COMMIT and ROLLBACK cannot be executed directly through SPI functions such as `SPI_execute`, but rather through separate interface functions. It advises caution when starting and ending transactions within user-defined SQL-callable functions. The section then details `SPI_commit` and `SPI_commit_and_chain`, which commit the current transaction, with the latter also chaining the new transaction with the characteristics of the just-finished transaction. Finally, it mentions that these functions require the SPI connection to be set as nonatomic during the call to `SPI_connect_ext`.