Re: Recursion and SPI

"Mikheev, Vadim" <vmikheev(at)SECTORBASE(dot)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">