Thread: calling plpgsql from c

Hallo,

I'm trying to call plpgsql functions from c functions directly through 
the Oid, but i have a problem: it seems that the plpgsql interpreter 
calls SPI_connect and fails even if the caller has already 
spi-connected. I am working on recursive functions in c and so i can not 
call SPI_finish by myself before calling the plpgsql function since i 
would loose tons of stuff (or am i missing something?). Now, is there a 
way to work around this? If not: wouldn't it be meaningful to change 
this behavior?

thank you

max.

Max Jacob <Max.Jacob@ircam.fr> writes:
> I'm trying to call plpgsql functions from c functions directly through 
> the Oid, but i have a problem: it seems that the plpgsql interpreter 
> calls SPI_connect and fails even if the caller has already 
> spi-connected.

This is a safety check.  If you are connected to SPI, you need to call
SPI_push() and SPI_pop() around any operation that might involve
recursive use of SPI.  That helps delimit "your" calls versus "their"
calls versus "no man's land".

It does seem that this is quite undocumented though.  Jan?
        regards, tom lane

Tom Lane wrote:
> Max Jacob <Max.Jacob@ircam.fr> writes:
> > I'm trying to call plpgsql functions from c functions directly through
> > the Oid, but i have a problem: it seems that the plpgsql interpreter
> > calls SPI_connect and fails even if the caller has already
> > spi-connected.
>
> This is a safety check.  If you are connected to SPI, you need to call
> SPI_push() and SPI_pop() around any operation that might involve
> recursive use of SPI.  That helps delimit "your" calls versus "their"
> calls versus "no man's land".
>
> It does seem that this is quite undocumented though.  Jan?

I have documented SPI_push() and SPI_pop() with the attached SGML patch.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/spi.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/spi.sgml,v
retrieving revision 1.32
diff -c -c -r1.32 spi.sgml
*** doc/src/sgml/spi.sgml    5 Mar 2004 01:00:45 -0000    1.32
--- doc/src/sgml/spi.sgml    17 Mar 2004 01:04:25 -0000
***************
*** 199,204 ****
--- 199,266 ----

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

+ <refentry id="spi-spi-push">
+  <refmeta>
+   <refentrytitle>SPI_push</refentrytitle>
+  </refmeta>
+
+  <refnamediv>
+   <refname>SPI_push</refname>
+   <refpurpose>pushes SPI stack to allow recursive SPI calls</refpurpose>
+  </refnamediv>
+
+  <indexterm><primary>SPI_push</primary></indexterm>
+
+  <refsynopsisdiv>
+ <synopsis>
+ void SPI_push(void)
+ </synopsis>
+  </refsynopsisdiv>
+
+  <refsect1>
+   <title>Description</title>
+
+   <para>
+    <function>SPI_push</function> pushes a new environment on to the
+    SPI call stack, allowing recursive calls to use a new environment.
+   </para>
+  </refsect1>
+
+ </refentry>
+
+ <!-- *********************************************** -->
+
+ <refentry id="spi-spi-pop">
+  <refmeta>
+   <refentrytitle>SPI_pop</refentrytitle>
+  </refmeta>
+
+  <refnamediv>
+   <refname>SPI_pop</refname>
+   <refpurpose>pops SPI stack to allow recursive SPI calls</refpurpose>
+  </refnamediv>
+
+  <indexterm><primary>SPI_pop</primary></indexterm>
+
+  <refsynopsisdiv>
+ <synopsis>
+ void SPI_pop(void)
+ </synopsis>
+  </refsynopsisdiv>
+
+  <refsect1>
+   <title>Description</title>
+
+   <para>
+    <function>SPI_pop</function> pops the previous environment from the
+    SPI call stack.  For use when returning from recursive SPI calls.
+   </para>
+  </refsect1>
+
+ </refentry>
+
+ <!-- *********************************************** -->
+
  <refentry id="spi-spi-exec">
   <refmeta>
    <refentrytitle>SPI_exec</refentrytitle>
Index: src/backend/executor/spi.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/executor/spi.c,v
retrieving revision 1.110
diff -c -c -r1.110 spi.c
*** src/backend/executor/spi.c    5 Mar 2004 00:47:01 -0000    1.110
--- src/backend/executor/spi.c    17 Mar 2004 01:04:27 -0000
***************
*** 201,212 ****
--- 201,214 ----
      SPI_tuptable = NULL;
  }

+ /* Pushes SPI stack to allow recursive SPI calls */
  void
  SPI_push(void)
  {
      _SPI_curid++;
  }

+ /* Pops SPI stack to allow recursive SPI calls */
  void
  SPI_pop(void)
  {
Index: src/include/executor/spi.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/executor/spi.h,v
retrieving revision 1.42
diff -c -c -r1.42 spi.h
*** src/include/executor/spi.h    5 Mar 2004 00:47:01 -0000    1.42
--- src/include/executor/spi.h    17 Mar 2004 01:04:28 -0000
***************
*** 81,88 ****
  extern int    SPI_finish(void);
  extern void SPI_push(void);
  extern void SPI_pop(void);
! extern int    SPI_exec(const char *src, int tcount);
! extern int SPI_execp(void *plan, Datum *values, const char *Nulls,
            int tcount);
  extern int SPI_execp_current(void *plan, Datum *values, const char *Nulls,
                               bool useCurrentSnapshot, int tcount);
--- 81,88 ----
  extern int    SPI_finish(void);
  extern void SPI_push(void);
  extern void SPI_pop(void);
! extern int  SPI_exec(const char *src, int tcount);
! extern int  SPI_execp(void *plan, Datum *values, const char *Nulls,
            int tcount);
  extern int SPI_execp_current(void *plan, Datum *values, const char *Nulls,
                               bool useCurrentSnapshot, int tcount);