Logical Replication Functions

Description: Creates a logical replication slot.

Parameter:

• slot_name

  Indicates the name of the streaming replication slot.

  Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

• plugin_name

  Indicates the name of the plug-in.

  Value range: a string, supporting mppdb_decoding

• output_order

  Indicates the output sequence of the replication slot decoding results. This parameter is optional.

  Valid value: 0 or 1. The default value is 0 on DNs and 1 on CNs.

  • 0: The replication slot decoding results are sorted by transaction COMMIT LSN. In this case, the value of confirmed_csn of the replication slot is 0. This replication slot is called an LSN-based replication slot.
  • 1: The replication slot decoding results are sorted by transaction CSN. In this case, the value of confirmed_csn of the replication slot is not 0. This replication slot is called a CSN-based replication slot. In this case, the confirmed_flush value is meaningless.

Return type: name, text

gaussdb=# SELECT * FROM pg_create_logical_replication_slot('slot_lsn','mppdb_decoding',0);
 slotname | xlog_position
----------+---------------
 slot_lsn | 0/6D08B58
(1 row)

gaussdb=# SELECT * FROM pg_create_logical_replication_slot('slot_csn','mppdb_decoding',1);
 slotname | xlog_position
----------+---------------
 slot_csn | 0/59AD800
(1 row)

Note: The first return value is the slot name, and the second one has different meanings in LSN-based replication slots and CSN-based replication slots. For an LSN-based replication slot, the value is confirmed_flush of the replication slot, indicating that transactions whose commit LSN is less than or equal to the value will not be decoded and output. For a CSN-based replication slot, the value is confirmed_csn of the replication slot, indicating that transactions whose CSN is less than or equal to the value will not be decoded and output. Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication. Currently, this function can be called only on the CN or primary DNs.

When this function is executed on a CN, if output_order is set to 1, a replication slot with the same name will be created on all CNs and primary DNs. If a replication slot with the same name already exists on some DNs, a CSN-based logical replication slot will be created on other CNs and primary DNs that do not have a replication slot with the same name. In addition, an error message is displayed, indicating that the replication slot already exists. In this case, you need to delete the existing replication slot with the same name on the DN (if the replication slot is an LSN-based logical replication slot, you need to manually delete it on the corresponding node) and create a replication slot on the CN. If you set output_order to 0 when creating a replication slot on a CN, no replication slot will be created on DNs. Replication slots created on CNs are only used to identify whether related replication slots exist on DNs.

Description: Creates a physical replication slot.

Parameter:

• slot_name

  Indicates the name of the streaming replication slot.

  Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

• isDummyStandby

  Reserved parameter.

  Type: Boolean

Return type: name, text

• Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.
• The physical replication slot created by this function does not have restart_lsn. Therefore, the slot is considered invalid and will be automatically deleted when the checkpoint is performed.

Description: Deletes a streaming replication slot.

Parameter:

• slot_name

  Indicates the name of the streaming replication slot.

  Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

Return type: void

• Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.
• When a database is deleted, the logical replication slots on the database are not deleted. You need to manually delete the logical replication slots on the database. Otherwise, historical versions of Xlogs and system catalogs cannot be reclaimed.
• When a logical replication slot is deleted from a CN, if the logical replication slot is an LSN-based logical replication slot, only the replication slot of the current node is deleted. Replication slots with the same name on other nodes are not affected. When a CSN-based logical replication slot with the same name exists on other nodes, no error is reported because some nodes do not have replication slots. In addition, replication slots with the same name on all nodes are successfully deleted. If no replication slot exists on any node, an error is reported.
• If an LSN-based logical replication slot remains on the current CN and a CSN-based logical replication slot with the same name remains on other nodes, deleting the replication slot on the current CN will delete only the local LSN-based logical replication slot. After the deletion is complete, perform the deletion operation again to delete the CSN-based logical replication slots with the same name on other nodes.

Description: Performs decoding but does not go to the next streaming replication slot. (The decoded result will be returned again during the next decoding.)

Parameter:

• slot_name

  Indicates the name of the streaming replication slot.

  Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

• upto_lsn

  For the CSN-based logical replication slot, the decoding is complete until the transaction whose CSN is less than or equal to the value is decoded (a transaction whose CSN is greater than the specified CSN may be decoded). For the LSN-based replication slot, the decoding is complete until the first transaction whose COMMIT LSN is greater than or equal to the value is decoded.

  Value range: a string, for example, '1/2AAFC60', '0/A060', or '3A/0' (a hexadecimal uint64 value containing two uint32 values separated by a slash (/); if any uint32 value is 0, 0 is displayed.) NULL indicates that the end position of decoding is not specified.

• upto_nchanges

  Indicates the number of decoded records (including the begin and commit timestamps). Assume that there are three transactions, which involve 3, 5, and 7 records, respectively. If upto_nchanges is set to 4, 8 records of the first two transactions will be decoded. Specifically, decoding is stopped when the number of decoded records exceeds the value of upto_nchanges after decoding in the first two transactions is finished.

  Value range: a non-negative integer

  

  If any of the upto_lsn and upto_nchanges values is reached, decoding ends.

• options: Specifies optional parameters, consisting of options_name and options_value.
  • include-xids

    Specifies whether the decoded data column contains XID information.

    Value range: Boolean. The default value is true.

    • false: The decoded data column does not contain XID information.
    • true: The decoded data column contains XID information.
  • skip-empty-xacts

    Specifies whether to ignore empty transaction information during decoding.

    Value range: Boolean. The default value is false.

    • false: The empty transaction information is not ignored during decoding.
    • true: The empty transaction information is ignored during decoding.
  • include-timestamp

    Specifies whether decoded information contains the commit timestamp.

    Value range: Boolean. The default value is true.

    • false: The decoded information does not contain the commit timestamp.
    • true: The decoded information contains the commit timestamp.
  • only-local

    Specifies whether to decode only local logs.

    Value range: Boolean. The default value is true.

    • false: Non-local logs and local logs are decoded.
    • true: Only local logs are decoded.
  • force-binary

    Specifies whether to output the decoding result in binary format.

    Value range: Boolean. The default value is false.

    • false: The decoding result is output in text format.
    • The value cannot be set to true currently.
  • white-table-list

    Whitelist parameter, including the schema and table name to be decoded.

    Value range: a string that contains table names in the whitelist. Different tables are separated by commas (,). An asterisk (*) is used to fuzzily match all tables. Schema names and table names are separated by periods (.). No space character is allowed. The following is an example:

    SELECT * FROM pg_logical_slot_peek_changes('slot1', NULL, 4096, 'white-table-list', 'public.t1,public.t2');

  • max-txn-in-memory

    Memory control parameter. The unit is MB. If the memory occupied by a single transaction is greater than the value of this parameter, data is flushed to disks.

    Value range: an integer ranging from 0 to 100. The default value is 0, indicating that memory control is disabled.

  • max-reorderbuffer-in-memory

    Memory control parameter. The unit is GB. If the total memory (including the cache) of transactions being concatenated in the sender thread is greater than the value of this parameter, the current decoding transaction is flushed to disks.

    Value range: an integer ranging from 0 to 100. The default value is 0, indicating that memory control is disabled.

  • include-user

    Specifies whether the BEGIN logical log of a transaction records the username of the transaction.

    Value range: Boolean. The default value is false.

    • false: The BEGIN logical log of a transaction does not record the username of the transaction.
    • true: The BEGIN logical log of a transaction records the username of the transaction.
  • exclude-userids

    Specifies the OID of a blacklisted user.

    Value range: OIDs of blacklisted users. Multiple OIDs are separated by commas (,). The system does not check whether the OIDs exist.

  • exclude-users

    Specifies the name of a blacklisted user.

    Value range: names of blacklisted users. Multiple names are separated by commas (,). dynamic-resolution specifies whether to dynamically parse and identify usernames. If the decoding is interrupted because the user does not exist and the corresponding blacklisted user does not exist at the time when logs are generated, you can set dynamic-resolution to true or delete the username from the blacklist to start decoding and continue to obtain logical logs.

  • dynamic-resolution

    Specifies whether to dynamically parse the names of blacklisted users.

    Value range: Boolean. The default value is true.

    • false: An error is reported and the logical decoding exits when the decoding detects that a user does not exist in blacklist exclude-users.
    • true: Decoding continues when it detects that a user does not exist in blacklist exclude-users.

For details about other configuration options, see "Logical Replication > Logical Decoding > Logical Decoding Options" in the Feature Guide.

Return type: text, xid, text

gaussdb=# SELECT * FROM pg_logical_slot_peek_changes('slot_lsn',NULL,4096,'skip-empty-xacts','on');
 location  |  xid  |                                                                                            data
-----------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 0/6D0B500 | 46914 | BEGIN 46914
 0/6D0B530 | 46914 | {"table_name":"public.t1","op_type":"INSERT","columns_name":["a","b"],"columns_type":["integer","integer"],"columns_val":["3","1"],"old_keys_name":[],"old_keys_type":[],"old_keys_val":[]}
 0/6D0B8B8 | 46914 | COMMIT 46914 (at 2023-02-22 17:29:31.090018+08) CSN 94034528
 0/6D0BB58 | 46915 | BEGIN 46915
 0/6D0BB88 | 46915 | {"table_name":"public.t1","op_type":"INSERT","columns_name":["a","b"],"columns_type":["integer","integer"],"columns_val":["3","2"],"old_keys_name":[],"old_keys_type":[],"old_keys_val":[]}
 0/6D0BF08 | 46915 | COMMIT 46915 (at 2023-02-22 17:31:30.672093+08) CSN 94034568
 0/6D0BF08 | 46916 | BEGIN 46916
 0/6D0BF38 | 46916 | {"table_name":"public.t1","op_type":"INSERT","columns_name":["a","b"],"columns_type":["integer","integer"],"columns_val":["3","3"],"old_keys_name":[],"old_keys_type":[],"old_keys_val":[]}
 0/6D0C218 | 46916 | COMMIT 46916 (at 2023-02-22 17:31:34.438319+08) CSN 94034570
(9 rows)

gaussdb=# SELECT * FROM pg_logical_slot_peek_changes('slot_csn',NULL,4096,'skip-empty-xacts','on');
 location  |  xid  |                                                                                            data
-----------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 0/0       | 46914 | BEGIN CSN: 94034528
 0/0       | 46914 | {"table_name":"public.t1","op_type":"INSERT","columns_name":["a","b"],"columns_type":["integer","integer"],"columns_val":["3","1"],"old_keys_name":[],"old_keys_type":[],"old_keys_val":[]}
 0/59ADA60 | 46914 | COMMIT 46914 (at 2023-02-22 17:29:31.090018+08) CSN 94034528
 0/59ADA60 | 46915 | BEGIN CSN: 94034568
 0/59ADA60 | 46915 | {"table_name":"public.t1","op_type":"INSERT","columns_name":["a","b"],"columns_type":["integer","integer"],"columns_val":["3","2"],"old_keys_name":[],"old_keys_type":[],"old_keys_val":[]}
 0/59ADA88 | 46915 | COMMIT 46915 (at 2023-02-22 17:31:30.672093+08) CSN 94034568
 0/59ADA88 | 46916 | BEGIN CSN: 94034570
 0/59ADA88 | 46916 | {"table_name":"public.t1","op_type":"INSERT","columns_name":["a","b"],"columns_type":["integer","integer"],"columns_val":["3","3"],"old_keys_name":[],"old_keys_type":[],"old_keys_val":[]}
 0/59ADA8A | 46916 | COMMIT 46916 (at 2023-02-22 17:31:34.438319+08) CSN 94034570
(9 rows)

Note: The decoding result returned by the function contains three columns, corresponding to the preceding return value types, which are the LSN (for an LSN-based replication slot) or CSN (for a CSN-based replication slot), XID, and decoded content, respectively. If the location column indicates the CSN, the value of the location column is updated only when the commit logs are decoded. Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.

On the CN, this function cannot be executed in a CSN-based replication slot (confirmed_csn is not 0).

Description: Performs decoding and goes to the next streaming replication slot.

Parameter: This function has the same parameters as pg_logical_slot_peek_changes. For details, see •pg_logical_slot_peek_changes.

Note: Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.

On the CN, this function cannot be executed in a CSN-based replication slot (confirmed_csn is not 0). When this function is called on the standby DN, the corresponding logical replication slot on the primary DN is updated synchronously. If this function is executed on the standby DN, a WAL sender of the primary DN is occupied when the replication slot number on the primary DN is updated. The logical decoding function reserves a WAL sender for each logical replication slot. Therefore, if this function is executed in normal scenarios, the logical replication slot number on the primary DN is updated normally. If this function is executed continuously in a short period of time, the primary DN fails to update the slot number and no error is reported.

Description: Performs decoding in binary mode and does not go to the next streaming replication slot. (The decoded data can be obtained again during the next decoding.)

Parameter:

• slot_name

  Indicates the name of the streaming replication slot.

  Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

• upto_lsn

  For the CSN-based logical replication slot, the decoding is complete until the transaction whose CSN is less than or equal to the value is decoded (a transaction whose CSN is greater than the specified CSN may be decoded). For the LSN-based replication slot, the decoding is complete until the first transaction whose COMMIT LSN is greater than or equal to the value is decoded.

  Value range: a string, for example, '1/2AAFC60', '0/A060', or '3A/0' (a hexadecimal uint64 value containing two uint32 values separated by a slash (/); if any uint32 value is 0, 0 is displayed.) NULL indicates that the end position of decoding is not specified.

• upto_nchanges

  Indicates the number of decoded records (including the begin and commit timestamps). Assume that there are three transactions, which involve 3, 5, and 7 records, respectively. If upto_nchanges is set to 4, 8 records of the first two transactions will be decoded. Specifically, decoding is stopped when the number of decoded records exceeds the value of upto_nchanges after decoding in the first two transactions is finished.

  Value range: a non-negative integer

  

  If any of the upto_lsn and upto_nchanges values is reached, decoding ends.

• options: Specifies optional parameters, consisting of options_name and options_value.
  • include-xids

    Specifies whether the decoded data column contains XID information.

    Value range: Boolean. The default value is true.

    • false: The decoded data column does not contain XID information.
    • true: The decoded data column contains XID information.
  • skip-empty-xacts

    Specifies whether to ignore empty transaction information during decoding.

    Value range: Boolean. The default value is false.

    • false: The empty transaction information is not ignored during decoding.
    • true: The empty transaction information is ignored during decoding.
  • include-timestamp

    Specifies whether decoded information contains the commit timestamp.

    Value range: Boolean. The default value is true.

    • false: The decoded information does not contain the commit timestamp.
    • true: The decoded information contains the commit timestamp.
  • only-local

    Specifies whether to decode only local logs.

    Value range: Boolean. The default value is true.

    • false: Non-local logs and local logs are decoded.
    • true: Only local logs are decoded.
  • force-binary

    This parameter is useless for the function.

    Value range: Boolean. The result is output in binary format.

  • white-table-list

    Whitelist parameter, including the schema and table name to be decoded.

    Value range: a string that contains table names in the whitelist. Different tables are separated by commas (,). An asterisk (*) is used to fuzzily match all tables. Schema names and table names are separated by periods (.). No space character is allowed. Example: select * from pg_logical_slot_peek_binary_changes('slot1', NULL, 4096, 'white-table-list', 'public.t1,public.t2');

  • max-txn-in-memory

    Memory control parameter. The unit is MB. If the memory occupied by a single transaction is greater than the value of this parameter, data is flushed to disks.

    Value range: an integer ranging from 0 to 100. The default value is 0, indicating that memory control is disabled.

  • max-reorderbuffer-in-memory

    Memory control parameter. The unit is GB. If the total memory (including the cache) of transactions being concatenated in the sender thread is greater than the value of this parameter, the current decoding transaction is flushed to disks.

    Value range: an integer ranging from 0 to 100. The default value is 0, indicating that memory control is disabled.

  • include-user

    Specifies whether the BEGIN logical log of a transaction records the username of the transaction.

    Value range: Boolean. The default value is false.

    • false: The BEGIN logical log of a transaction does not record the username of the transaction.
    • true: The BEGIN logical log of a transaction records the username of the transaction.
  • exclude-userids

    Specifies the OID of a blacklisted user.

    Value range: OIDs of blacklisted users. Multiple OIDs are separated by commas (,). The system does not check whether the OIDs exist.

  • exclude-users

    Specifies the name of a blacklisted user.

    Value range: names of blacklisted users. Multiple names are separated by commas (,). dynamic-resolution specifies whether to dynamically parse and identify usernames. If the decoding is interrupted because the user does not exist and the corresponding blacklisted user does not exist at the time when logs are generated, you can set dynamic-resolution to true or delete the username from the blacklist to start decoding and continue to obtain logical logs.

  • dynamic-resolution

    Specifies whether to dynamically parse the names of blacklisted users.

    Value range: Boolean. The default value is true.

    • false: An error is reported and the logical decoding exits when the decoding detects that a user does not exist in blacklist exclude-users.
    • true: Decoding continues when it detects that a user does not exist in blacklist exclude-users.

Some configuration options do not take effect even if they are configured in functions. For details, see "Logical Replication > Logical Decoding > Logical Decoding Options" in the Feature Guide.

Return type: text, xid, bytea

Note: The function returns the decoding result. Each decoding result contains three columns, corresponding to the above return types and indicating the LSN position, XID, and decoded content in binary format, respectively. Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.

On the CN, this function cannot be executed in a CSN-based replication slot (confirmed_csn is not 0).

Description: Performs decoding in binary mode and does not go to the next streaming replication slot.

Parameter: This function has the same parameters as pg_logical_slot_peek_binary_changes. For details, see •pg_logical_slot_peek_bi...

Note: Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.

On the CN, this function cannot be executed in a CSN-based replication slot (confirmed_csn is not 0). This function cannot be executed on the standby DN.

Description: Directly goes to the streaming replication slot for a specified CSN (for CSN-based replication slots) or LSN (for LSN-based replication slots), without outputting any decoded result.

Parameter:

• slot_name

  Indicates the name of the streaming replication slot.

  Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

• upto_lsn

  For the CSN-based logical replication slot, it indicates the target CSN before which logs are decoded. During the next decoding, only the transaction results whose CSN is greater than this value will be output. If the input CSN is smaller than the value of confirmed_csn recorded in the current streaming replication slot, the function directly returns the decoded result. If the input CSN is greater than the latest CSN that can be obtained, the latest CSN will be used for decoding.

  For the LSN-based logical replication slot, it indicates the target LSN before which logs are decoded. During the next decoding, only the transaction results whose LSN is greater than this value will be output. If an input LSN is smaller than the position recorded in the current streaming replication slot, an error is reported. If the input LSN is greater than the LSN of the current physical log, the latter LSN will be directly used for decoding.

  Value range: a string, for example, '1/2AAFC60', '0/A060', or '3A/0' (a hexadecimal uint64 value containing two uint32 values separated by a slash (/); if any uint32 value is 0, 0 is displayed.) NULL indicates that the end position of decoding is not specified.

Return type: name, text

• The return value corresponds to slot_name and the actual position (CSN or LSN). Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit permissions of the built-in role gs_role_replication.
• On the CN, this function cannot be executed in a CSN-based replication slot (confirmed_csn is not 0). This function can be executed only on the logical replication slot on the standby DN and the corresponding logical replication slot on the primary DN is updated synchronously. If this function is executed on the standby DN, a WAL sender of the primary DN is occupied when the replication slot number on the primary DN is updated. The logical decoding function reserves a WAL sender for each logical replication slot. Therefore, if this function is executed in normal scenarios, the logical replication slot number on the primary DN is updated normally. If this function is executed continuously in a short period of time, the primary DN fails to update the slot number and no error is reported.

Description: Obtains the replication slot list.

Run the following command on the DN:
gaussdb=# SELECT * FROM pg_get_replication_slots();
 slot_name |     plugin     | slot_type | datoid | active | xmin | catalog_xmin | restart_lsn | dummy_standby | confirmed_flush | confirmed_csn
-----------+----------------+-----------+--------+--------+------+--------------+-------------+---------------+-----------------+---------------
 dn_6002   |                | physical  |      0 | t      |      |              | 0/3622B528  | f             |                 |
 dn_6003   |                | physical  |      0 | t      |      |              | 0/3622B528  | f             |                 |
 slot_lsn  | mppdb_decoding | logical   | 131072 | f      |      |        66658 | 0/36252350  | f             | 0/362523D0      |
 slot_test | mppdb_decoding | logical   | 131072 | f      |      |        66658 | 0/36251718  | f             |                 |      10025527
(4 rows)
Run the following command on the CN:
gaussdb=# SELECT * FROM pg_get_replication_slots();
 slot_name |     plugin     | slot_type | datoid | active | xmin | catalog_xmin | restart_lsn | dummy_standby | confirmed_flush | confirmed_csn
-----------+----------------+-----------+--------+--------+------+--------------+-------------+---------------+-----------------+---------------
 slot_test | mppdb_decoding | logical   | 139264 | f      |      |              |             | f             |                 |
(1 row)

Return type: text, text, text, oid, Boolean, xid, xid, text, Boolean, text, xid

Note: In the returned value, slot_name indicates the name of the replication slot, plugin indicates the name of the output plug-in corresponding to the logical replication slot, slot_type indicates the type of the replication slot (physical indicates the physical replication slot, and logical indicates the logical replication slot), datoid indicates the OID of the database where the replication slot resides, active specifies whether the replication slot is activated (f: not activated; t: activated), xmin indicates the transaction ID of the earliest transaction reserved by the database for the replication slot, catalog_xmin indicates the transaction ID of the earliest transaction related to the system catalog reserved by the database for the logical replication slot, restart_lsn indicates the physical location of the earliest Xlog required by the replication slot, dummy_standby is a reserved parameter, confirmed_csn indicates that the client confirms the CSN corresponding to the last transaction in the received log (This parameter is dedicated to logical replication slots.), and confirmed_flush indicates that the client confirms the location of the received log (dedicated for logical replication slots).

When a query is performed on a DN, the confirmed_csn query result of the LSN-based logical replication slot is empty, and the confirmed_flush query result of the CSN-based logical replication slot is empty. When a query is performed on the CN, catalog_xmin, restart_lsn, confirmed_flush, and confirmed_csn of the CSN-based logical replication slot are not displayed and the query result is empty.

Description: Specifies an LSN range or an Xlog file for decoding when no DDL operation is performed.

The constraints are as follows:

• The current network and hardware environment are normal.
• It is recommended that the size of a single tuple be less than or equal to 500 MB. If the size ranges from 500 MB to 1 GB, an error is reported.
• Data page replication is not supported for data retrieval that does not fall into Xlogs.
• When an API is called, the log level parameter wal_level must be set to logical, and only the log files generated when wal_level is set to logical can be parsed. If the used Xlog file is not at the logical level, the decoded content does not have the corresponding value or type. There is no other impact. If wal_level is not set to logical, an error is reported and decoding is not performed.
• The Xlog file can be parsed only by a copy of a completely homogeneous DN, and no DDL operation or VACUUM FULL occurs in the database to ensure that the metadata corresponding to the data can be found.
• Do not read too many Xlog files at a time. If no file is specified for decoding within a specified range, you are advised to read one Xlog file each time. Generally, the memory occupied by an Xlog file during decoding is about two to three times the size of the Xlog file.
• Data before VACUUM FULL cannot be retrieved.
• The Xlog file before scale-out cannot be decoded.
• To decode the UPDATE statement, the table must have a primary key. Otherwise, the WHERE clause in the UPDATE statement is empty.
• Fields of the TOAST, CLOB, or BLOB type cannot be decoded. If a field of the TOAST, CLOB, or BLOB type is decoded, the field is skipped or an error is reported.
• In this decoding mode, the content that can be decoded is decoded based on the Xlog text record data, and the decoding is not performed based on transactions. Therefore, data that is not in the Xlog cannot be decoded.
• If no decoding file is specified from the decoding point, the system checks whether DDL occurs between the decoding start point and the latest redo value. If DDL occurs, the system does not decode all data. If a decoding file is specified, the system checks whether DDL occurs between the start point of the decoding file and the last readable content of the file and between the start point of the Xlog in the data directory and the latest redo value. If a DDL operation is detected, the system does not decode all tables.
• The CSN-based replication slot is not supported.

Note: When separation-of-duties is enabled, only the initial database user can call the function. When separation-of-duties is disabled, the system administrator permission is required.

Parameter:

• LSN_start

  Specifies the LSN at the start of decoding.

  Value range: a string, in the format of xlogid/xrecoff, for example, '1/2AAFC60'. NULL indicates that the start position of decoding logs is not limited.

• LSN_end

  Specifies the LSN at the end of decoding.

  Value range: a string, in the format of xlogid/xrecoff, for example, '1/2AAFC60'. NULL indicates that the end position of decoding is not specified.

• upto_nchanges

  Indicates the number of decoded records (including the begin and commit timestamps). Assume that there are three transactions, which involve 3, 5, and 7 records, respectively. If upto_nchanges is set to 4, 8 records of the first two transactions will be decoded. Specifically, decoding is stopped when the number of decoded records exceeds the value of upto_nchanges after decoding in the first two transactions is finished.

  Value range: a non-negative integer

  

  If any of the LSN and upto_nchanges values are reached, decoding ends.

• decoding_plugin

Decoding plug-in, which is a .so plug-in that specifies the output format of the decoded content.

Value range: mppdb_decoding and sql_decoding.

• xlog_path

Decoding plug-in, which specifies the Xlog absolute path and file level of the decoding file.

Value range: NULL or a character string of the absolute path of the Xlog file.

• options: This parameter is optional and consists of multiple pairs of options_name and options_value. You can retain the default values.
  • include-xids

    Specifies whether the decoded data column contains XID information.

    Value range: Boolean. The default value is true.

    false: The decoded data column does not contain XID information.

    true: The decoded data column contains XID information.

  • skip-empty-xacts

    Specifies whether to ignore empty transaction information during decoding.

    Value range: Boolean. The default value is false.

    false: The empty transaction information is not ignored during decoding.

    true: The empty transaction information is ignored during decoding.

  • include-timestamp

    Specifies whether decoded information contains the commit timestamp.

    Value range: Boolean. The default value is true.

    false: The decoded information does not contain the commit timestamp.

    true: The decoded information contains the commit timestamp.

  • only-local

    Specifies whether to decode only local logs.

    Value range: Boolean. The default value is true.

    false: Non-local logs and local logs are decoded.

    true: Only local logs are decoded.

  • force-binary

    Specifies whether to output the decoding result in binary format.

    Value range: Boolean. The default value is false.

    false: The decoding result is output in text format.

    The value cannot be set to true currently.

  • white-table-list

    Whitelist parameter, including the schema and table name to be decoded. Value range: a string that contains table names in the whitelist. Different tables are separated by commas (,). An asterisk (*) is used to fuzzily match all tables. Schema names and table names are separated by periods (.). No space character is allowed.

Example:

- Perform DML operations on a CN. You can query the DML operations executed on the current DN.
gaussdb=# SELECT * FROM pg_logical_get_area_changes('0/502E418', NULL, NULL, 'sql_decoding', NULL);
 location  |  xid  |                                  data
-----------+-------+------------------------------------------------------------------------
 0/502E448 | 17365 | insert into public.t1 values (1, 1);
 0/502E5A0 | 17365 | COMMIT 17365 (at 2023-11-01 11:28:43.92526+08) 2010016
 0/502E5D0 | 17366 | delete from public.t1 where a = 1;insert into public.t1 values (1, 2);
 0/502E6D8 | 17366 | COMMIT 17366 (at 2023-11-01 11:28:45.889283+08) 2010017
 0/502E7B8 | 17367 | delete from public.t1 where a = 1;
 0/502E8B0 | 17367 | COMMIT 17367 (at 2023-11-01 11:28:48.301307+08) 2010018
(6 rows)

Description: Monitors the length of the read log queue and decoding result queue of each decoder thread to locate the concurrent decoding performance bottleneck.

Return type: text, int, text, text, text, int64, int64, TimestampTz

Example:

gaussdb=# SELECT * FROM gs_get_parallel_decode_status();
 slot_name | parallel_decode_num | read_change_queue_length  | decode_change_queue_length | reader_lsn | working_txn_cnt | working_txn_memory |      decoded_time
-----------+---------------------+---------------------------+----------------------------+------------+-----------------+--------------------+------------------------
 slot1     |                   2 | queue0: 1005, queue1: 320 | queue0: 63, queue1: 748    | 0/1DCE2578 |              42 |          192927504 | 2023-01-10 11:18:22+08
(1 row)

Note: In the return values, slot_name indicates the replication slot name, parallel_decode_num indicates the number of parallel decoder threads in the replication slot, read_change_queue_length indicates the current length of the log queue read by each decoder thread, decode_change_queue_length indicates the current length of the decoding result queue of each decoder thread, reader_lsn indicates the log location read by the reader thread, working_txn_cnt indicates the number of transactions being concatenated in the current sender thread, working_txn_memory indicates the total memory (in bytes) occupied by the concatenation transactions in the sender thread, and decoded_time indicates the time of the latest WAL decoded by the replication slot.

The value of decoded_time comes from checkpoint logs and transaction commit logs, which has a certain error. If no log containing the time is decoded, "2000-01-01 08:00:00+08" (depending on the time zone set in the database) is displayed.

Description: Queries the time of the latest WAL decoded by a replication slot.

Parameter:

• slot_name

  Specifies the name of the replication slot to be queried.

  Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

Example:

gaussdb=# SELECT * FROM gs_get_slot_decoded_wal_time('replication_slot');
 gs_get_slot_decoded_wal_time
------------------------------
 2023-01-10 11:25:22+08
(1 row)

Note: The returned values indicate the time of the latest WAL decoded by the replication slot.

The returned time comes from checkpoint logs and transaction commit logs, which has a certain error. If no log containing the time is decoded, "2000-01-01 08:00:00+08" (depending on the time zone set in the database) is displayed. When you query the latest decoded WAL time of a logical replication slot that does not exist, NULL is returned. In gsql, the display of NULL is related to the setting, which can be set using \pset null'null'.

Description: Obtains the decoding statistics of a replication slot for parallel logical decoding, including 26 rows of statistical items.

The descriptions of the statistical items are listed in the following table.

Record - (stat_id int, stat_name TEXT, value TEXT)

Parameter:

• slot_name

  Indicates the name of the streaming replication slot.

  Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

Return type: int, text, text

Example:

gaussdb=# SELECT * FROM gs_logical_parallel_decode_status('replication_slot');
 stat_id |          stat_name           |      value
---------+------------------------------+------------------
       1 | slot_name                    | replication_slot
       2 | reader_lsn                   | 0/357E180
       3 | wal_read_total_time          | 266694599
       4 | wal_wait_total_time          | 266691307
       5 | parser_total_time            | 39971
       6 | decoder_total_time           | 81216
       7 | sender_total_time            | 48193
       8 | net_send_total_time          | 19388
       9 | net_wait_total_time          | 0
      10 | net_send_total_bytes         | 266897
      11 | transaction_count            | 7
      12 | big_transaction_count        | 1
      13 | max_transaction_tuples       | 4096
      14 | sent_transaction_count       | 7
      15 | spill_disk_transaction_count | 1
      16 | spill_disk_bytes             | 244653
      17 | spill_disk_count             | 4096
      18 | input_queue_full_count       | 0
      19 | output_queue_full_count      | 0
      20 | dml_count                    | 4097
      21 | dml_filtered_count           | 0
      22 | toast_count                  | 0
      23 | candidate_catalog_xmin       | 17152
      24 | candidate_xmin_lsn           | 0/420A598
      25 | candidate_restart_valid      | 0/420A598
      26 | candidate_restart_lsn        | 0/420A598
(26 rows)

Note: According to the definitions of statistical items, they must meet the following requirements:

wal_read_total_time >= wal_wait_total_time;

transaction_count >= big_transaction_count;

transaction_count >= sent_transaction_count;

transaction_count >= spill_disk_transaction_count;

dml_count >= dml_filtered_count;

dml_count >= toast_count;

If spill_transaction_count == 0, then spill_disk_bytes == 0;

However, frequent locking and unlocking are required, which greatly affects the performance. As a result, the preceding constraints may not be met in multi-thread cases.

transaction_count indicates the number of transactions in all databases.

sent_transaction_count indicates the number of transactions sent by the local database because transactions that are not in the local database will not be sent.

If the value of slot_name does not exist, the function does not report an error and the return value is empty.

Description: Resets indicators in gs_logical_parallel_decode_status('slot_name').

Parameter:

• slot_name

  Indicates the name of the streaming replication slot.

  Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

Return type: text

Example:

gaussdb=# SELECT * FROM gs_logical_parallel_decode_reset_status('replication_slot');
 gs_logical_parallel_decode_reset_status
-----------------------------------------
 OK
(1 row)

gaussdb=# SELECT * FROM gs_logical_parallel_decode_status('replication_slot');
 stat_id |          stat_name           |      value
---------+------------------------------+------------------
       1 | slot_name                    | replication_slot
       2 | reader_lsn                   | 0/357E420
       3 | wal_read_total_time          | 0
       4 | wal_wait_total_time          | 0
       5 | parser_total_time            | 0
       6 | decoder_total_time           | 0
       7 | sender_total_time            | 0
       8 | net_send_total_time          | 0
       9 | net_wait_total_time          | 0
      10 | net_send_total_bytes         | 0
      11 | transaction_count            | 0
      12 | big_transaction_count        | 0
      13 | max_transaction_tuples       | 0
      14 | sent_transaction_count       | 0
      15 | spill_disk_transaction_count | 0
      16 | spill_disk_bytes             | 0
      17 | spill_disk_count             | 0
      18 | input_queue_full_count       | 0
      19 | output_queue_full_count      | 0
      20 | dml_count                    | 0
      21 | dml_filtered_count           | 0
      22 | toast_count                  | 0
      23 | candidate_catalog_xmin       | 0
      24 | candidate_xmin_lsn           | 0/0
      25 | candidate_restart_valid      | 0/420A598
      26 | candidate_restart_lsn        | 0/420A598
(26 rows)

Note: If the value of slot_name does not exist, the function does not report an error and the return value is invalid slot name.

Description: Enables logical replication performance sampling. Currently, the distributed system does not support this function.

Parameter:

• slot_name

  Indicates the name of the streaming replication slot.

  Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

• window

  Specifies the sampling window.

  Value range: an integer ranging from 2 to 1024. Sampling data in the latest interval x window period is collected.

• interval

  Specifies the performance monitoring interval, in seconds.

  Value range: interval type. The minimum value is 1s and the maximum value is 1 minute. Sampling data in the latest interval x window period is collected.

  Return type: text

Description: Stops logical replication performance sampling. Currently, the distributed system does not support this function.

Parameter:

• slot_name

  Indicates the name of the streaming replication slot.

  Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

• Return type: text

Description: Displays the original logical replication performance sampling data. Currently, the distributed system does not support this function.

Parameter:

• slot_name

  Indicates the name of the streaming replication slot.

  Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

• Return type: SETOF record

Description: Displays logical replication performance data. Currently, the distributed system does not support this function.

Parameter:

• slot_name

  Indicates the name of the streaming replication slot.

  Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

• Return type: SETOF record

Description: Queries the monitoring status of a specified logical decoding task. Currently, the distributed system does not support this function.

Parameter:

• slot_name

  Indicates the name of the streaming replication slot.

  Value range: a string, supporting only lowercase letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.). One or two periods cannot be used alone as the replication slot name.

• Return type: text

Description: Executes on the DN where parallel decoding is performed and returns the thread information of parallel decoding.

Return type: int64, text, text, int

Example:

gaussdb=# SELECT * FROM gs_get_parallel_decode_thread_info();
    thread_id    | slot_name | thread_type | seq_number
-----------------+-----------+-------------+------------
 140335364699904 | slot1     | sender      |          1
 140335214098176 | slot1     | reader      |          1
 140335325312768 | slot1     | decoder     |          1
 140335291750144 | slot1     | decoder     |          2
 140335274968832 | slot1     | decoder     |          3
 140335258187520 | slot1     | decoder     |          4
 140335165404928 | slot2     | sender      |          1
 140335022864128 | slot2     | reader      |          1
 140335129818880 | slot2     | decoder     |          1
 140335113037568 | slot2     | decoder     |          2
(10 rows)

Note: In the return values, thread_id indicates the thread ID, slot_name indicates the replication slot name, thread_type indicates the thread type (including the sender, reader and decoder), and seq_number indicates the sequence number of each thread with same type in the current replication slot. Each parallel decoding connection only has one sender and reader. Therefore, the sequence numbers of the sender and reader are both 1. The sequence numbers of the decoder are arranged from 1 to the decoding degree of parallelism (DOP) of the current replication slot.

Description: Creates a replication source with a given external name and returns the internal ID assigned to it.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

• node_name

  Name of the replication source to be created.

  Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

Return type: oid

Description: Deletes a previously created replication source, including any associated replay progress.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

• node_name

  Name of the replication source to be deleted.

  Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

Description: Searches for a replication source by name and returns the internal ID. If no such replication source is found, an error is thrown.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

• node_name

  Specifies the name of the replication source to be queried.

  Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

Return type: oid

Description: Marks the current session for replaying from a given origin, allowing you to track replay progress. This parameter can be used only when no origin is selected. Run the pg_replication_origin_session_reset command to cancel the configuration.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

• node_name

  Name of the replication source.

  Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

Description: Cancels the pg_replication_origin_session_setup() effect.

Note: The user who calls this function must have the SYSADMIN permission.

Description: Returns a true value if a replication source is selected in the current session.

Note: The user who calls this function must have the SYSADMIN permission.

Return type: Boolean

Description: Returns the replay position of the replication source selected in the current session.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

• flush

  Determines whether the corresponding local transaction has been flushed to disk.

  Value range: Boolean

Return type: LSN

Description: Marks the current transaction as recommitted at a given LSN and timestamp. This function can be called only when pg_replication_origin_session_setup is used to select a replication source.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

• origin_lsn

  Position for replaying the replication source.

  Value range: LSN

• origin_timestamp

  Time point when a transaction is committed.

  Value range: timestamp with time zone

Description: Cancels the pg_replication_origin_xact_setup() effect.

Note: The user who calls this function must have the SYSADMIN permission.

Description:

Sets the replication progress of a given node to a given position. This is primarily used to set the initial position, or to set a new position after a configuration change or similar change.

Note: Improper use of this function may cause inconsistent replication data.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

• node_name

  Name of an existing replication source.

  Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

• lsn

  Position for replaying the replication source.

  Value range: LSN

Description: Returns the position for replaying the given replication source.

Note: The user who calls this function must have the SYSADMIN permission.

Parameter:

• node_name

  Name of the replication source.

  Value range: a string, supporting only letters, digits, underscores (_), question marks (?), hyphens (-), and periods (.).

• flush

  Determines whether the corresponding local transaction has been flushed to disk.

  Value range: Boolean

Description: Displays the replication status of the replication source.

Note: The user who calls this function must have the SYSADMIN permission.

Return type:

• local_id: OID, which specifies the ID of the replication source.
• external_id: text, which specifies the name of the replication source.
• remote_lsn: LSN of the replication source.
• local_lsn: local LSN.

Description: Obtains the distributed decoding status details (by replication slot) on the current node. This command must be executed on CNs. If it is executed on DNs, null is returned.

Return type: text, int, int, int64, xid, xid, text, text, text

gaussdb=# SELECT * FROM gs_get_distribute_decode_status();
 slot_name | logical_receiver_num | slice_num | walsender_thread_id | last_sent_csn | last_confirmed_csn |      receiver_queue_length      |          connect_times          |                 csn_receive_array
-----------+----------------------+-----------+---------------------+---------------+--------------------+---------------------------------+---------------------------------+---------------------------------------------------
 slot1     |                    1 |         3 |     139958481843968 |       2012169 |            2010107 | queue0: 1, queue1: 1, queue2: 0 | slice0: 2, slice1: 2, slice2: 2 | slice0: 2012244, slice1: 2012244, slice2: 2012244
(1 row)

Description: Obtains the distributed decoding status details (by DN) on the current node. This command must be executed on CNs. If it is executed on DNs, null is returned.

Return type: text, int, int64, int, int, xid

gaussdb=# SELECT * FROM gs_get_distribute_decode_status_detail();
 slot_name | slice_id |    thread_id    | queue_len | connect_times | received_csn
-----------+----------+-----------------+-----------+---------------+--------------
 slot1     |        0 | 139959895848704 |         1 |             2 |      2012244
 slot1     |        1 | 139959895848704 |         1 |             2 |      2012244
 slot1     |        2 | 139959895848704 |         0 |             2 |      2012244
(3 rows)