471,306 Members | 854 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,306 software developers and data experts.

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

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

Nov 12 '05 #1
2 1638
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
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.

Similar topics

reply views Thread by Glen | last post: by
6 posts views Thread by Andy Baker | last post: by
3 posts views Thread by Erik Bongers | last post: by
10 posts views Thread by nimmi_srivastav | last post: by
6 posts views Thread by B0nj | last post: by
8 posts views Thread by Robert W. | last post: by
3 posts views Thread by jdurancomas | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.