Thread: Nested transaction - I am a bank ??

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 ?

Thx
Deep

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Richard Huxton
Sent: Tuesday, January 13, 2004 4:32 AM
To: Anton.Nikiforov@loteco.ru
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Parse error help needed...


On Tuesday 13 January 2004 12:01, Anton.Nikiforov@loteco.ru wrote:
> RH> Remove the "commit" line - functions cannot define their own
> transactions RH> anyway.
> Do you know if it will be solved sometime? Or this is architecture
> dependend problem? I mean that transactions are rulez and very helpful
> rulez when working with large databases.

Nested transactions are on the todo list, but I don't know when they will
appear.

--
  Richard Huxton
  Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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 ..
>
>

Not if you run the queries as a single transaction.


>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 ?
>
>Thx
>Deep
>
>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Richard Huxton
>Sent: Tuesday, January 13, 2004 4:32 AM
>To: Anton.Nikiforov@loteco.ru
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Parse error help needed...
>
>
>On Tuesday 13 January 2004 12:01, Anton.Nikiforov@loteco.ru wrote:
>
>
>>RH> Remove the "commit" line - functions cannot define their own
>>transactions RH> anyway.
>>Do you know if it will be solved sometime? Or this is architecture
>>dependend problem? I mean that transactions are rulez and very helpful
>>rulez when working with large databases.
>>
>>
>
>Nested transactions are on the todo list, but I don't know when they will
>appear.
>
>
>


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

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

On Tue, 13 Jan 2004, Joshua D. Drake wrote:

> 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 ..
> >
> >
>
> Not if you run the queries as a single transaction.
>
>
> >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 ?

Joshua, aren't functions run within their own transactions if they don't
inherit one?  Wouldn't that take care of this part as well?

On Tue, 2004-01-13 at 13:34, scott.marlowe wrote:
> On Tue, 13 Jan 2004, Joshua D. Drake wrote:
>
> > 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 ..
> > >
> > >
> >
> > Not if you run the queries as a single transaction.
> >
> >
> > >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 ?
>
> Joshua, aren't functions run within their own transactions if they don't
> inherit one?  Wouldn't that take care of this part as well?
>

sort of... in order to call a function you have to do "select foo()"
which creates an implicit transaction block based on the select
statement that commits when the "select statement" finishes and
everything within foo will be treated as a single transaction.

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

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

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.

Both actions you mentioned are not [or are unlikely to be implemented
as] two separate transactions, but a single transaction (and thus the
subject "nested transaction" has nothing to do with this.

Nested transaction are usually used in complex operations.

Save points can be used to implement nested transaction.

Since we're using a bank as example, consider a bank with 1 million
accounts. At the end of the month, it needs to calculate and post
interest for each account. The whole operation takes 10 hours. If we use
a single transaction for this, then if the machine/database crashes, the
whole unfinished transaction will be rolled back. If the db is back up,
but then fails again in the middle of this giant transaction, it will be
rolled back again. And perhaps again... and again... and thus it will
never finishes.

With save points (and nested transactions) we can save in the middle of
transaction and later rolls back to the last save point instead of
beginning the transaction all over.

--
dave