Re: inherit support for foreign tables - Mailing list pgsql-hackers

On Thu, Dec 04, 2014 at 10:00:14AM +0530, Ashutosh Bapat wrote:
> On Thu, Dec 4, 2014 at 9:05 AM, Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote:
> > (2014/12/03 19:35), Ashutosh Bapat wrote:
> >> On Tue, Dec 2, 2014 at 8:29 AM, Etsuro Fujita
> >> <fujita.etsuro@lab.ntt.co.jp <mailto:fujita.etsuro@lab.ntt.co.jp>> wrote:

> >      IIUC, even the transactions over the local and the *single* remote
> >>     server are not guaranteed to be executed atomically in the current
> >>     form.  It is possible that the remote transaction succeeds and the
> >>     local one fails, for example, resulting in data inconsistency
> >>     between the local and the remote.
> >>
> >
> >  IIUC, while committing transactions involving a single remote server,
> >> the steps taken are as follows
> >> 1. the local changes are brought to PRE-COMMIT stage, which means that
> >> the transaction *will* succeed locally after successful completion of
> >> this phase,
> >> 2. COMMIT message is sent to the foreign server
> >> 3. If step two succeeds, local changes are committed and successful
> >> commit is conveyed to the client
> >> 4. if step two fails, local changes are rolled back and abort status is
> >> conveyed to the client
> >> 5. If step 1 itself fails, the remote changes are rolled back.
> >> This is as per one phase commit protocol which guarantees ACID for
> >> single foreign data source. So, the changes involving local and a single
> >> foreign server seem to be atomic and consistent.
> >>
> >
> > Really?  Maybe I'm missing something, but I don't think the current
> > implementation for committing transactions has such a mechanism stated in
> > step 1.  So, I think it's possible that the local transaction fails in
> > step3 while the remote transaction succeeds, as mentioned above.
> >
> >
> PFA a script attached which shows this. You may want to check the code in
> pgfdw_xact_callback() for actions taken by postgres_fdw on various events.
> CommitTransaction() for how those events are generated. The code there
> complies with the sequence above.

While postgres_fdw delays remote commit to eliminate many causes for having
one server commit while another aborts, other causes remain.  The local
transaction could still fail due to WAL I/O problems or a system crash.  2PC
is the reliable answer, but that was explicitly out of scope for the initial
postgres_fdw write support.  Does this inheritance patch add any atomicity
problem that goes away when one breaks up the inheritance hierarchy and
UPDATEs each table separately?  If not, this limitation is okay.