Re: [HACKERS] Recursion and SPI - Mailing list pgsql-patches

"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:

> > Does the SPI interface support recursion?  That is, can a function
> > use SPI to make a query which involves calling another function which
> > uses SPI?
>
> >From http://www.postgresql.org/docs/programmer/spi.htm :
>
> "SPI procedures are always called by some (upper) Executor and the SPI
> manager
> uses the Executor to run your queries. Other procedures may be called by the
>                                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> Executor running queries from your procedure."
>
> > The documentation suggests not, saying that if a function which uses
> > SPI calls another function which uses SPI, it won't work, and calling
> > that ``bad practice.''
>
> >From http://www.postgresql.org/docs/programmer/spi-spiconnect.htm :
>
> "You may get SPI_ERROR_CONNECT error if SPI_connect is called from an
> already
> connected procedure - e.g. if you *directly call* one procedure from another
>                                   ^^^^^^^^^^^^^^^
> connected one. Actually, while the child procedure will be able to use SPI,
> your parent procedure will not be able to continue to use SPI after the
> child
> returns (if SPI_finish is called by the child). It's bad practice."
>
> But you are able to run queries which call SPI functions.

Thanks for the clarification.

Approaching the current documentation from a position of ignorance, I
find it ambiguous.  I propose something along the lines of the
following patch to clarify it.  Thanks.

(Alternatively, perhaps the code could maintain a count of nested
calls to SPI_connect/SPI_finish.  But I didn't try to write that
patch.)

Ian

Index: spi.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/spi.sgml,v
retrieving revision 1.8
diff -u -r1.8 spi.sgml
--- spi.sgml    2000/12/22 18:57:50    1.8
+++ spi.sgml    2001/01/05 22:10:13
@@ -34,10 +34,11 @@
 </Para>

 <Para>
-<Acronym>SPI</Acronym> procedures are always called by some (upper)
-Executor and the <Acronym>SPI</Acronym>
-manager uses the Executor to run your queries. Other procedures may be
-called by the Executor running queries from your procedure.
+Procedures which use <Acronym>SPI</Acronym> are called by the
+Executor.  The <Acronym>SPI</Acronym> calls recursively invoke the
+Executor in turn to run queries.  When the Executor is invoked
+recursively, it may itself call procedures which may make
+<Acronym>SPI</Acronym> calls.
 </Para>

 <Para>
@@ -146,12 +147,17 @@
    utility SPI functions may be called from un-connected procedures.
 </PARA>
 <PARA>
-   You may get <ReturnValue>SPI_ERROR_CONNECT</ReturnValue> error if <Function>SPI_connect</Function> is
-   called from an already connected procedure - e.g. if you directly call one
-   procedure from another connected one.  Actually, while the child procedure
-   will be able to use SPI, your parent procedure will not be able to continue
-   to use SPI after the child returns (if <Function>SPI_finish</Function> is called by the child).
-   It's bad practice.
+   If your procedure is already connected,
+   <Function>SPI_connect</Function> will return an
+   <ReturnValue>SPI_ERROR_CONNECT</ReturnValue> error.  Note that this
+   may happen if a procedure which has called
+   <Function>SPI_connect</Function> directly calls another procedure
+   which itself calls <Function>SPI_connect</Function>.  While
+   recursive calls to the <Acronym>SPI</Acronym> manager are permitted
+   when an <Acronym>SPI</Acronym> query invokes another function which
+   uses <Acronym>SPI</Acronym>, directly nested calls to
+   <Function>SPI_connect</Function> and
+   <Function>SPI_finish</Function> are forbidden.
 </PARA>
 </REFSECT1>
 <REFSECT1 ID="R1-SPI-SPICONNECT-2">