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

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.

--
------------------------------------------
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training