Re: Two-phase commmit, plpgsql and plproxy

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