Thread: 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: > > 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() ? > 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: > 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 >