Re: [Plproxy-users] Plproxy functions inside transactions and Pl/pgsql exception handling

Hannu Krosing wrote:
> On Fri, 2009-01-30 at 14:09 +0300, Igor Katson wrote:
>
>> As far as I understand, it is a known problem of using plproxy, that it
>> cannot be rolled back if used inside transactions. But I need something
>> similar to this functionality.
>>
>> I have some data, that is duplicated across the DB partitions, and to be
>> exact, there is i.e. a plproxy-partitioned DB, containing users. For the
>> list of user's friends to be in the same DB, where the user himself is,
>> I need to duplicate the 'user-friend' data to the partition of the user,
>> and the partition of the friend.
>>
>> So I need to call SEVERAL plproxy functions inside a transaction.
>>
>> Well, I understand that plproxy does not support well that kind of usage
>> (will it?). But I need to create some mechanism to do a check and a
>> rollback (if neccessary) manually inside the PL/pgsql function that does
>> this job.
>>
>> How can I do that, if, afaik, PL/pgsql does not support exception handling?
>>
>
> To do so, you would need two phase commit (2PC) which is usually a pita
> to maintain (needs a separate transaction manager) and also it does not
> scale.
>
> As the whole point on pl/proxy is scaling, you want to avoid 2PC
>
> The way to avoid 2PC is to design your system so that you can use async
> replication for maintaining "secondary" data / read-only copies.
>
> The way to do it in a scalable fashion is to have one
> pl/proxy-partitioned function to update users friend list on that users
> partition and then use pgQ (from SkyTools) to capture changes and then
> apply them to partitions of each friend.
>
> This mean that there will be a delay between updating users friend list
> and the "reverse" friend-with list of each friend, which must be
> considered in the design. But it is easy to do on most cases and doable
> in 100% of cases.
>
> Typical pgQ delay can be below one second, even a few tenths of second
> is doable.
>
>
Thanks for the great answer.

Concerning plpgsql and exceptions: btw, I was not right, and there IS
exception handling in plpgsql, but implementing it is ok only somewhere,
and in the other cases it seems like hell, considering this problem
(doing a fully manual "rollback" in the remote DB), e.g. when in the
first plproxy func something is deleted, and the second func gives out
an error, I must manually get the data to to be deleted in the 1st, and
insert it back manually in case of failure of the 2nd. I don' like this
method.

What I really like is 2-phase commit idea, that you described. When
reading about it in Wikipedia
(http://en.wikipedia.org/wiki/Two-phase_commit_protocol) it seems the
exactly right thing, that I need, but when scrolling the Postgres manual
(prepare transaction, commit prepared and rollback prepared) it does not.

Is there a way to deploy 2PC, as described in Wiki, with postgres? I
mean, that all the partitions will do a rollback, if one of them says
'abort' ?

P.S. I can't understand, why it can ruin the whole plproxy idea in my
case, because I always need only 2 partitions acting in a 2PC
transaction — the user one, and the friend one.

Thanks in advance.