On Tuesday 13 January 2004 17:47, Thapliyal, Deepak wrote: > Hi, > > Assume I have a bank app.. When customer withdraws $10 from his accouint I > have to do following > --> update account_summary table [subtract $10 from his account] > --> update account detail_table [with other transaction details] > > Requirement: > either both transactions should succeed or both transactions should > be rolled back in case of failure.
In database terms, the two operations together are one transaction. You do something like: BEGIN; INSERT INTO detail (acct_num,trans_type,trans_time,notes) VALUES (1,'CASHOUT',now(),'blah'); UPDATE account_summary SET amount=amount-10 WHERE acct_num = 1; COMMIT; Now, if one (or both) of those were written as a function, that function's effects would still be bound by the transaction. All operations(*) take place within a transaction in PG, either explicitly as above or implicitly with one per statement. What you can't do is have a function that does something like: LOOP 1..10 BEGIN; -- do something ten times, each time in its own transaction COMMIT; END LOOP (*) except for a couple of bits like vacuum, truncate(?) and similar. -- Richard Huxton Archonet Ltd ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
