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 19073Attachments:
/bjm/difftext/plainDownload
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);