Thread

1. Nested transaction - I am a bank ??

   Thapliyal, Deepak <dthapliyal@soe.sony.com> — 2004-01-13T17:47:49Z

   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
   
   

2. Re: Nested transaction - I am a bank ??

   Joshua D. Drake <jd@commandprompt.com> — 2004-01-13T18:16:36Z

   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
   
   
   

3. Re: Nested transaction - I am a bank ??

   scott.marlowe <scott.marlowe@ihs.com> — 2004-01-13T18:34:20Z

   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?
   
   
   

4. Re: Nested transaction - I am a bank ??

   Richard Huxton <dev@archonet.com> — 2004-01-13T18:48:23Z

   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
   
   

5. Re: Nested transaction - I am a bank ??

   Robert Treat <xzilla@users.sourceforge.net> — 2004-01-13T21:41:11Z

   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
   
   
   

6. Re: Nested transaction - I am a bank ??

   Nigel J. Andrews <nandrews@investsystems.co.uk> — 2004-01-13T23:25:31Z

   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
   
   
   

7. Re: Nested transaction - I am a bank ??

   David Garamond <lists@zara.6.isreserved.com> — 2004-01-15T06:04:25Z

   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