Re: Transactions involving multiple postgres foreign servers - Mailing list pgsql-hackers

<div dir="ltr"><p>Hi All,<p>Ashutosh proposed the feature 2PC for FDW for achieving atomic commits across multiple
foreignservers. <br /> If a transaction make changes to more than two foreign servers the current implementation in
postgres_fdwdoesn't make sure that either all of them commit or all of them rollback their changes. <br /><br /> We
(MasahikoSawada and me) reopen this thread and trying to contribute in it. <br /><br /> 2PC for FDW <br /> ============
<br/> The patch provides support for atomic commit for transactions involving foreign servers. when the transaction
makeschanges to foreign servers, <br /> either all the changes to all the foreign servers commit or rollback. <br /><br
/>The new patch 2PC for FDW include the following things: <br /> 1. The patch 0001 introduces a generic feature. All
kindsof FDW that support 2PC such as oracle_fdw, mysql_fdw, postgres_fdw etc. can involve in the transaction. <br /><br
/>Currentlywe can push some conditions down to shard nodes, especially in 9.6 the directly modify feature has <br
/>beenintroduced. But such a transaction modifying data on shard node is not executed surely. <br />Using 0002 patch,
thatmodify is executed with 2PC. It means that we almost can provide sharding solution using <br />multiple PostgreSQL
server(one parent node and several shared node). <br /><br />For multi master, we definitely need transaction manager
buttransaction manager probably can use this 2PC for FDW feature to manage distributed transaction. <br /><br /> 2.
0002patch makes postgres_fdw possible to use 2PC.<br /><p> 0002 patch makes postgres_fdw to use below APIs. These APIs
aregeneric features which can be used by all kinds of FDWs.<p>    a. Execute PREAPRE TRANSACTION and COMMIT/ABORT
PREAPREDinstead of COMMIT/ABORT on foreign server which supports 2PC. <br />    b. Manage information of foreign
preparedtransactions resolver <br /><p>Masahiko Sawada will post the patch. <br /><br /> Suggestions and comments are
helpfulto implement this feature.<br /><br /> Regards, <br /><br /> Vinayak Pokale </div><div class="gmail_extra"><br
/><divclass="gmail_quote">On Mon, Feb 1, 2016 at 11:14 PM, Alvaro Herrera <span dir="ltr"><<a
href="mailto:alvherre@2ndquadrant.com"target="_blank">alvherre@2ndquadrant.com</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">Alvaro Herrera
wrote:<br/> > Ashutosh Bapat wrote:<br /> ><br /> > > Here's updated patch. I didn't use version numbers in
filenames in my<br /> > > previous patches. I am starting from this onwards.<br /> ><br /> > Um, I tried
thispatch and it doesn't apply at all.  There's a large<br /> > number of conflicts.  Please update it and resubmit
tothe next<br /> > commitfest.<br /><br /></span>Also, please run "git show --check" of "git diff origin/master
--check"<br/> and fix the whitespace problems that it shows.  It's an easy thing but<br /> there's a lot of red squares
inmy screen.<br /><div class="HOEnZb"><div class="h5"><br /> --<br /> Álvaro Herrera                <a
href="http://www.2ndQuadrant.com/"rel="noreferrer" target="_blank">http://www.2ndQuadrant.com/</a><br /> PostgreSQL
Development,24x7 Support, Remote DBA, Training & Services<br /><br /><br /> --<br /> Sent via pgsql-hackers mailing
list(<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br /> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers" rel="noreferrer"
target="_blank">http://www.postgresql.org/<wbr/>mailpref/pgsql-hackers</a><br /></div></div></blockquote></div><br
/></div>