From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Juan Garcés Bustamante <jgarces(at)futuroprofesional(dot)cl> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transacciones Anidadas |
Date: | 2005-12-16 20:21:52 |
Message-ID: | 20051216202152.GA7704@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-es-ayuda pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2005-12-16 20:22:34 | Re: [GENERAL] Transacciones Anidadas |
Previous Message | Alvaro Herrera | 2005-12-16 20:19:24 | Re: Duda sobre tipo de datos Enteros vs. Caracteres |
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2005-12-16 20:22:34 | Re: [GENERAL] Transacciones Anidadas |
Previous Message | Madison Kelly | 2005-12-16 20:19:44 | Re: Getting a DB password to work without editing pg_hba.conf, |