Re: FDW-based dblink - Mailing list pgsql-hackers

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:

> Quite aside from the requirement for on-commit trigger, how exactly
> would you use 2PC with the remote database? When would you issue PREPARE
> TRANSACTION, and when would COMMIT PREPARED? What if the local database
> crashes in between - is the remote transaction left hanging in prepared
> state?

I'm thinking prepareing remote transactions just before commit the local
transaction in CommitTransaction(). The pseudo code is something like:

   1. Fire deferred triggers and do works for just-before-commit.
   2. AtEOXact_dblink()
       => prepare and commit remote transactions.
   3. HOLD_INTERRUPTS()
       We cannot rollback the local transaction after this.
   4. do works for commit

If we need more robust atomicity, we could use 2PC against the local
transaction if there some remote transactions. i.e., expand COMMIT
command into PREPARE TRANSACTION and COMMIT PREPARED internally:

   1. Fire deferred triggers and do works for just-before-commit.
   2. AtEOXact_dblink_prepare()            -- prepare remotes
   3. PrepareTransaction()                 -- prepare local
   4. AtEOXact_dblink_commit()             -- commit remotes
   5. FinishPreparedTransaction(commit)    -- commit local

I'm using deferrable after trigger for the purpose in my present
prototype, and it seems to work if the trigger is called at the
end of deferrable event and local backend doesn't crash in final
works for commit -- and we have some should-not-failed operations
in the final works already  (flushing WAL, etc.).