Thread: Two-phase commmit, plpgsql and plproxy
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 ?
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
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.
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
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.
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
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
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