Re: Transaction vs. Savepoints - Mailing list 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@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Andreas
Sent: Friday, 9 February 2007 16:05
To: pgsql-novice@postgresql.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