Thread: two concurrency questions
I searched the documentation and can't find the specific answers to these questions. 1: Are user defined functions (eg pgplsql) atomic? I.e. if my function updates a bunch of rows and one update fails for one reason or another does it automatically roll-back to the state before the function was called? 2: If an update causes a trigger and the trigger updates a row and the trigger fails, what happens? Is there a way to atomize the whole process? Thanks, Jeff Patterson jpat@mpip.org The Melanoma Patients' Information Page http://www.mpip.org
Jeff Patterson <jpat@mpip.org> writes: > I searched the documentation and can't find the specific answers to > these questions. > > 1: Are user defined functions (eg pgplsql) atomic? I.e. if my function > updates a bunch of rows and one update fails for one reason or another > does it automatically roll-back to the state before the function was > called? Yes, because everything in PG is done inside a transaction, either implicitly or explicitly. If you have autocommit on and don't use BEGIN/COMMIT, each statement is its own transaction. Since a function is always called from a statement, functions are atomic. > 2: If an update causes a trigger and the trigger updates a row and the > trigger fails, what happens? Is there a way to atomize the whole > process? See the answer to #1. -Doug
On Mon, 2003-03-03 at 21:20, Jeff Patterson wrote: > 1: Are user defined functions (eg pgplsql) atomic? I.e. if my function > updates a bunch of rows and one update fails for one reason or another > does it automatically roll-back to the state before the function was called? > > 2: If an update causes a trigger and the trigger updates a row and the > trigger fails, what happens? Is there a way to atomize the whole process? Both operations occur inside a transaction (if you don't explicitly start a transaction, PostgreSQL does it for you), so they should both be atomic: when an error occurs, the current transaction is aborted. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC