Re: [Plproxy-users] Two-phase commmit, plpgsql and plproxy

On 2/11/09, Igor Katson <descentspb(at)gmail(dot)com> wrote:
> I would like to call several plproxy functions one after another (which
> will call plpgsql functions in different target partitions), and in case
> one of them fails, i want to roll back changes in every one.
>
> That is exactly how two-phase-commit (2PC) should work.
>
> As far as I understand, the postgres' group of statements like PREPARE
> TRANSACTION can do this job. But when trying to insert a 'PREPARE
> TRANSACTION' statement into a PL/Pgsql function I get an error:
>
> ERROR: XX000: SPI_execute_plan failed executing query "PREPARE
> TRANSACTION '2pctest'": SPI_ERROR_TRANSACTION
>
> Is there a way to achieve the needed behaviour with two-phase commmit,
> plpgsql and plproxy ?

Simplest would be to create new plpgsql function on target partition
that contains the combined functionality or simply calls the needed
functions. So it would still be single plproxy call.

About 2PC: there are some plans to implement 2PC in plproxy on
experimental basis for RUN ON ALL functions, for single function-call.
But as a experimental feature - we have no idea if it will actually work
or how bad the speed hit will be.

And there is even more hypothetical idea to implement it for
multi-call transactions via some commit hook:

begin;
select plproxy.start_2pc();
select some_plproxy_func1();
select some_plproxy_func2();
commit; / rollback;

but this is more uncertain, we have no idea if we have such hooks
from Postgres. Also note there are no plans to get it 2PC with the
local surrounding TX. This seems like overkill...

Anyway - ATM I'm still some time inactive on plproxy front.
If someone wants to take stab on experimenting with such stuff,
please go ahead...

--
marko