Re: [Plproxy-users] Plproxy functions inside transactions and Pl/pgsql exception handling - Mailing list pgsql-general
From | Igor Katson |
---|---|
Subject | Re: [Plproxy-users] Plproxy functions inside transactions and Pl/pgsql exception handling |
Date | |
Msg-id | 49832ADF.2010009@gmail.com Whole thread Raw |
In response to | Re: [Plproxy-users] Plproxy functions inside transactions and Pl/pgsql exception handling (Hannu Krosing <hannu@2ndQuadrant.com>) |
List | pgsql-general |
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.
pgsql-general by date: