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

<br /><br /> On Tue, Oct 4, 2016 at 1:26 PM, Ashutosh Bapat <<a
href="javascript:;">ashutosh.bapat@enterprisedb.com</a>>wrote:<br /> >>><br /> >>> Why always
rollbackany dangling transaction? There can be a case that<br /> >>> a foreign server has a dangling
transactionwhich needs to be<br /> >>> committed because the portions of that transaction on the other
shards<br/> >>> are committed.<br /> >><br /> >> Right, we can heuristically make a decision
whetherwe do COMMIT or<br /> >> ABORT on local server.<br /> >> For example, if COMMIT PREPARED succeeded
onat least one foreign<br /> >> server, the local server return OK to client and the other dangling<br />
>>transactions should be committed later.<br /> >> We can find out that we should do either commit or abort
thedangling<br /> >> transaction by checking CLOG.<br /> ><br /> > Heuristics can not become the default
behavior.A user should be given<br /> > an option to choose a heuristic, and he should be aware of the<br /> >
pitfallswhen using this heuristic. I guess, first, we need to get a<br /> > solution which ensures that the
transactiongets committed on all the<br /> > servers or is rolled back on all the foreign servers involved.
AFAIR,<br/> > my patch did that. Once we have that kind of solution, we can think<br /> > about heuristics.<br
/><br/> I meant that we could determine it heuristically only when remote server crashed in 2nd phase of 2PC.<br />For
example,what does the local server returns to client when no one remote server returns OK to local server in 2nd phase
of2PC for more than statement_timeout seconds? Ok or error?<br /><br /> >><br /> >> But we need to handle
thecase where the CLOG file containing XID<br /> >> necessary for resolving dangling transaction is truncated.<br
/>>> If the user does VACUUM FREEZE just after remote server crashed, it<br /> >> could be truncated.<br />
><br/> > Hmm, this needs to be fixed. Even my patch relied on XID to determine<br /> > whether the transaction
committedor rolled back locally and thus to<br /> > decide whether it should be committed or rolled back on all
the<br/> > foreign servers involved. I think I had taken care of the issue you<br /> > have pointed out here. Can
youplease verify the same?<br /> ><br /> >><br /> >>> The way gid is crafted, there is no way to
checkwhether the given<br /> >>> prepared transaction was created by the local server or not. Probably<br />
>>>the local server needs to add a unique signature in GID to identify<br /> >>> the transactions
preparedby itself. That signature should be<br /> >>> transferred to standby to cope up with the fail-over of
localserver.<br /> >><br /> >> Maybe we can use database system identifier in control file.<br /> ><br
/>> may be.<br /> ><br /> >><br /> >>> In this idea, one has to keep on polling the foreign server
tofind<br /> >>> any dangling transactions. In usual scenario, we shouldn't have a<br /> >>> large
numberof dangling transactions, and thus periodic polling might<br /> >>> be a waste.<br /> >><br />
>>We can optimize it by storing the XID that is resolved heuristically<br /> >> into the control file or
systemcatalog, for example.<br /> >><br /> ><br /> > There will be many such XIDs. We don't want to dump so
manythings in<br /> > control file, esp. when that's not control data. System catalog is out<br /> > of question
sincea rollback of local transaction would make those<br /> > rows in the system catalog invisible. That's the
reason,why I chose<br /> > to write the foreign prepared transactions to files rather than a<br /> > system
catalog.<br/> ><br /><br /> We can store the lowest in-doubt transaction id (say in-doubt XID) that needs to be
resolvedlater into control file and the CLOG containing XID greater than in-doubt XID is never truncated.<br /> We need
totry to solve such transaction only when in-doubt XID is not NULL.<br /><br /> Regards,<br /><br /> --<br /> Masahiko
Sawada<br/> NIPPON TELEGRAPH AND TELEPHONE CORPORATION<br /> NTT Open Source Software Center<br /><br /><br />-- <br
/>Regards,<br/><br />--<br />Masahiko Sawada<br />NIPPON TELEGRAPH AND TELEPHONE CORPORATION<br />NTT Open Source
SoftwareCenter <br />