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
Attachment:
smime.p7s
Description: S/MIME Cryptographic Signature
