Thread: function transaction scope question
If a function in PL/pgSQL is wrapped by a transaction, does that same single transaction encompass other functions defined elsewhere that this function calls?
Thanks.
On Wed, Jan 14, 2009 at 9:41 AM, Wright, George <George.Wright@infimatic.com> wrote: > If a function in PL/pgSQL is wrapped by a transaction, does that same single > transaction encompass other functions defined elsewhere that this function > calls? There's a rather stronger condition than that to be claimed... *EVERY* PostgreSQL stored function call (irrespective of language) runs within the context of a particular transaction. That transaction context will therefore include all of the following: - any functions that the stored function may call - any triggers that may be fired by virtue of table updates - invocation of NOTIFY (but *not* the notifications that result in listening connections) That's quite comfortably stronger than what you appear to be hoping for :-). -- http://linuxfinances.info/info/linuxdistributions.html Steve Martin - "I like a woman with a head on her shoulders. I hate necks."
There doesn't appear to be a 'transaction timeout' mechanism for the transaction that wraps a PL/pgSQL function. So if the code hangs, it would appear that the transaction will as well unless statement_timeout is set. Is there a better way to prevent a transaction hang without globally limiting a statement timeout?
On Jan 14, 2009, at 9:41 AM, Wright, George wrote: > If a function in PL/pgSQL is wrapped by a transaction, does that > same single transaction encompass other functions defined elsewhere > that this function calls? Yes, they all share the same transaction scope. And PL/pgSQL functions cannot change the transaction state (i.e. call commit or start a new transaction). John DeSoi, Ph.D.
On Jan 14, 2009, at 9:41 AM, Wright, George wrote: > If a function in PL/pgSQL is wrapped by a transaction, does that > same single transaction encompass other functions defined elsewhere > that this function calls? Yes, they all share the same transaction scope. And PL/pgSQL functions cannot change the transaction state (i.e. call commit or start a new transaction). John DeSoi, Ph.D. ----------- Then if there is a large amount of code all wrapped by a single transaction and it hangs somewhere in the code, what technique can be used to abort the transaction? (It's not really a deadlock) Thanks.
On Jan 15, 2009, at 8:43 AM, Wright, George wrote: > Then if there is a large amount of code all wrapped by a single > transaction and it hangs somewhere in the code, what technique can be > used to abort the transaction? (It's not really a deadlock) I believe there is a statement timeout setting where an automatic abort could be executed. If you want to abort a running query manually see pg_cancel_backend: http://www.postgresql.org/docs/8.3/interactive/functions-admin.html John DeSoi, Ph.D.