Thread: will I need nested transactions ?

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

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

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 majordomo@postgresql.org)
>