Skip site navigation (1) Skip section navigation (2)

Re: Transaction vs. Savepoints

From: "Phillip Smith" <phillip(dot)smith(at)weatherbeeta(dot)com(dot)au>
To: "'Andreas'" <maps(dot)on(at)gmx(dot)net>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: Transaction vs. Savepoints
Date: 2007-02-09 05:47:12
Message-ID: 002d01c74c0d$beef7b40$9b0014ac@wbaus090 (view raw or flat)
Thread:
Lists: pgsql-novice
You know you can start and commit multiple transactions in one
connection...?

<open connection to pg daemon; say when user logs on at 9.00am>
BEGIN;
<transaction 1 here>
COMMIT;
BEGIN;
<transaction 2 here>
<save point 1>
<save point 2>
COMMIT;
BEGIN;
<transaction 3 here>
<save point 1>
COMMIT;
<close connection to pg daemon; say when user logs off at 5.00pm>

Just as an example...
-p

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Andreas
Sent: Friday, 9 February 2007 16:05
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] Transaction vs. Savepoints

Hi,

I'm a bit confused about the handling of transactions and savepoints 
withing reallife applications.
It's theoretically all pretty neat and I understand what's the point of 
transactions in general.

As far as I know, PG handles 1 transaction per connection an several 
savepoints boxed within.
E.G.   T1 ( Sp1, Sp2, Sp3 )
I could commit Sp1, rollback Sp2 and commit Sp3 in a session.
If I commit T1 all those actions get permanently written to to DB 
whereas if I rollback T1 all committed savepoints within get rolled 
back, too.

Suppose an application that runs all the 8 workhours. It handles 
customers and goods/services.

I obviously can't start a global transaction with the application 
because in the worst case something bad happens at the end of a workday 
and T1 can't be committed so all the work of this day would be lost.

So I have to manage smaller work-packages.
Like T1 (open a customer -- change his adress info -- write it back -- 
commit T1)

He might have several adresses so I'd show them in a list and provide a 
dialoge that opens to edit single adresses.
T1 (open a customer -- Sp1 (change adress 3 -- write it back -- commit 
Sp1) -- (Sp2 add a phone note -- commit Sp2) -- commit T1)

What if before T1 gets committed the front-end-application crashes.
T1 gets rolled back and erases the changes of Sp1 and Sp2 even though a 
user would expect them to be safe since those 2 dialoges might have been 
closed 50 minutes ago before he went to lunch.
He would be a wee bit unhappy to find them lost, too.

Now suppose you open a customer-form F1 and another form F2 that is 
semantically independent like some infos about an item in your warehouse 
or some color-config-dialog.
Both dialogs would try to start a transaction but the second one can't.
If I somehow keep track of running transactions and safepoint within the 
application I could let the second dialog use a safepoint instead of a 
real transaction.
What if F2 gets closed OK (committed) but the first dialog F1 that got 
the transaction gets rolled back?

Using separate connections everywhere where a bit of the application 
accesses the DB isn't desirable either because initiating connections is 
way slower than using an allready established one.
I actually wondered if I can get away with a single connection that gets 
build up within the start of the application.

How do you go about those problems ?


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

In response to

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2007-02-09 05:58:44
Subject: Re: Transaction vs. Savepoints
Previous:From: AndreasDate: 2007-02-09 05:04:59
Subject: Transaction vs. Savepoints

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group