Re: Transactions involving multiple postgres foreign servers

Added to 2015-06 commitfest to attract some reviews and comments.

On Tue, Feb 17, 2015 at 2:56 PM, Ashutosh Bapat <
ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:

> Hi All,
>
> Here are the steps and infrastructure for achieving atomic commits across
> multiple foreign servers. I have tried to address most of the concerns
> raised in this mail thread before. Let me know, if I have left something.
> Attached is a WIP patch implementing the same for postgres_fdw. I have
> tried to make it FDW-independent.
>
> A. Steps during transaction processing
> ------------------------------------------------
>
> 1. When an FDW connects to a foreign server and starts a transaction, it
> registers that server with a boolean flag indicating whether that server is
> capable of participating in a two phase commit. In the patch this is
> implemented using function RegisterXactForeignServer(), which raises an
> error, thus aborting the transaction, if there is at least one foreign
> server incapable of 2PC in a multiserver transaction. This error thrown as
> early as possible. If all the foreign servers involved in the transaction
> are capable of 2PC, the function just updates the information. As of now,
> in the patch the function is in the form of a stub.
>
> Whether a foreign server is capable of 2PC, can be
> a. FDW level decision e.g. file_fdw as of now, is incapable of 2PC but it
> can build the capabilities which can be used for all the servers using
> file_fdw
> b. a decision based on server version type etc. thus FDW can decide that
> by looking at the server properties for each server
> c. a user decision where the FDW can allow a user to specify it in the
> form of CREATE/ALTER SERVER option. Implemented in the patch.
>
> For a transaction involving only a single foreign server, the current code
> remains unaltered as two phase commit is not needed. Rest of the discussion
> pertains to a transaction involving more than one foreign servers.
> At the commit or abort time, the FDW receives call backs with the
> appropriate events. FDW then takes following actions on each event.
>
> 2. On XACT_EVENT_PRE_COMMIT event, the FDW coins one prepared transaction
> id per foreign server involved and saves it along with xid, dbid, foreign
> server id and user mapping and foreign transaction status = PREPARING
> in-memory. The prepared transaction id can be anything represented as byte
> string. Same information is flushed to the disk to survive crashes. This is
> implemented in the patch as prepare_foreign_xact(). Persistent and
> in-memory storages and their usages are discussed later in the mail. FDW
> then prepares the transaction on the foreign server. If this step is
> successful, the foreign transaction status is changed to PREPARED. If the
> step is unsuccessful, the local transaction is aborted and each FDW will
> receive XACT_EVENT_ABORT (discussed later). The updates to the foreign
> transaction status need not be flushed to the disk, as they can be inferred
> from the status of local transaction.
>
> 3. If the local transaction is committed, the FDW callback will get
> XACT_EVENT_COMMIT event. Foreign transaction status is changed to
> COMMITTING. FDW tries to commit the foreign transaction with the prepared
> transaction id. If the commit is successful, the foreign transaction entry
> is removed. If the commit is unsuccessful because of local/foreign server
> crash or network failure, the foreign prepared transaction resolver takes
> care of the committing it at later point of time.
>
> 4. If the local transaction is aborted, the FDW callback will get
> XACT_EVENT_ABORT event. At this point, the FDW may or may not have prepared
> a transaction on foreign server as per step 1 above. If it has not prepared
> the transaction, it simply aborts the transaction on foreign server; a
> server crash or network failure doesn't alter the ultimate result in this
> case. If FDW has prepared the foreign transaction, it updates the foreign
> transaction status as ABORTING and tries to rollback the prepared
> transaction. If the rollback is successful, the foreign transaction entry
> is removed. If the rollback is not successful, the foreign prepared
> transaction resolver takes care of aborting it at later point of time.
>
> B. Foreign prepared transaction resolver
> ---------------------------------------------------
> In the patch this is implemented as a built-in function pg_fdw_resolve().
> Ideally the functionality should be run by a background worker process
> frequently.
>
> The resolver looks at each entry and invokes the FDW routine to resolve
> the transaction. The FDW routine returns boolean status: true if the
> prepared transaction was resolved (committed/aborted), false otherwise.
> The resolution is as follows -
> 1. If foreign transaction status is COMMITTING or ABORTING, commits or
> aborts the prepared transaction resp through the FDW routine. If the
> transaction is successfully resolved, it removes the foreign transaction
> entry.
> 2. Else, it checks if the local transaction was committed or aborted, it
> update the foreign transaction status accordingly and takes the action
> according to above step 1.
> 3. The resolver doesn't touch entries created by in-progress local
> transactions.
>
> If server/backend crashes after it has registered the foreign transaction
> entry (during step A.1), we will be left with a prepared transaction id,
> which was never prepared on the foreign server. Similarly the
> server/backend crashes after it has resolved the foreign prepared
> transaction but before removing the entry, same situation can arise. FDW
> should detect these situations, when foreign server complains about
> non-existing prepared transaction ids and consider such foreign
> transactions as resolved.
>
> After looking at all the entries the resolver flushes the entries to the
> disk, so as to retain the latest status across shutdown and crash.
>
> C. Other methods and infrastructure
> ------------------------------------------------
> 1. Method to show the current foreign transaction entries (in progress or
> waiting to be resolved). Implemented as function pg_fdw_xact() in the patch.
> 2. Method to drop foreign transaction entries in case they are resolved by
> user/DBA themselves. Not implemented in the patch.
> 3. Method to prevent altering or dropping foreign server and user mapping
> used to prepare the foreign transaction till the later gets resolved. Not
> implemented in the patch. While altering or dropping the foreign server or
> user mapping, that portion of the code needs to check if there exists an
> foreign transaction entry depending upon the foreign server or user mapping
> and should error out.
> 4. The information about the xid needs to be available till it is decided
> whether to commit or abort the foreign transaction and that decision is
> persisted. That should put some constraint on the xid wraparound or oldest
> active transaction. Not implemented in the patch.
> 5. Method to propagate the foreign transaction information to the slave.
>
> D. Persistent and in-memory storage considerations
> --------------------------------------------------------------------
> I considered following options for persistent storage
> 1. in-memory table and file(s) - The foreign transaction entries are saved
> and manipulated in shared memory. They are written to file whenever
> persistence is necessary e.g. while registering the foreign transaction in
> step A.2. Requirements C.1, C.2 need some SQL interface in the form of
> built-in functions or SQL commands.
>
> The patch implements the in-memory foreign transaction table as a fixed
> size array of foreign transaction entries (similar to prepared transaction
> entries in twophase.c). This puts a restriction on number of foreign
> prepared transactions that need to be maintained at a time. We need
> separate locks to syncronize the access to the shared memory; the patch
> uses only a single LW lock. There is restriction on the length of prepared
> transaction id (or prepared transaction information saved by FDW to be
> general), since everything is being saved in fixed size memory. We may be
> able to overcome that restriction by writing this information to separate
> files (one file per foreign prepared transaction). We need to take the same
> route as 2PC for C.5.
>
> 2. New catalog - This method takes out the need to have separate method
> for C1, C5 and even C2, also the synchronization will be taken care of by
> row locks, there will be no limit on the number of foreign transactions as
> well as the size of foreign prepared transaction information. But big
> problem with this approach is that, the changes to the catalogs are atomic
> with the local transaction. If a foreign prepared transaction can not be
> aborted while the local transaction is rolled back, that entry needs to
> retained. But since the local transaction is aborting the corresponding
> catalog entry would become invisible and thus unavailable to the resolver
> (alas! we do not have autonomous transaction support). We may be able to
> overcome this, by simulating autonomous transaction through a background
> worker (which can also act as a resolver). But the amount of communication
> and synchronization, might affect the performance.
>
> A mixed approach where the backend shifts the entries from storage in
> approach 1 to catalog, thus lifting the constraints on size is possible,
> but is very complicated.
>
> Any other ideas to use catalog table as the persistent storage here? Does
> anybody think, catalog table is a viable option?
>
> 3. WAL records - Since the algorithm follows "write ahead of action", WAL
> seems to be a possible way to persist the foreign transaction entries. But
> WAL records can not be used for repeated scan as is required by the foreign
> transaction resolver. Also, replaying WALs is controlled by checkpoint, so
> not all WALs are replayed. If a checkpoint happens after a foreign prepared
> transaction remains resolved, corresponding WALs will never be replayed,
> thus causing the foreign prepared transaction to remain unresolved forever
> without a clue. So, WALs alone don't seem to be a fit here.
>
> The algorithms rely on the FDWs to take right steps to the large extent,
> rather than controlling each step explicitly. It expects the FDWs to take
> the right steps for each event and call the right functions to manipulate
> foreign transaction entries. It does not ensure the correctness of these
> steps, by say examining the foreign transaction entries in response to each
> event or by making the callback return the information and manipulate the
> entries within the core. I am willing to go the stricter but more intrusive
> route if the others also think that way. Otherwise, the current approach is
> less intrusive and I am fine with that too.
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company