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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
