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