Re: BUG #2507: Problem using two-phase commit

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(dot)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

> N. Ramirez escribió:
>
>> 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?
>
> 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)
>
>
>> idem for use of dblink
>
> Not sure what you mean here.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.