BDR System Functions¶

BDR management is primarily accomplished via SQL-callable functions. All functions in BDR are exposed in the bdr schema. Any calls to these functions should be schema-qualified, rather than putting bdr in the search_path .

This page contains additional system functions that are not described in the other sections of the documentation.

Version Information Functions¶

bdr.bdr_version¶

This function retrieves the textual representation of the BDR version that is currently in use.

bdr.bdr_version_num¶

This function retrieves a numerical representation of the BDR version that is currently in use. Version numbers are monotonically increasing, allowing this value to be used for less-than and greater-than comparisons.

The following formula is used to turn the version number consisting of major version, minor verion and patch release into a single numerical value:

MAJOR_VERSION * 10000 + MINOR_VERSION * 100 + PATCH_RELEASE

System Information Functions¶

bdr.get_relation_stats¶

Returns the relation information.

bdr.get_subscription_stats¶

Returns the current subscription statistics.

System and Progress Information Parameters¶

BDR exposes some parameters that can be queried via SHOW in psql or using PQparameterStatus (or equivalent) from a client application. This section lists all such parameters BDR reports to.

bdr.local_node_id¶

Upon session initialization, this is set to the node id the client is connected to. This allows an application to figure out what node it is connected to even behind a transparent proxy.

It is also used in combination with CAMO, see the Connection pools and proxies section.

bdr.last_committed_lsn¶

After every COMMIT of an asynchronous transaction, this parameter is updated to point to the end of the commit record on the origin node. In combination with bdr.wait_for_apply_queue , this allows applications to perform causal reads across multiple nodes, i.e. to wait until a transaction becomes remotely visible.

transaction_id¶

As soon as Postgres assigns a transaction id, this parameter is updated to show the transaction id just assigned, if CAMO is enabled.

Consensus Function¶

bdr.consensus_disable¶

Disables the consensus worker on the local node until server restart or until it’s re-enabled using bdr.consensus_enable (whichever happens first).

Warning

Disabling consensus will disable some features of BDR and eventually will impact availability of the BDR cluster if left disabled for prolonged periods of time. This function should only be used in coordination with Technical Support.

bdr.consensus_enable¶

Re-enabled disabled consensus worker on local node.

bdr.consensus_proto_version¶

Returns currently used consensus protocol version by the local node.

Needed by the BDR group reconfiguration internal mechanisms.

bdr.consensus_snapshot_export¶

Synopsis¶

bdr.consensus_snapshot_export(version integer DEFAULT NULL)

Generate a new BDR consensus snapshot from the currently committed-and-applied state of the local node and return it as bytea.

By default, a snapshot for the highest supported Raft version is exported. But that can be overridden by passing an explicit version number.

The exporting node does not have to be the current Raft leader, nor does it need to be completely up to date with the latest state on the leader. However, such snapshot might not be accepted by bdr.consensus_snapshot_import() (see bellow).

The new snapshot is not automatically stored to the local node’s bdr.local_consensus_snapshot table. It’s only returned to the caller.

The generated snapshot may be passed to bdr.consensus_snapshot_import() on any other node(s) in the same BDR nodegroup that is behind the exporting node’s raft log position.

The local BDR consensus worker must be disabled for this function to work. Typical usage is:

SELECT bdr.bdr_consensus_disable();
\copy (SELECT * FROM bdr.consensus_snapshot_export()) TO my_node_consensus_snapshot.data
SELECT bdr.bdr_consensus_enable();

While the BDR consensus worker is disabled, DDL locking attempts on the node will fail or time out, galloc sequences will not get new values, Eager and CAMO transactions will pause or ERROR, and other functionality that needs the distributed consensus system will be disrupted. The required downtime is generally very brief.

Depending on the use case, it may be practical to extract a snapshot that already exists from the snapshot field of the bdr.local_consensus_snapshot table and use that instead. Doing so does not require that the consensus worker be stopped.

bdr.consensus_snapshot_import¶

Synopsis¶

bdr.consensus_snapshot_import(IN snapshot bytea)

Import a consensus snapshot which was exported by bdr.consensus_snapshot_export() , usually from another node in the same BDR nodegroup.

It’s also possible to use a snapshot extracted directly from the snapshot field of the bdr.local_consensus_snapshot table on another node.

This function is useful for resetting a BDR node’s catalog state to known good state in case of corruption or user mistake.

The snapshot can be imported if the importing node’s apply_index is less than or equal to the snapshot-exporting node’s commit_index at the time the snapshot was generated. See bdr.get_raft_status() . A node that cannot accept the snapshot because its logs is already too far ahead will raise an ERROR and make no changes. The imported snapshot does not have to be completely up-to-date, as once the snapshot is imported the node will fetch the remaining changes from the current leader.

The BDR consensus worker must be disabled on the importing node for this function to work. See notes on bdr.consensus_snapshot_export() for details.

It’s possible to use this to force the local node to generate a new Raft snapshot by running:

SELECT bdr.consensus_snapshot_import(bdr.consensus_snapshot_export());

This may also cause it to truncate its Raft logs up to the current applied log position.

bdr.consensus_snapshot_verify¶

Synopsis¶

bdr.consensus_snapshot_verify(IN snapshot bytea)

Verify the given consensus snapshot which was exported by bdr.consensus_snapshot_export() . The snapshot header contains the version with which is was generated and the node will try to verify it against the same version.

The snapshot may have been exported on the same node or any other node in the cluster. If the node verifying the snapshot does not support the version of the exported snapshot, then an error will be raised.

bdr.get_consensus_status¶

Returns status information about the current consensus (Raft) worker.

bdr.get_raft_status¶

Returns status information about the current consensus (Raft) worker. Alias for bdr.get_consensus_status .

bdr.raft_leadership_transfer¶

Synopsis¶

bdr.raft_leadership_transfer(IN node_name text, IN wait_for_completion boolean)

Request the node identified by node_name to be the Raft leader. The request can be initiated from any of the BDR nodes and it will be internally forwarded to the current leader to transfer the leadership to the designated node. The designated node must be an ACTIVE BDR node and with full voting rights.

If wait_for_completion is false, the request is served on best-efforts basis. If the node can’t become a leader within bdr.raft_election_timeout period, then some other capable node will become the leader again. Also, the leadership can change over the period of time per Raft protocol. A true return result only indicates the request had been submitted successfully.

If wait_for_completion is true then the function will wait until the given node becomes the new leader and possibly wait infinitely if the requested node fails to become Raft leader (e.g. due to network issues). It is therefore recommended to always set a statement_timeout in combination with wait_for_completion to prevent an infinite loop.

Utility Functions¶

bdr.wait_slot_confirm_lsn¶

Allows the user to wait until the last write on this session has been replayed to one or all nodes.

Waits until a slot passes certain LSN. If no position is supplied, the current write position is used on the local node.

If no slot name is passed, it will wait until all BDR slots pass the LSN.

The function polls every 1000ms for changes from other nodes.

If a slot is dropped concurrently the wait will end for that slot. If a node is currently down and is not updating its slot then the wait will continue. You may wish to set statement_timeout to complete earlier in that case.

Synopsis¶

bdr.wait_slot_confirm_lsn(slot_name text DEFAULT NULL, target_lsn pg_lsn DEFAULT NULL)

Parameters¶

slot_name - name of replication slot, or if NULL, all BDR slots (only)
target_lsn - LSN to wait for, or if NULL, use the current write LSN on the local node

bdr.wait_for_apply_queue¶

The function bdr.wait_for_apply_queue allows a BDR node to wait for the local application of certain transactions originating from a given BDR node. It will return only after all transactions from that peer node are applied locally. An application or a proxy can use this function to prevent stale reads.

For convenience, BDR provides a special variant of this function for CAMO and the CAMO partner node, see bdr.wait_for_camo_partner_queue .

In case a specific LSN is given, that’s the point in the recovery stream from the peer to wait for. This can be used in combination with bdr.last_committed_lsn retrieved from that peer node on a previous or concurrent connection.

If the given target_lsn is NULL, this function checks the local receive buffer and uses the LSN of the last transaction received from the given peer node. Effectively waiting for all transactions already received to be applied. This is especially useful in case the peer node has failed and it’s not known which transactions have been sent. Note that in this case, transactions that are still in transit or buffered on the sender side are not waited for.

Synopsis¶

bdr.wait_for_apply_queue(peer_node_name TEXT, target_lsn pg_lsn)

Parameters¶

peer_node_name - the name of the peer node from which incoming transactions are expected to be queued and which should be waited for. If NULL, waits for all peer node’s apply queue to be consumed.
target_lsn - the LSN in the replication stream from the peer node to wait for, usually learned via bdr.last_committed_lsn from the peer node.

bdr.get_node_sub_receive_lsn¶

This function can be used on a subscriber to get the last LSN that has been received from the given origin. Either filtered to take into account only relevant LSN increments for transactions to be applied or unfiltered.

The difference between the output of this function and the output of bdr.get_node_sub_apply_lsn() measures the size of the corresponding apply queue.

Synopsis¶

bdr.get_node_sub_receive_lsn(node_name name, committed bool default true)

Parameters¶

node_name - the name of the node which is the source of the replication stream whose LSN we are retrieving/
committed - the default (true) makes this function take into account only commits of transactions received, rather than the last LSN overall; including actions that have no effect on the subscriber node.

bdr.get_node_sub_apply_lsn¶

This function can be used on a subscriber to get the last LSN that has been received and applied from the given origin.

Synopsis¶

bdr.get_node_sub_apply_lsn(node_name name)

Parameters¶

node_name - the name of the node which is the source of the replication stream whose LSN we are retrieving.

bdr.run_on_all_nodes¶

Function to run a query on all nodes.

Warning

This function will run an arbitrary query on a remote node with the privileges of the user used for the internode connections as specified in the node’s DSN. Caution needs to be taken when granting privileges to this function.

Synopsis¶

bdr.run_on_all_nodes(query text)

Parameters¶

query - arbitrary query to be executed.

Notes¶

This function will connect to other nodes and execute the query, returning a result from each of them in json format. Multiple rows may be returned from each node, encoded as a json array. Any errors, such as being unable to connect because a node is down, will be shown in the response field. No explicit statement_timeout or other runtime parameters are set, so defaults will be used.

This function does not go through normal replication, it uses direct client connection to all known nodes. By default, the connection is created with bdr.ddl_replication = off, since the command are already being sent to all of the nodes in the cluster.

Be careful when using this function since you risk breaking replication and causing inconsistencies between nodes. Use either transparent DDL replication or bdr.replicate_ddl_command() to replicate DDL. DDL may be blocked in a future release.

Example¶

It’s useful to use this function in monitoring, for example in the following query:

SELECT bdr.run_on_all_nodes($$
    SELECT local_slot_name, origin_name, target_name, replay_lag_size
      FROM bdr.node_slots
     WHERE origin_name IS NOT NULL
$$);

…will return something like this on a two node cluster:

[
    {
        "dsn": "host=node1 port=5432 dbname=bdrdb user=postgres ",
        "node_id": "2232128708",
        "response": {
            "command_status": "SELECT 1",
            "command_tuples": [
                {
                    "origin_name": "node1",
                    "target_name": "node2",
                    "local_slot_name": "bdr_bdrdb_bdrgroup_node2",
                    "replay_lag_size": "0 bytes"
                }
            ]
        },
        "node_name": "node1"
    },
    {
        "dsn": "host=node2 port=5432 dbname=bdrdb user=postgres ",
        "node_id": "2058684375",
        "response": {
            "command_status": "SELECT 1",
            "command_tuples": [
                {
                    "origin_name": "node2",
                    "target_name": "node1",
                    "local_slot_name": "bdr_bdrdb_bdrgroup_node1",
                    "replay_lag_size": "0 bytes"
                }
            ]
        },
        "node_name": "node2"
    }
]

bdr.run_on_nodes¶

Function to run a query on a specified list of nodes.

Warning

This function will run an arbitrary query on remote nodes with the privileges of the user used for the internode connections as specified in the node’s DSN. Caution needs to be taken when granting privileges to this function.

Synopsis¶

bdr.run_on_nodes(node_names text[], query text)

Parameters¶

node_names - text ARRAY of node names where query will be executed.
query - arbitrary query to be executed.

Notes¶

This function will connect to other nodes and execute the query, returning a result from each of them in json format. Multiple rows may be returned from each node, encoded as a json array. Any errors, such as being unable to connect because a node is down, will be shown in the response field. No explicit statement_timeout or other runtime parameters are set, so defaults will be used.

This function does not go through normal replication, it uses direct client connection to all known nodes. By default, the connection is created with bdr.ddl_replication = off, since the command are already being sent to all of the nodes in the cluster.

Be careful when using this function since you risk breaking replication and causing inconsistencies between nodes. Use either transparent DDL replication or bdr.replicate_ddl_command() to replicate DDL. DDL may be blocked in a future release.

bdr.run_on_group¶

Function to run a query on a group of nodes.

Warning

This function will run an arbitrary query on remote nodes with the privileges of the user used for the internode connections as specified in the node’s DSN. Caution needs to be taken when granting privileges to this function.

Synopsis¶

bdr.run_on_group(node_group_name text, query text)

Parameters¶

node_group_name - name of node group where query will be executed.
query - arbitrary query to be executed.

Notes¶

This function will connect to other nodes and execute the query, returning a result from each of them in json format. Multiple rows may be returned from each node, encoded as a json array. Any errors, such as being unable to connect because a node is down, will be shown in the response field. No explicit statement_timeout or other runtime parameters are set, so defaults will be used.

This function does not go through normal replication, it uses direct client connection to all known nodes. By default, the connection is created with bdr.ddl_replication = off, since the command are already being sent to all of the nodes in the cluster.

Be careful when using this function since you risk breaking replication and causing inconsistencies between nodes. Use either transparent DDL replication or bdr.replicate_ddl_command() to replicate DDL. DDL may be blocked in a future release.

bdr.global_lock_table¶

This function will acquire a global DML locks on a given table. See: DDL Locking Details for information about global DML lock.

Synopsis¶

bdr.global_lock_table(relation regclass)

Parameters¶

relation - name or Oid of the relation to be locked.

Notes¶

This function will acquire the global DML lock independently of the ddl_locking setting.

The bdr.global_lock_table function requires UPDATE , DELETE , or TRUNCATE privilege on the locked relation , unless bdr.backwards_compatibility is set is set to 30618 or below.

bdr.wait_for_xid_progress¶

This function can be used to wait for the given transaction (identified by it’s XID) originated at the given node (identified by it’s node id) to make enough progress on the cluster. The progress is defined as the transaction being applied on a node and this node having seen all other replication changes done before the transaction is applied.

Synopsis¶

bdr.wait_for_xid_progress(origin_node_id oid, origin_topxid int4, allnodes boolean DEFAULT true)

Parameters¶

origin_node_id - node id of the node where the transaction was originated.
origin_topxid - XID of the transaction.
allnodes - if true then wait for the transaction to progress on all nodes. Otherwise only wait for the current node.

Notes¶

The function can be used only for those transactions that have replicated a DDL command because only those transactions are tracked currently. If a wrong origin_node_id or origin_topxid is supplied, the function may wait forever or until statement_timeout is hit.

bdr.local_group_slot_name¶

Returns the name of the group slot on the local node.

Example¶

bdrdb=# SELECT bdr.local_group_slot_name();
 local_group_slot_name
- ----------------------
 bdr_bdrdb_bdrgroup

bdr.node_group_type¶

Returns the type of the given node group. Returned value is same as what was passed to bdr.create_node_group() when the node group was created, except normal is returned if the node_group_type was passed as NULL when the group was created.

Example¶

bdrdb=# SELECT bdr.node_group_type(bdrgroup);
 node_group_type
- ----------------
 normal

Global Advisory Locks¶

BDR supports global advisory locks. These locks are very similar to the advisory locks available in PostgreSQL except that the advisory locks supported by BDR are global in nature. They follow semantics similar to DDL locks. So an advisory lock is obtained by majority consensus and hence can be used even if one or more nodes are down or lagging behind, as long as a majority of all nodes can work together.

Currently we only support EXCLUSIVE locks. So if another node or another backend on the same node has already acquired the advisory lock on the object, then other nodes or backends must wait for the lock to be released.

Advisory lock is transactional in nature. So the lock is automatically released when the transaction ends unless it is explicitly released before the end of the transaction, in which case it will be available as soon as it’s released. Session level advisory locks are not currently supported.

Global advisory locks are re-entrant. So if the same resource is locked three times it must then be unlocked three times to be released for other sessions’ use.

bdr.global_advisory_lock¶

This function acquires an EXCLUSIVE lock on the provided object. If the lock is not available, then it will wait until the lock becomes available or the bdr.global_lock_timeout is reached.

Synopsis¶

bdr.global_advisory_lock(key bigint)

parameters¶

key - the object on which an advisory lock is acquired.

Synopsis¶

bdr.global_advisory_lock(key1 integer, key2 integer)

parameters¶

key1 - first part of the composite key.
key2 - second part of the composite key.

bdr.global_advisory_unlock¶

This function released previously acquired lock on the application defined source. The lock must have been previously obtained in the same transaction by the application, otherwise an ERROR is raised.

Synopsis¶

bdr.global_advisory_unlock(key bigint)

parameters¶

key - the object on which advisory lock is acquired.

Synopsis¶

bdr.global_advisory_unlock(key1 integer, key2 integer)

parameters¶

key1 - first part of the composite key.
key2 - second part of the composite key.

Monitoring Functions¶

bdr.monitor_group_versions¶

To provide a cluster-wide version check, this function uses BDR version information returned from the view bdr.group_version_details .

Synopsis¶

bdr.monitor_group_versions()

Notes¶

This function returns a record with fields status and message , as explained in Monitoring and logging .

This function calls bdr.run_on_all_nodes() .

bdr.monitor_group_raft¶

To provide a cluster-wide Raft check, this function uses BDR Raft information returned from view bdr.group_raft_details .

Synopsis¶

bdr.monitor_group_raft()

Notes¶

This function returns a record with fields status and message , as explained in Monitoring and logging .

This function calls bdr.run_on_all_nodes() .

bdr.monitor_local_replslots¶

This function uses replication slot status information returned from view pg_replication_slots (slot active or inactive) to provide a local check considering all replication slots, except the BDR group slots.

Synopsis¶

bdr.monitor_local_replslots()

Notes¶

This function returns a record with fields status and message , as explained in Monitoring Replication Slots .

bdr.wal_sender_stats¶

If the Decoding Worker is enabled, this function shows information about the decoder slot and current LCR (Logical Change Record ) segment file being read by each WAL sender.

Synopsis¶

bdr.wal_sender_stats() &rarr; setof record (pid integer, is_using_lcr boolean,  decoder_slot_name TEXT, lcr_file_name TEXT)

Output columns¶

pid - PID of the WAL sender (corresponds to pg_stat_replication ’s pid column)
is_using_lcr - Whether the WAL sender is sending LCR files. The next columns will be NULL if is_using_lcr is FALSE .
decoder_slot_name - The name of the decoder replication slot.
lcr_file_name - The name of the current LCR file.

bdr.get_decoding_worker_stat¶

If the Decoding Worker is enabled, this function shows information about the state of the Decoding Worker associated with the current database. This also provides more granular information about Decoding Worker progress than is available via pg_replication_slots .

Synopsis¶

bdr.get_decoding_worker_stat()  &rarr; setof record (pid integer, decoded_upto_lsn pg_lsn, waiting BOOL, waiting_for_lsn pg_lsn)

Output columns¶

pid - the PID of the Decoding Worker (corresponds to the column active_pid in pg_replication_slots )
decoded_upto_lsn - LSN upto which the Decoding Worker has read transactional logs
waiting - whether the Decoding Worker is waiting for new WAL
waiting_for_lsn - the LSN of the next expected WAL

Notes¶

For further details see Monitoring WAL senders using LCR .

bdr.lag_control¶

If Lag control is enabled, this function shows information about the commit delay and number of nodes conforming to their configured lag measure for the local node and current database.

Synopsis¶

bdr.lag_control()

Output columns¶

commit_delay - current runtime commit delay in fractional milliseconds
commit_delay_maximum - configured maximum commit delay in fractional milliseconds
commit_delay_adjustment - Change to runtime commit delay possible during a sample interval in fractional milliseconds
conforming_nodes - current runtime number of nodes conforming to lag measures
conforming_nodes_minimum - configured minimum number of nodes required t conform to lag measures, below which a commit delay adjustment is applied
lag_bytes_threshold - lag size at which a commit delay is applied in kilobytes
lag_bytes_maximum - configured maximum lag size in kilobytes
lag_time_threshold - lag time at which a commit delay is applied in milliseconds
lag_time_maximum - configured maximum lag time in milliseconds
sample_interval - configured minimum time between lag samples and possible commit delay adjustments in milliseconds

Internal Functions¶

BDR Message Payload Functions¶

bdr.decode_message_response_payload and bdr.decode_message_payload

These functions decode the consensus payloads to a more human-readable output.

Used primarily by the bdr.global_consensus_journal_details debug view.

bdr.get_global_locks¶

This function shows information about global locks held on the local node.

Used to implement the bdr.global_locks view, to provide a more detailed overview of the locks.

bdr.get_slot_flush_timestamp¶

Retrieves the timestamp of the last flush position confirmation for a given replication slot.

Used internally to implement the bdr.node_slots view.

BDR Internal Function Replication Functions¶

bdr.internal_alter_sequence_set_kind, internal_replication_set_add_table, internal_replication_set_remove_table

Functions used internally for replication of the various function calls.

No longer used by the current version of BDR. Only exists for backwards compatibility during rolling upgrades.

bdr.internal_submit_join_request¶

Submits a consensus request for joining a new node.

Needed by the BDR group reconfiguration internal mechanisms.

bdr.isolation_test_session_is_blocked¶

A helper function, extending (and actually invoking) the original pg_isolation_test_session_is_blocked with an additional check for blocks on global locks.

Used for isolation/concurrency tests.

bdr.local_node_info¶

This function displays information for the local node, needed by the BDR group reconfiguration internal mechanisms.

The view bdr.local_node_summary provides similar information useful for user consumption.

bdr.msgb_connect¶

Function for connecting to the connection pooler of another node, used by the consensus protocol.

bdr.msgb_deliver_message¶

Function for sending messages to another node’s connection pooler, used by the consensus protocol.

bdr.peer_state_name¶

This function transforms the node state (node_state ) into a textual representation, and is used mainly to implement the bdr.node_summary view.

bdr.request_replay_progress_update¶

Requests the immediate writing of a ‘replay progress update’ Raft message. It is used mainly for test purposes, but can be also used to test if the consensus mechanism is working.

bdr.seq_nextval¶

Internal implementation of sequence increments.

This function will be used instead of standard nextval in queries which interact with BDR Global Sequences .

Notes¶

The following are also internal BDR sequence manipulation functions. bdr.seq_currval and bdr.sql_lastval are used automatically.

bdr.show_subscription_status¶

Retrieves information about the subscription status, and is used mainly to implement the bdr.subscription_summary view.

bdr.conflict_resolution_to_string¶

Transforms the conflict resolution from oid to text.

The view bdr.conflict_history_summary uses this to give user-friendly information for the conflict resolution.

bdr.conflict_type_to_string¶

Transforms the conflict type from oid to text.

The view bdr.conflict_history_summary uses this to give user-friendly information for the conflict resolution.

bdr.get_node_conflict_resolvers¶

Display a text string of all the conflict resolvers on the local node.

bdr.reset_subscription_stats¶

Returns a boolean result after resetting the statistics created by subscriptions, as viewed by bdr.stat_subscription.

bdr.reset_relation_stats¶

Returns a boolean result after resetting the relation stats, as viewed by bdr.stat_relation.

bdr.pg_xact_origin¶

Return origin id of a given transaction.

Synopsis¶

bdr.pg_xact_origin(xmin xid)

Parameters¶

xid - Transaction id whose origin is returned

bdr.difference_fix_origin_create¶

Creates a replication origin with a given name passed as argument, but adding a bdr_ prefix. It returns the internal id of the origin. This performs same functionality as pg_replication_origin_create() , except this requires bdr_superuser rather than postgres superuser permissions.

Synopsis¶

bdr.difference_fix_session_setup¶

Marks the current session as replaying from the current origin. The function uses the pre-created bdr_local_only_origin local replication origin implicitly for the session. It allows replay progress to be reported. It returns void. This performs the same functionality as pg_replication_origin_session_setup() , except that this requires bdr_superuser rather than postgres superuser permissions. Note that the earlier form of the function: bdr.difference_fix_session_setup(text) has been deprecated and will be removed in upcoming releases.

Synopsis¶

bdr.difference_fix_session_setup()

bdr.difference_fix_session_reset¶

Marks the current session as not replaying from any origin, essentially resetting the effect of bdr.difference_fix_session_setup() . It returns void. This performs the same functionality as pg_replication_origin_session_reset() , except this requires bdr_superuser rather than postgres superuser permissions.

Synopsis¶

bdr.difference_fix_session_reset()

bdr.difference_fix_xact_set_avoid_conflict¶

Marks the current transaction as replaying a transaction that has committed at LSN ‘0/0’ and timestamp ‘2000-01-01’. This performs the same functionality as pg_replication_origin_xact_setup('0/0', '2000-01-01') , except this requires bdr_superuser rather than postgres superuser permissions.

Synopsis¶

bdr.difference_fix_xact_set_avoid_conflict()

bdr.resynchronize_table_from_node(node_name name, relation regclass)¶

Resynchronizes the relation from a remote node.

Synopsis¶

bdr.resynchronize_table_from_node(node_name name, relation regclass)

Parameters¶

node_name - the node from which to copy/resync the relation data.
relation - the relation to be copied from the remote node.

Notes¶

This acquires a global DML lock on the relation, truncates the relation locally, and copies data into it from the remote node.

The relation must exist on both nodes with the same name and definition.

Resynchronization of partitioned tables with identical partition definitions, resynchronization partitioned table to non-partitioned table and vice-versa and resynchronization of referenced tables by temporarily dropping and recreating foreign key constraints are all supported.

After running the function on a referenced table, if the referenced column data no longer matches the referencing column values, it throws an error and function should be rerun after resynchronizing the referencing table data.

Furthermore, it supports resynchronization of table with generated columns, by computing the generated column values locally, after copying the data from remote node.

Currently, row_filters are ignored by this function.

The bdr.resynchronize_table_from_node function can be only executed by the owner of the table, provided the owner has bdr_superuser privileges.

bdr.consensus_kv_store¶

Stores value in the consistent KV Store.

Returns timestamp of the value expiration time. This depends on ttl , if ttl is NULL , then this will return infinity if the value was deleted it will return -infinity .

Synopsis¶

bdr.consensus_kv_store(key text, value jsonb,
        prev_value jsonb DEFAULT NULL, ttl int DEFAULT NULL)

Parameters¶

key - an arbitrary unique key to insert, update or delete.
value - json value to store, if NULL, any existing record will be deleted
prev_value - if set, the write operation is only done if the current value is equal to prev_value .
ttl - time to live of the new value, in milliseconds.

Notes¶

This is an internal function, mainly used by HARP.

Warning

This function should never be used by user applications.

bdr.consensus_kv_fetch¶

Fetch value from the consistent KV Store in json format.

Synopsis¶

bdr.consensus_kv_fetch(IN key text) RETURNS jsonb

Parameters¶

key - an arbitrary key to fetch.

Notes¶

This is an internal function, mainly used by HARP.

Warning

This function should never be used by user applications.

bdr.alter_subscription_skip_changes_upto¶

Because logical replication can replicate across versions, doesn’t replicate global changes like roles, and can replicate selectively, sometimes the logical replication apply process can encounter an error and stop applying changes.

Wherever possible such problems should be fixed by making changes to the target side. CREATE ing any missing table that’s blocking replication, CREATE a needed role, GRANT a necessary permission, etc. But occasionally a problem can’t be fixed that way and it may be necessary to skip entirely over a transaction. Changes are skipped as entire transactions, all or nothing. To decide where to skip to, use log output to find the commit LSN, per the example below, or peek the change stream with the logical decoding functions.

Unless a transaction only made one change, it’s often necessary to manually apply the transaction’s effects on the target side, so it’s important to save the problem transaction whenever possible. See the example below.

It’s possible to skip over changes without bdr.alter_subscription_skip_changes_upto by using pg_catalog.pg_logical_slot_get_binary_changes to skip to the LSN of interest, so this is really a convenience function. It does do a faster skip; however, it may bypass some kinds of errors in logical decoding.

This function only works on disabled subscriptions.

The usual sequence of steps is:

identify the problem subscription and LSN of the problem commit
disable the subscription
save a copy of the transaction(s) using pg_catalog.pg_logical_slot_peek_changes on the source node (if possible)
bdr.alter_subscription_skip_changes_upto on the target node
apply repaired or equivalent changes on the target manually, if necessary
re-enable the subscription

Warning

It’s easy to make problems worse when using this function. Don’t do anything unless you’re really, really sure it’s the only option.

Synopsis¶

bdr.alter_subscription_skip_changes_upto(
  subname text,
  skip_upto_and_including pg_lsn
);

Example¶

Apply of a transaction is failing with an ERROR, and you’ve determined that lower-impact fixes such as changes on the target side will not resolve this issue. You determine that you must skip the transaction.

In the error logs, find the commit record LSN to skip to, as in this artificial example:

ERROR:  XX000: CONFLICT: target_table_missing; resolver skip_if_recently_dropped returned an error: table does not exist
CONTEXT:  during apply of INSERT from remote relation public.break_me in xact with commit-end lsn 0/300AC18 xid 131315
committs 2021-02-02 15:11:03.913792+01 (action #2) (effective sess origin id=2 lsn=0/300AC18)
while consuming I message from receiver for subscription bdr_regression_bdrgroup_node1_node2 (id=2667578509)
on node node2 (id=3367056606) from upstream node node1 (id=1148549230, reporiginid=2)

In this portion of log we have the information we need: the_target_lsn: 0/300AC18 the_subscription: bdr_regression_bdrgroup_node1_node2

Next, disable the subscription so the apply worker doesn’t try to connect to the replication slot:

SELECT bdr.alter_subscription_disable(the_subscription);

Note that you cannot skip only parts of the transaction, it’s all or nothing. So it’s strongly recommended that you save a record of it by COPY ing it out on the provider side first, using the subscription’s slot name.

\copy (SELECT * FROM pg_catalog.pg_logical_slot_peek_changes(the_slot_name,
    the_target_lsn, NULL, min_proto_version, 1, max_proto_version, 1,
    startup_params_format, 1, proto_format, json))
 TO transaction_to_drop.csv WITH (FORMAT csv);

Note that the example is broken into multiple lines for readability, but it should be issued in a single line because \copy does not support multi-line commands.

Now you can skip the change by changing “peek” to “get” above, but bdr....skip_changes_upto does a faster skip that avoids decoding and outputting all the data:

SELECT bdr.alter_subscription_skip_changes_upto(subscription_name,
    the_target_lsn);

If necessary or desired, apply the same changes (or repaired versions of them) manually to the target node, using the dumped transaction contents as a guide.

Finally, re-enable the subscription:

SELECT bdr.alter_subscription_enable(the_subscription);