Re: Recursive stored procedure in C.

Oops, I forgot to say that I have tried PostgreSQL 7.4.1, 7.4.6, and
7.4.7 all with the same result. I'm running this on a Linux (Slackware
10.0), kernel 2.6.10y.

Leif

On Thu, 14 Jul 2005, Leif Jensen wrote:

>
> Hi all,
>
> I am trying to make a stored procedure in C that is used as a trigger
> on before/after insert/update on a certain table. This procedure might do
> inserts/updates on the same table (recursively triggering itself). I have
> made (pretty) sure that I'm not using 'global' variables in this module
> and that I do an SPI_connect() the very first time I enter and an
> SPI_finish() at the last exit.
>
> This works to some point, but in some instances I get
> SPI_ERROR_UNCONNECTED from an SPI_exec() call a few recursive layers down,
> e.g. the following code yields:
>
> ret = SPI_exec( sql_query, 0 ); => SPI_ERROR_UNCONNECTED
> if( ret == SPI_ERROR_UNCONNECTED ) {
> ret = SPI_finish(); => SPI_ERROR_UNCONNECTED
> ret = SPI_connect(); => SPI_ERROR_CONNECT
> ret = SPI_exec( sql_query, 0 ); => SPI_ERROR_UNCONNECTED
> }
>
> ??? (This 'if' was mainly to try to figure out what was going on, since
> I should not do an SPI_finish() at all until the very end.)
>
> Is such recursivity at all possible ? What are the pitfalls ? Any good
> examples out there?
>
> Any suggestions at all will be much appreciated,
>
> Leif
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>