Thread: Plproxy functions inside transactions and Pl/pgsql exception handling
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?
Re: [Plproxy-users] Plproxy functions inside transactions and Pl/pgsql exception handling
From
Hannu Krosing
Date:
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
Re: [Plproxy-users] Plproxy functions inside transactions and Pl/pgsql exception handling
From
Igor Katson
Date:
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.