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.