Thread: Two-phase commmit, plpgsql and plproxy

Two-phase commmit, plpgsql and plproxy

From
Igor Katson
Date:
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 ?

Re: Two-phase commmit, plpgsql and plproxy

From
Jeff Davis
Date:
On Wed, 2009-02-11 at 12:43 +0300, Igor Katson wrote:
> ERROR:  XX000: SPI_execute_plan failed executing query "PREPARE
> TRANSACTION '2pctest'": SPI_ERROR_TRANSACTION

It's probably treating the word PREPARE specially. You can avoid this
problem by using EXECUTE and specifying the command as a string.

> Is there a way to achieve the needed behaviour with two-phase commmit,
> plpgsql and plproxy ?

You can't begin or end a transaction inside a function. If that was
allowed, what would the function do after the transaction was prepared?

I think you need to do PREPARE TRANSACTION separately, somehow. You
might need to modify plproxy to do that the way you want.

Regards,
    Jeff Davis


Re: Two-phase commmit, plpgsql and plproxy

From
Igor Katson
Date:
Jeff Davis wrote:
> On Wed, 2009-02-11 at 12:43 +0300, Igor Katson wrote:
>
>> ERROR:  XX000: SPI_execute_plan failed executing query "PREPARE
>> TRANSACTION '2pctest'": SPI_ERROR_TRANSACTION
>>
>
> It's probably treating the word PREPARE specially. You can avoid this
> problem by using EXECUTE and specifying the command as a string.
>
>
Yes, doing an EXECUTE helped to create the function.
>> Is there a way to achieve the needed behaviour with two-phase commmit,
>> plpgsql and plproxy ?
>>
>
> You can't begin or end a transaction inside a function. If that was
> allowed, what would the function do after the transaction was prepared?
>
>
> I think you need to do PREPARE TRANSACTION separately, somehow. You
> might need to modify plproxy to do that the way you want.
>
Thanks, Jeff. That's not good news, cause I am not able to do that.

The postgres manual says, that
" The intended usage of the feature is that a prepared transaction will
normally be committed or rolled back as soon as an external transaction
manager has verified that other databases are also prepared to commit. "

So does this "external transaction manager" exist? I am not clear about
what it is.


Re: Two-phase commmit, plpgsql and plproxy

From
Jeff Davis
Date:
On Thu, 2009-02-12 at 00:57 +0300, Igor Katson wrote:
> Thanks, Jeff. That's not good news, cause I am not able to do that.

There may be some creative solution, but I don't know plproxy well
enough to suggest one.

> The postgres manual says, that
> " The intended usage of the feature is that a prepared transaction will
> normally be committed or rolled back as soon as an external transaction
> manager has verified that other databases are also prepared to commit. "
>
> So does this "external transaction manager" exist? I am not clear about
> what it is.

PostgreSQL does not provide a transaction manager.

When you are dealing with multiple databases, the transaction manager
needs to make decisions like "this transaction failed on one node,
therefore we need to roll all the other transactions back".

I think you are basically trying to make plproxy into the transaction
manager. You might get some better suggestions from people who know
plproxy well.

Regards,
    Jeff Davis


Re: Two-phase commmit, plpgsql and plproxy

From
Igor Katson
Date:
Jeff Davis wrote:
> On Thu, 2009-02-12 at 00:57 +0300, Igor Katson wrote:
>
>> Thanks, Jeff. That's not good news, cause I am not able to do that.
>>
>
> There may be some creative solution, but I don't know plproxy well
> enough to suggest one.
>
>
>> The postgres manual says, that
>> " The intended usage of the feature is that a prepared transaction will
>> normally be committed or rolled back as soon as an external transaction
>> manager has verified that other databases are also prepared to commit. "
>>
>> So does this "external transaction manager" exist? I am not clear about
>> what it is.
>>
>
> PostgreSQL does not provide a transaction manager.
>
> When you are dealing with multiple databases, the transaction manager
> needs to make decisions like "this transaction failed on one node,
> therefore we need to roll all the other transactions back".
>
> I think you are basically trying to make plproxy into the transaction
> manager. You might get some better suggestions from people who know
> plproxy well.
>
Thanks, Jeff. Googling smth like "postgresql transaction manager" does
not give any nice result. It seems, that the one just does not exist.
Hope, plproxy developers will answer smth. considering this problem.

Re: Two-phase commmit, plpgsql and plproxy

From
Joshua Tolley
Date:
On Thu, Feb 12, 2009 at 02:17:03AM +0300, Igor Katson wrote:
>>
>> PostgreSQL does not provide a transaction manager.
>>
>> When you are dealing with multiple databases, the transaction manager
>> needs to make decisions like "this transaction failed on one node,
>> therefore we need to roll all the other transactions back".
>>
>> I think you are basically trying to make plproxy into the transaction
>> manager. You might get some better suggestions from people who know
>> plproxy well.
>>
> Thanks, Jeff. Googling smth like "postgresql transaction manager" does
> not give any nice result. It seems, that the one just does not exist.
> Hope, plproxy developers will answer smth. considering this problem.

There are other transaction managers available (mainly Java-based,
AFAIK). Generally a transaction manager tries to be able to interface
with all kinds of different transaction-aware services (databases,
transactional messaging systems, etc.), because generally someone
wanting to coordinate transactions using 2PC wants to coordinate all
kinds of different services;  a PostgreSQL-specific one would probably
be of extremely limited applicability, especially compared to the work
required to get the transaction manager's behavior provably correct.
Often a middleware application (such as an application server) will
provide a transaction manager; standalone ones exist as well. I've heard
good things of, but never used, Bitronix, for example.

- Josh / eggyknap

Attachment

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

From
Marko Kreen
Date:
On 2/11/09, Igor Katson <descentspb@gmail.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

Re: Two-phase commmit, plpgsql and plproxy

From
Mark Roberts
Date:
On Thu, 2009-02-12 at 02:17 +0300, Igor Katson wrote:
>
> Thanks, Jeff. Googling smth like "postgresql transaction manager"
> does
> not give any nice result. It seems, that the one just does not exist.
> Hope, plproxy developers will answer smth. considering this problem.

I wrote my own "transaction manager" and tied it into the application
that was triggering the events in the first place.  It worked remarkably
well.  Greatly simplified by removing application knowledge, it looks
like this:


sub commit_prepared_xacts {
    my ($master_transaction_id, @databases) = @_;
    for my $database (@databases) {
        if (!prepared_xact_exists($master_transaction_id, $database)) {
            rollback_all_xacts($master_transaction_id, @databases);
        }
    }
}

sub prepared_xact_exists {
    my ($master_transaction_id, $database) = @_;
    return do_query($database, qq|
        select gid pg_prepared_xacts from where gid = ?
    |, $master_transaction_id);
}

sub rollback_all_xacts
{
    my ($master_transaction_id, @databases) = @_;
    for my $database (@databases) {
        do_query($database, qq|
            rollback prepared $master_transaction_id
        |);
    }
}



-Mark