Recursive stored procedure in C.
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
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:
Show quoted text
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
Leif Jensen <leif@crysberg.dk> writes:
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.
If you want the trigger levels to be independent (which I think you do)
then you need a SPI_connect and a SPI_finish in *each* trigger call.
Whatever magic you are doing to special-case recursion is all wrong and
should be ripped out, root and branch.
The bit you may be missing is that you need to do SPI_push and SPI_pop
around anything that might possibly call another function using SPI (eg,
your own recursive instance, but also anything else that might use SPI).
SPI_execute() and friends do this for you, but if the recursion is not
via a SPI-executed query then you need to do it explicitly.
regards, tom lane
Hi Tom,
Thank you for the suggestions. I didn't know anything about SPI_push and
SPI_pop and I will walk through my code and stuff them in. Am I looking at
some wrong documentation ? I never saw anything about those ?
Do you know any good examples doing such things ?
Leif
On Thu, 14 Jul 2005, Tom Lane wrote:
Leif Jensen <leif@crysberg.dk> writes:
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.If you want the trigger levels to be independent (which I think you do)
Yes.
then you need a SPI_connect and a SPI_finish in *each* trigger call.
Whatever magic you are doing to special-case recursion is all wrong and
should be ripped out, root and branch.
Ok, will do.
The bit you may be missing is that you need to do SPI_push and SPI_pop
around anything that might possibly call another function using SPI (eg,
your own recursive instance, but also anything else that might use SPI).
SPI_execute() and friends do this for you, but if the recursion is not
SPI_exec() ?
Show quoted text
via a SPI-executed query then you need to do it explicitly.
regards, tom lane
Leif Jensen <leif@crysberg.dk> writes:
Thank you for the suggestions. I didn't know anything about SPI_push and
SPI_pop and I will walk through my code and stuff them in. Am I looking at
some wrong documentation ? I never saw anything about those ?
They weren't well documented in old releases, but see
http://www.postgresql.org/docs/8.0/static/spi-spi-push.html
http://www.postgresql.org/docs/8.0/static/spi-spi-pop.html
regards, tom lane
Hi again,
Thanks. No changes on this 7.x.x -> 8.x.x ?
.. and just to be sure: SPI_exec does the SPI_push/SPI_pop thing too ?
Leif
On Thu, 14 Jul 2005, Tom Lane wrote:
Show quoted text
Leif Jensen <leif@crysberg.dk> writes:
Thank you for the suggestions. I didn't know anything about SPI_push and
SPI_pop and I will walk through my code and stuff them in. Am I looking at
some wrong documentation ? I never saw anything about those ?They weren't well documented in old releases, but see
http://www.postgresql.org/docs/8.0/static/spi-spi-push.html
http://www.postgresql.org/docs/8.0/static/spi-spi-pop.htmlregards, tom lane