Re: Transacciones Anidadas

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