On Tue, 13 Jan 2004, 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.
>
> Question:
> if my first update succeeds and second fails (say due to space
> errors .. I have inconsistancy ..
>
> Per the thread below stored procedures/functions cannot have commits. I
> assume that means that they will be implicitly commited ??
>
> How do I approach this simple requirment using psql ?
I know others have answered this but I don't recall a specific answer to the
psql question. So...simple:
mydb=> begin;
mydb=> update [whatever];
mydb=> update [whatever];
mydb=> [whatever]
mydb=> [...etc.]
mydb=> commit;
As has been mentioned, put all the operations in a function you could replace
all those lines with one:
select myfunc();
which will either complete and commit (if autocommit is on) or not commit. If
autocommit is off then it will still need the commit statement in order to
commit and also a rollback (or may be a commit works as well I can't
remember) in order to clear the aborted transaction in case of error (so that
more statements can be issued).
--
Nigel Andrews
