Hi,

Just trying to confirm my understanding of how PG manages transactions 
with respect to stored procedures, in particular, stored procedures 
which invoke other procedures and their attendant SQL statements.

Assuming the following description of a set of procedures:

	procA consists of calls to procB, procC, and procD.

	procB, procC, and procD invoke procE and procF.

	procs B,C,D,E, and F invoke INSERT/UPDATE/SELECT's

My understanding is that since A) PG doesn't currently support nested 
transactions, B) procedures can't currently define transactional 
elements within their body, and C) there's at least an implicit 
transaction of single statement granularity at the outermost level via:

	select procA();

that all INSERT/UPDATE/SELECT invocations within all nested procedures 
operate within a single transactional context, that being the context in 
which the procA() call is made.

Is that correct?

If so, what is the lifetime of any locks which are acquired by the 
INSERT/UPDATE/SELECT statements within the transaction? Is it, as I 
believe, the lifetime of the procA invocation?

I'm currently working with a system that makes extremely heavy use of 
nested pl/pgsql procedures to encode application logic and I'm concerned 
that certain design patterns may dramatically degrade concurrency if 
this transactional analysis is correct. Any insight into patterns of 
development that would avoid locking or concurrency issues would be 
helpful.

Thanks in advance!


ss

Scott Shattuck
Technical Pursuit Inc.


Mon, 25 Nov 2002 11:59:33 -0700
Scott Shattuck <ss@technicalpursuit.com>
transactions and stored procedures