Re: Transacciones Anidadas

Juan Garcés Bustamante wrote:
> Hola
>
> Estoy trabajando con Postgres 8.0.3 en Ubuntu.
>
> Necesito realizar transacciones anidadas, pero no logro que se aborten
> transacciones intermedias al abortarse una superior.
The main language spoken here is english. If you ask your questions in english,
you'll have a much higher chance of someone answering. If you'd rather post
in spanish, you could subscribe to a spanish postgresql list (I believe there is one).

> Ejemplo:
>
> BEGIN WORK;
> INSERT INTO mitabla VALUES (1);
> BEGIN TRANSACTION;
> INSERT INTO mitabla VALUES (2);
> INSERT INTO mitabla VALUES (3);
> COMMIT TRANSACTION;
> INSERT INTO mitabla VALUES (4);
> ROLLBACK WORK;
>
> El "ROLLBACK WORK" no aborta la TRANSACTION.
You cannot nest transactions that way. Instead, start the outer transaction with
"begin", and the inner transaction with "savepoint <name>". You can then rollback
to a savepoint with "rollback to <name>", and rollback the whole transaction
with just "rollback". Instead of commiting a savepoint, you release it.
(With "release <name>").

Your example should therefor look like this:
begin;
insert into mitabla values (1) ;
savepoint sp1 ;
insert into mitablea values (2) ;
insert into mitabla values (3) ;
release sp1 ;
insert into mitabla values(4) ;
rollback;

> Resultado de la consulta:
>
> mitabla
> ========
> 1
> 2
> 3
> (3 rows)
What probably happened is that your second "begin" was ignored by postgres -
with a warning like "Warning: Already inside transaction" i'd guess.
Your commit then comitted everything from the first begin on, therefor
comitting values 1,2,3. Additionally, I guess that you have autocommit set
to "off". This causes psql to start a new transaction for the value "4", because
in autocommit=off mode psql will not let you execute commands outside a transaction.
Your final rollback then rolled back that transaction, removing 4 from the table,
but leaving 1,2,3 in place.

> Resultado esperado:
>
> mitabla
> ========
>
> (0 rows)
Try my corrected example, it should report "0 rows" ;-)

greetings, Florian Pflug