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 thesefunctions should be schema-qualified, rather than putting bdr in thesearch_path.

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

Version Information Functions¶

bdr.bdr_version¶

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

bdr.bdr_version_num¶

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

The following formula is used to turn the version number consisting ofmajor version, minor verion and patch release into a single numericalvalue:

MAJOR_VERSION * 10000 + MINOR_VERSION * 100 + PATCH_RELEASE

System and Progress Information Parameters¶

BDR exposes some parameters that can be queried via SHOW in psqlor using PQparameterStatus (or equivalent) from a clientapplication. 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 isconnected to. This allows an application to figure out what node itis connected to even behind a transparent proxy.

It is also used in combination with CAMO, see the[CAMO.md#connection-pools-and-proxies](Connection pools and proxies)section.

bdr.last_committed_lsn¶

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

transaction_id¶

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

!!! Note * This is only available on EDB Postgres Extended.

Utility Functions¶

bdr.wait_slot_confirm_lsn¶

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

Waits until a slot passes certain LSN. If no position is supplied, thecurrent 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 forthe local application of certain transactions originating from a givenBDR node. It will return only after all transactions from that peernode are applied locally. An application or a proxy can use thisfunction to prevent stale reads.

For convenience, BDR provides a special variant of this function forCAMO and the CAMO partner node, seebdr.wait_for_camo_partner_queue.

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

If the given target_lsn is NULL, this function checks the localreceive buffer and uses the LSN of the last transaction received fromthe given peer node. Effectively waiting for all transactions alreadyreceived to be applied. This is especially useful in case the peernode has failed and it’s not known which transactions have been sent.Note that in this case, transactions that are still in transit orbuffered 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 hasbeen received from the given origin. Either filtered to take intoaccount only relevant LSN increments for transactions to be applied orunfiltered.

The difference between the output of this function and the output ofbdr.get_node_sub_apply_lsn() measures the size of the correspondingapply 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 hasbeen 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, returninga result from each of them in json format. Multiple rows may be returned fromeach node, encoded as a json array. Any errors, such as being unable toconnect because a node is down, will be shown in the response field.No explicit statement_timeout or other runtime parameters are set, sodefaults will be used.

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

Be careful when using this function since you risk breaking replicationand causing inconsistencies between nodes. Use either transparent DDLreplication or bdr.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 followingquery:

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, returninga result from each of them in json format. Multiple rows may be returned fromeach node, encoded as a json array. Any errors, such as being unable toconnect because a node is down, will be shown in the response field.No explicit statement_timeout or other runtime parameters are set, sodefaults will be used.

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

Be careful when using this function since you risk breaking replicationand causing inconsistencies between nodes. Use either transparent DDLreplication or bdr.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, returninga result from each of them in json format. Multiple rows may be returned fromeach node, encoded as a json array. Any errors, such as being unable toconnect because a node is down, will be shown in the response field.No explicit statement_timeout or other runtime parameters are set, sodefaults will be used.

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

Be careful when using this function since you risk breaking replicationand causing inconsistencies between nodes. Use either transparent DDLreplication or bdr.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 informationabout 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 theddl_locking setting.

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

bdr.wait_for_xid_progress¶

This function can be used to wait for the given transaction (identifiedby 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 thetransaction being applied on a node and this node having seen allother 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 havereplicated a DDL command because only those transactions are trackedcurrently. 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 whatwas passed to bdr.create_node_group() when the node group was created,except normal is returned if the node_group_type was passed as NULLwhen 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 theadvisory locks supported by BDR are global in nature. They follow semanticssimilar to DDL locks. So an advisory lock is obtained by majority consensus andhence can be used even if one or more nodes are down or lagging behind, as longas a majority of all nodes can work together.

Currently we only support EXCLUSIVE locks. So if another node or anotherbackend 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 releasedwhen the transaction ends unless it is explicitly released before the end ofthe 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 threetimes 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 isnot available, then it will wait until the lock becomes available or thebdr.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 definedsource. The lock must have been previously obtained in the same transaction bythe 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.