Обсуждение: 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

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