Re: [GENERAL] will I need nested transactions ?

[Ben]

If you want the model where if any updates fail, all should be rolled 
back, then you don't need nested transactions, just multiple aborts:

begin;
update d; if error abort;
update c; if error abort;
...
commit;

On Mon, 17 May 2004, Andreas wrote:

> Hi,
> 
> will I need "nested transactions" which - as I read - aren't 
> implemented, yet ?
> 
> I have some objects that rely on each other.
> Each has a status like proposal, working, canceled.
> 
> table-A   <---   table-B   <---   table-C   <---   table-D
> 
> Those are  (1, OO)  relationships,
> A status change above gets cascaded down but not upwards.
> If I try to cancel a table-A-record every "lower" record in B, C, D 
> should be canceled, too, when the transaction is committed.
> Since it is possible, that I cancel e.g. a table B object only its 
> children should get updated but not table-A.
> 
> I thought somthing along this to cancel a type B object:
> 
> BEGIN
> BEGIN
> BEGIN
>UPDATE table-D
>  END
>  if no error UPDATE table-C
> END
> if no error UPDATE table-B
> END
> 
> Does this make sense and will it provide the necesary protection ?
> 
> BTW the client is Access 2000 via ODBC talking to an PostgreSQL 7.4.2 on 
> Linux.
> 
> 
> Regards
> Andreas
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] will I need nested transactions ?

[Richard Huxton]

Andreas wrote:
Hi,
will I need "nested transactions" which - as I read - aren't 
implemented, yet ?

I have some objects that rely on each other.
Each has a status like proposal, working, canceled.
table-A   <---   table-B   <---   table-C   <---   table-D
Those are  (1, OO)  relationships,
A status change above gets cascaded down but not upwards.
If I try to cancel a table-A-record every "lower" record in B, C, D 
should be canceled, too, when the transaction is committed.
Since it is possible, that I cancel e.g. a table B object only its 
children should get updated but not table-A.

I thought somthing along this to cancel a type B object:
BEGIN
   BEGIN
   BEGIN
  UPDATE table-D
END
if no error UPDATE table-C
   END
   if no error UPDATE table-B
END
Does this make sense and will it provide the necesary protection ?
I don't think it needs to be that complicated. Just wrap the whole lot 
in one transaction and it will either all work or all fail:

BEGIN
  UPDATE table_d ...
  UPDATE table_c ...
  UPDATE table_d ...
COMMIT;
--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly