will I need nested transactions ?

[Andreas]

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

[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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[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 ma*******@postgresql.org)

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

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