Commit scopes#

Commit scopes are rules that determine how transaction commits and conflicts are handled within a PGD system. You can read more about them in Migration to commit scopes .

You can manipulate commit scopes using the following functions:

Commit scope syntax#

The overall grammar for commit scope rules is composed as follows:

commit_scope:
    commit_scope_operation [AND ...]

commit_scope_operation:
    commit_scope_group confirmation_level commit_scope_kind

commit_scope_target:
    { (node_group [, ...])
      | ORIGIN_GROUP }

commit_scope_group:
{ ANY num [NOT] commit_scope_target
  | MAJORITY [NOT] commit_scope_target
  | ALL [NOT] commit_scope_target }

confirmation_level:
    [ ON { received|replicated|durable|visible } ]

commit_scope_kind:
{ GROUP COMMIT [ ( group_commit_parameter = value [, ... ] ) ] [ ABORT ON ( abort_on_parameter = value ) ] [ DEGRADE ON (degrade_on_parameter = value [, ... ] ) TO commit_scope_degrade_operation ]
  | QUORUM COMMIT [ ( commit_decision = { group | raft } ) ] ABORT ON ( timeout = value )
  | CAMO [ DEGRADE ON ( degrade_on_parameter = value [, ... ] ) TO ASYNC ]
  | LAG CONTROL [ ( lag_control_parameter = value [, ... ] ) ]
  | SYNCHRONOUS COMMIT [ DEGRADE ON (degrade_on_parameter = value ) TO commit_scope_degrade_operation ] }

commit_scope_degrade_operation:
   commit_scope_group confirmation_level commit_scope_kind

Where node_group is the name of a PGD data node group.

commit_scope_degrade_operation#

The commit_scope_degrade_operation is either the same commit scope kind with a less restrictive commit scope group as the overall rule being defined, or is asynchronous (ASYNC ).

For instance, Degrading commit scope rules from an ALL SYNCHRONOUS COMMIT to a MAJORITY SYNCHRONOUS COMMIT or a MAJORITY SYNCHRONOUS COMMIT to an ANY 3 SYNCHRONOUS COMMIT or even an ANY 3 SYNCHRONOUS COMMIT to an ANY 2 SYNCHRONOUS COMMIT . You can also degrade from SYNCHRONOUS COMMIT to ASYNC . However, you cannot degrade from SYNCHRONOUS COMMIT to GROUP COMMIT or the other way around, regardless of the commit scope groups involved.

It is also possible to combine rules using AND , each with their own degradation clause:

ALL ORIGIN GROUP SYNCHRONOUS COMMIT DEGRADE ON (timeout = 10s) TO MAJORITY ORIGIN GROUP SYNCHRONOUS COMMIT AND ANY 1 NOT ORIGIN GROUP SYNCHRONOUS COMMIT DEGRADE ON (timeout = 20s) TO ASYNC

Commit scope targets#

ORIGIN_GROUP#

Instead of targeting a specific group, you can also use ORIGIN_GROUP , which dynamically refers to the bottommost group from which a transaction originates. Therefore, if you have a top level group, top_group , and two subgroups as children, left_dc and right_dc , then adding a commit scope like:

SELECT bdr.create_commit_scope(
    commit_scope_name := example_scope,
    origin_node_group := top_level_group,
    rule := MAJORITY ORIGIN GROUP SYNCHRONOUS COMMIT,
    wait_for_ready := true
);

would mean that for transactions originating on a node in left_dc , a majority of the nodes of left_dc would need to confirm the transaction synchronously before the transaction is committed. Moreover, the same rule would also mean that for transactions originating from a node in right_dc , a majority of nodes from right_dc are required to confirm the transaction synchronously before it is committed. This saves the need to add two seperate rules, one for left_dc and one for right_dc , to the commit scope.

Commit scope groups#

ANY#

Example: ANY 2 (left_dc)

A transaction under this commit scope group will be considered committed after any two nodes in the left_dc group confirm they processed the transaction.

ANY NOT#

Example: ANY 2 NOT (left_dc)

A transaction under this commit scope group will be considered committed if any two nodes that aren’t in the left_dc group confirm they processed the transaction.

MAJORITY#

Example: MAJORITY (left_dc)

A transaction under this commit scope group will be considered committed if a majority of the nodes in the left_dc group confirm they processed the transaction.

MAJORITY NOT#

Example: MAJORITY NOT (left_dc)

A transaction under this commit scope group will be considered committed if a majority of the nodes that aren’t in the left_dc group confirm they processed the transaction.

ALL#

Example: ALL (left_dc)

A transaction under this commit scope group will be considered committed if all of the nodes in the left_dc group confirm they processed the transaction.

When ALL is used with Group Commit (legacy) , the bdr.commit_decisions setting must be set to raft to avoid reconciliation issues.

ALL NOT#

Example: ALL NOT (left_dc)

A transaction under this commit scope group will be considered committed if all of the nodes that aren’t in the left_dc group confirm they processed the transaction.

Confirmation level#

The confirmation level sets the point in time when a remote PGD node confirms that it reached a particular point in processing a transaction. QUORUM COMMIT only supports durable and visible .

ON received#

A transaction is confirmed immediately after receiving it, prior to starting the local application.

ON replicated#

A transaction is confirmed after applying changes of the transaction but before flushing them to disk.

ON durable#

A transaction is confirmed after all of its changes are flushed to disk.

ON visible#

This is the default visibility. A transaction is confirmed after all of its changes are flushed to disk and it’s visible to concurrent transactions.

Commit Scope kinds#

More details of the commit scope kinds and details of their parameters:

Postgres GUC parameter value conventions .

SYNCHRONOUS COMMIT [ DEGRADE ON (degrade_on_parameter = value ) TO commit_scope_degrade_operation ]

DEGRADE ON parameters#

Parameter	Type	Default	Description
timeout	interval	0	Timeout in milliseconds (accepts other units) after which operation degrades. (0 means not set.)
require_write_lead	Boolean	False	Specifies whether the node must be a write lead to be able to switch to degraded operation.

These set the conditions on which the commit scope rule will degrade to a less restrictive mode of operation.

commit_scope_degrade_operation#

The commit_scope_degrade_operation must be SYNCHRONOUS COMMIT with a less restrictive commit scope group—or must be asynchronous (ASYNC ).

QUORUM COMMIT#

Provides distributed transaction consistency by coordinating commit decisions across all participating nodes before any node commits.

See How Quorum Commit works for more details.

QUORUM COMMIT [ ( commit_decision = { group | raft } ) ] ABORT ON ( timeout = value )

Supported commit scope groups are ALL , MAJORITY ORIGIN GROUP , MAJORITY CLUSTER , and MAJORITY FOR EACH GROUP . MAJORITY with an explicit group list, ANY n , and NOT aren’t supported.

Only the durable and visible confirmation levels are supported with QUORUM COMMIT . Using received or replicated results in an error.

QUORUM COMMIT parameters#

Parameter	Type	Default	Description
commit_decision	enum	group	Specifies how the commit decision is made. (group I raft). See commit_decision settings .

QUORUM COMMIT ABORT ON parameters#

ABORT ON is mandatory for Quorum Commit.

Parameter	Type	Default	Description
timeout	interval	0	Time to wait for quorum before aborting. (0 means not set.)

commit_decision settings#

The value group means the origin node decides to commit once it receives the required confirmations from the commit scope group.

The value raft means the commit decision goes through PGD’s built-in Raft consensus. This is slower than group but may be required for certain multi-group topologies.

CAMO#

With the client’s cooperation, enables protection to prevent multiple insertions of the same transaction in failover scenarios.

See Commit At Most Once for more details.

CAMO [ DEGRADE ON ( degrade_on_parameter = value ) TO ASYNC ]

DEGRADE ON parameters#

Allows degrading to asynchronous operation on timeout.

Parameter	Type	Default	Description
timeout	interval	0	Timeout in milliseconds (accepts other units) after which operation becomes asynchronous. (0 means not set.)
require_write_lead	Boolean	False	Specifies whether the node must be a write lead to be able to switch to asynchronous mode.

LAG CONTROL#

Allows the configuration of dynamic rate-limiting controlled by replication lag.

See Lag Control for more details.

LAG CONTROL [ ( lag_control_parameter = value [, ... ] ) ]

LAG CONTROL parameters#

Parameter	Type	Description
max_lag_size	int	The maximum lag in kB that a given node can have in the replication connection to another node. When the lag exceeds this maximum scaled by max_commit_delay, lag control adjusts the commit delay.
max_lag_time	interval	The maximum replication lag in milliseconds that the given origin can have with regard to a replication connection to a given downstream node.
max_commit_delay	interval	Configures the maximum delay each commit can take, in fractional milliseconds. If set to 0, it disables Lag Control. After each commit delay adjustment (for example, if the replication is lagging more than max_lag_size or max_lag_time), the commit delay is recalculated with the weight of the bdr.lag_control_commit_delay_adjust GUC. The max_commit_delay is a ceiling for the commit delay.

If max_lag_size and max_lag_time are set to 0, the LAG CONTROL is disabled.
If max_commit_delay is not set or set to 0, the LAG CONTROL is disabled.

The lag size is derived from the delta of the send_ptr of the walsender to the apply_ptr of the receiver.

The lag time is calculated according to the following formula:

lag_time = (lag_size / apply_rate) * 1000;

Where lag_size is the delta between the send_ptr and apply_ptr (as used for max_lag_size ), and apply_rate is a weighted exponential moving average, following the simplified formula:

apply_rate = prev_apply_rate * (1 - apply_rate_weight) +
              ((apply_ptr_diff * apply_rate_weight) / diff_secs);

Where:

prev_apply_rate was the previously configured apply_rate , before recalculating the new rate.
apply_rate_weight is the value of the GUC bdr.lag_tracker_apply_rate_weight .
apply_ptr_diff is the difference between the current apply_ptr and the apply_ptr at the point in time when the apply rate was last computed.
diff_secs is the delta in seconds from the last time the apply rate was calculated.

GROUP COMMIT#

Allows commits to be confirmed by a consensus of nodes, controls conflict resolution settings, and, like Synchronous Commit , has optional rule-degredation parameters.

GROUP COMMIT [ ( group_commit_parameter = value [, ...] ) ] [ ABORT ON ( abort_on_parameter = value ) ] [ DEGRADE ON (degrade_on_parameter = value ) TO commit_scope_degrade_operation ]

GROUP COMMIT parameters#

Parameter	Type	Default	Description
transaction_tracking	Boolean	Off/False	Specifies whether to track status of transaction. See transaction_tracking settings .
conflict_resolution	enum	async	Specifies how to handle conflicts. (async`I`eager ). See conflict_resolution settings .
commit_decision	enum	group	Specifies how the COMMIT decision is made. (group`I`partner`I`raft). See commit_decision settings .

ABORT ON parameters#

Parameter	Type	Default	Description
timeout	interval	0	Timeout in milliseconds (accepts other units). (0 means not set.)
require_write_lead	Boolean	False	CAMO only. If set, then for a transaction to switch to local (async) mode, a consensus request is required.

DEGRADE ON parameters#

Parameter	Type	Default	Description
timeout	interval	0	Timeout in milliseconds (accepts other units) after which operation degrades. (0 means not set.)
require_write_lead	Boolean	False	Specifies whether the node must be a write lead to be able to switch to degraded operation.

transaction_tracking settings#

When set to true, two-phase commit transactions:

Look up commit decisions when a writer is processing a PREPARE message.
When recovering from an interruption, look up the transactions prepared before the interruption. When found, it then looks up the commit scope of the transaction and any corresponding RAFT commit decision. Suppose the node is the origin of the transaction and doesn’t have a RAFT commit decision, and transaction_tracking is on in the commit scope. In that case, it periodically looks for a RAFT commit decision for this unresolved transaction until it’s committed or aborted.

conflict_resolution settings#

The value async means resolve conflicts asynchronously during replication using the conflict resolution policy.

The value eager means that conflicts are resolved eagerly during COMMIT by aborting one of the conflicting transactions.

Eager is only available with MAJORITY or ALL commit scope groups.

When used with the ALL commit scope group, the bdr.commit_decisions must be set to raft to avoid reconcilation issue.

See Missing-column conflict resolution .

GROUP COMMIT commit_decision settings#

The value group means the preceding commit_scope_group specification also affects the COMMIT decision, not just durability.

The value partner means the partner node decides whether transactions can be committed. This value is allowed only on groups with 2 data nodes.

The value raft means the decision makes use of PGD’s built-in Raft consensus. Once all the nodes in the selected commit scope group have confirmed the transaction, to ensure that all the nodes in the PGD cluster have noted the transaction, it is noted with the all-node Raft.

This option must be used when the ALL commit scope group is being used to ensure no divergence between the nodes over the decision. This option may have low performance.

See Commit decisions .

commit_scope_degrade_operation settings#

The commit_scope_degrade_operation must be GROUP_COMMIT with a less restrictive commit scope group—or must be asynchronous (ASYNC ).

Commit scopes

Contents

Commit scopes#

Commit scope syntax#

commit_scope_degrade_operation#

Commit scope targets#

ORIGIN_GROUP#

Commit scope groups#

ANY#

ANY NOT#

MAJORITY#

MAJORITY NOT#

ALL#

ALL NOT#

Confirmation level#

ON received#

ON replicated#

ON durable#

ON visible#

Commit Scope kinds#

DEGRADE ON parameters#

commit_scope_degrade_operation#

QUORUM COMMIT#

QUORUM COMMIT parameters#

QUORUM COMMIT ABORT ON parameters#

commit_decision settings#

CAMO#

DEGRADE ON parameters#

LAG CONTROL#

LAG CONTROL parameters#

GROUP COMMIT#

GROUP COMMIT parameters#

ABORT ON parameters#

DEGRADE ON parameters#

transaction_tracking settings#

conflict_resolution settings#

GROUP COMMIT commit_decision settings#

commit_scope_degrade_operation settings#