By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

Nested SPI_exec's

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Jason Godden <ja*********@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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #2

P: n/a
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 <ja*********@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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.