The following bug has been logged online:

Bug reference: 2507
Logged by: N. Ramirez
Email address: noramirez (AT) speedy (DOT) com.ar
PostgreSQL version: 8.1.4
Operating system: Linux Suse 9
Description: Problem using two-phase commit
Details:

I do not have an operation as it must be when use the functions to do
2-phase commit

Example create table prueba (a int, b int);
begin;
PREPARE TRANSACTION 'aaaa';
insert into prueba values (1,2);
ROLLBACK PREPARED 'aaaa'; select * from prueba
a b
-----------------------------
1 2
because?
it did not do rollback?
as it is used the method of 2-phase commit?
idem for use of dblink

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

N. Ramirez escribió:

It did rollback, but you put the insert outside the prepared
transaction, so it was committed independently. Try this:

alvherre=# create table prueba (a int, b int);
CREATE TABLE
alvherre=# begin;
BEGIN
alvherre=# insert into prueba values (1, 2);
INSERT 0 1
alvherre=# prepare transaction 'aaaa';
PREPARE TRANSACTION
alvherre=# select * from prueba;
a | b
---+---
(0 filas)

alvherre=# rollback prepared 'aaaa';
ROLLBACK PREPARED
alvherre=# select * from prueba;
a | b
---+---
(0 filas)

alvherre=# begin;
BEGIN
alvherre=# insert into prueba values (1, 2);
INSERT 0 1
alvherre=# prepare transaction 'bbb';
PREPARE TRANSACTION
alvherre=# select * from prueba;
a | b
---+---
(0 filas)

alvherre=# commit prepared 'bbb';
COMMIT PREPARED
alvherre=# select * from prueba;
a | b
---+---
1 | 2
(1 fila)


Not sure what you mean here.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Alvaro:

I mean that as two-phase commit in the case is used of wanting to
update tie tables with dblink, that is to say, that do not esten in my
trasaccional scheme.
in addition, all operation that becomes after doing "prepare transaction"
which it is of update
of data base is not included in the transaction, this is thus?

In your example I apply a scheme with remote tables now.
select dblink_connect('Remota','dbname=postgres');
begin;
select dblink_exec('Remota','insert into prueba values(1,2)');
prepare transaction 'aaaa';
select * from dblink('Remota','select * from prueba') as (codigo integer,
descrip integer)
rollback prepared 'aaaa';
select * from dblink('Remota','select * from prueba') as (codigo integer,
descrip integer)

and it does not work, that is to say, I cannot have a transaction that
groups the local
operations to the base and another data base? Can be done thus something in
postgres?
something of the style

select dblink_connect('Remota','dbname=postgres');
begin;
insert into pruebalocal values (1,2);
select dblink_exec('Remota','insert into prueba values(1,2)');
rollback;
and that has not been hit in any of the 2 tables? neither the remote
premises
nor or the single one can making use "by hand" of the transactions?

Thank you very much

Estimado Alvaro:

Quiero decir que como se usa two-phase commit en el caso de querer
actualizar tablas vinculadas con dblink,
es decir que no esten en mi esquema trasaccional.
ademas, toda operacion que se hace despues de hacer un "prepare transaction"
que sea de actualizacion
de base de datos no se incluye en la transaccion, esto es asi?
En tu ejemplo aplico ahora un esquema con tablas remotas.

select dblink_connect('Remota','dbname=postgres');
begin;
select dblink_exec('Remota','insert into prueba values(1,2)');
prepare transaction 'aaaa';
select * from dblink('Remota','select * from prueba') as (codigo integer,
descrip integer)
rollback prepared 'aaaa';
select * from dblink('Remota','select * from prueba') as (codigo integer,
descrip integer)

y no funciona, es decir no estoy pudiendo tener una transaccion que agrupe
las operaciones locales a la base
y a otra base de datos?
Se puede hacer algo asi en postgres?
algo del estilo

select dblink_connect('Remota','dbname=postgres');
begin;
insert into pruebalocal values (1,2);
select dblink_exec('Remota','insert into prueba values(1,2)');
rollback

y que no se haya impactado en ninguna de las 2 tablas? ni la local ni la
remota o solo se puede haciendo uso
"a mano" de las transacciones?

Muchas gracias
----- Original Message -----
From: "Alvaro Herrera" <alvherre (AT) commandprompt (DOT) com>
To: "N. Ramirez" <noramirez (AT) speedy (DOT) com.ar>
Cc: <pgsql-bugs (AT) postgresql (DOT) org>
Sent: Saturday, July 01, 2006 11:54 AM
Subject: Re: [BUGS] BUG #2507: Problem using two-phase commit



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster