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
Show quoted text
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