Thread: Transacciones Anidadas
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.
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.
Resultado de la consulta:
mitabla
========
1
2
3
(3 rows)
Resultado esperado:
mitabla
========
(0 rows)
Alguna idea??
Gracias.
On Fri, Dec 16, 2005 at 03:23:07PM -0400, Juan Garcs Bustamante wrote: > Estoy trabajando con Postgres 8.0.3 en Ubuntu. Jaime Casanova has already mentioned this list is in English and that questions in Spanish should go to pgsql-es-ayuda. > Necesito realizar transacciones anidadas, pero no logro que se aborten > transacciones intermedias al abortarse una superior. PostgreSQL 8.0 and later have savepoints; they don't support nesting transactions by using multiple BEGIN statements. http://www.postgresql.org/docs/8.0/interactive/tutorial-transactions.html http://www.postgresql.org/docs/8.0/interactive/sql-savepoint.html > 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. It does abort a transaction, but not the one you're thinking of. The second BEGIN has no effect because you're already in a transaction: test=> BEGIN WORK; BEGIN test=> INSERT INTO mitabla VALUES (1); INSERT 0 1 test=> BEGIN TRANSACTION; WARNING: there is already a transaction in progress BEGIN test=> INSERT INTO mitabla VALUES (2); INSERT 0 1 test=> INSERT INTO mitabla VALUES (3); INSERT 0 1 test=> COMMIT TRANSACTION; COMMIT You've committed the transaction, so the three inserted records are in the table, as your query shows: > Resultado de la consulta: > > mitabla > ======== > 1 > 2 > 3 > (3 rows) You didn't explicitly begin another transaction but the fourth insert does appear to have been rolled back, so I'd guess you're using a client that has autocommit disabled. After you committed the first three inserts another transaction was started automatically, and that's what was rolled back. -- Michael Fuhr
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
On Fri, Dec 16, 2005 at 03:23:07PM -0400, Juan Garcés Bustamante wrote: > Hola Guten Abend ! > Estoy trabajando con Postgres 8.0.3 en Ubuntu. Hm, ich benutze Debian, mit PostgreSQL 7.4 in einem Cluster. Wie das bei Ubuntu so funktioniert, weiß ich nicht so richtig. > Necesito realizar transacciones anidadas, pero no logro que se aborten > transacciones intermedias al abortarse una superior. Ich glaube Du mußt Savepoints benutzen, wenn Du eine Zwischentransaktion innerhalb einer anderen starten willst. Es natürlich klar, daß innerhalb *einer* Transaktion jeder Fehler die gesamte Verarbeitung abbricht ! > 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. Das kann ich mir nicht vorstellen. Bist Du sicher, daß BEGIN TRANSACTION die richtige Syntax ist für das, was Du machen willst ? > Resultado de la consulta: > > mitabla > ======== > 1 > 2 > 3 > (3 rows) Ach so, klar, mE können BEGINs geschachtelt werden, ohne daß ein Problem auftritt. Allerdings beendet dann COMMIT alle begonnenen Transaktionen auf einmal. Da das INSERT ... 4 erst nach dem COMMIT, aber vor dem ROLLBACK kommt, wird es richtig von dem ROLLBACK erfasst und erscheint nicht in der Tabelle. > Resultado esperado: > > mitabla > ======== > > (0 rows) Nee, nee. Deine Erwartung an das Ergebnis ist falsch. > Alguna idea?? Naja, siehe oben :-) > Gracias. Kein Problem. Gern wieder. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346