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

Transaction vs. Savepoints

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Transaction vs. Savepoints
Date: 2007-02-09 05:04:59
Message-ID: 45CC00FB.9090704@gmx.net (view raw or flat)
Thread:
Lists: pgsql-novice
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 ?


Responses

pgsql-novice by date

Next:From: Phillip SmithDate: 2007-02-09 05:47:12
Subject: Re: Transaction vs. Savepoints
Previous:From: Tom LaneDate: 2007-02-08 19:10:46
Subject: Re: relation 12345 is still open

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