Thread: Nested SPI_exec's
Hi All, I'm in the process of moving a rather complicated plpgsql stored procedure to a C module and I'm curious about how to implement the for each row syntax in C. My understanding is that if you perform an SPI_exec whilst looping through the results of another exec the allocated SPITupleTable will be automatically unallocated. Is this correct or am I way off here and it's actually safe to iterate through the tuples in an SPITupleTable and perform additional SPI_exec's? Thinking about this now it probably is but I thought it better to ask. The other query I have relates to the transaction 'safe-ness' of a C module. If I initiate all of this within a transaction (from outside the C module), is everything within the SPI module automagically encapsulated with the transaction or do I have to manage the transaction from within the SPI module? Rgds, Jason
Jason Godden <jasongodden@optushome.com.au> writes:
> I'm in the process of moving a rather complicated plpgsql stored procedure to
> a C module and I'm curious about how to implement the for each row syntax in
> C. My understanding is that if you perform an SPI_exec whilst looping
> through the results of another exec the allocated SPITupleTable will be
> automatically unallocated. Is this correct or am I way off here and it's
> actually safe to iterate through the tuples in an SPITupleTable and perform
> additional SPI_exec's?
SPI won't auto-deallocate tuple tables unless you leave the procedure
(ie call SPI_finish). It's true that the static variable SPI_tuptable
will be overwritten by each exec, but you can just copy that into a
local variable and continue to operate on the tuple table till you
are done with it. It'd probably be wise to explicitly do
SPI_freetuptable when you are done with a result, if you're making
more of them inside a loop...
> The other query I have relates to the transaction 'safe-ness' of a C module.
> If I initiate all of this within a transaction (from outside the C module),
> is everything within the SPI module automagically encapsulated with the
> transaction or do I have to manage the transaction from within the SPI
> module?
You don't and in fact can't manage transactions in a callable procedure.
regards, tom lane
Hi Tom, Thanks for the fast response. One learns something new everyday! Cheers, Jason On Tue, 7 Oct 2003 12:14 am, Tom Lane wrote: > Jason Godden <jasongodden@optushome.com.au> writes: > > I'm in the process of moving a rather complicated plpgsql stored > > procedure to a C module and I'm curious about how to implement the for > > each row syntax in C. My understanding is that if you perform an > > SPI_exec whilst looping through the results of another exec the allocated > > SPITupleTable will be automatically unallocated. Is this correct or am I > > way off here and it's actually safe to iterate through the tuples in an > > SPITupleTable and perform additional SPI_exec's? > > SPI won't auto-deallocate tuple tables unless you leave the procedure > (ie call SPI_finish). It's true that the static variable SPI_tuptable > will be overwritten by each exec, but you can just copy that into a > local variable and continue to operate on the tuple table till you > are done with it. It'd probably be wise to explicitly do > SPI_freetuptable when you are done with a result, if you're making > more of them inside a loop... > > > The other query I have relates to the transaction 'safe-ness' of a C > > module. If I initiate all of this within a transaction (from outside the > > C module), is everything within the SPI module automagically encapsulated > > with the transaction or do I have to manage the transaction from within > > the SPI module? > > You don't and in fact can't manage transactions in a callable procedure. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly