Logical Replication Functions

Description: Creates a logical replication slot.

Parameter description:

• 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, the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role. 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 description:

• 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

Note: Users who call this function must have the SYSADMIN permission or the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role. 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 description:

• 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

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

• 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 on the decoding DN 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.

    Valid value: 0 and 1. The default value is 1.

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

    Specifies whether to ignore empty transaction information during decoding.

    Value range: 0 and 1. The default value is 0.

    • 0: The empty transaction information is not ignored during decoding.
    • 1: The empty transaction information is ignored during decoding.
  • include-timestamp

    Specifies whether decoded information contains the commit timestamp.

    Value range: 0 and 1. The default value is 0.

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

    Specifies whether to decode only local logs.

    Value range: 0 and 1. The default value is 1.

    • 0: Non-local logs and local logs are decoded.
    • 1: Only local logs are decoded.
  • force-binary

    Specifies whether to output the decoding result in binary format.

    Value range: 0

    • 0: The decoding result is output in text 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. 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: 0 and 1. The default value is 0.

    • 0: The BEGIN logical log of a transaction does not contain the username of the transaction.
    • 1: 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: 0 and 1. The default value is 1.

    • 0: If the parameter is set to 0, an error is reported and the logical decoding exits when the decoding detects that the user does not exist in blacklist exclude-users.
    • 1: If the parameter is set to 1, decoding continues when it detects that the user does not exist in blacklist exclude-users.

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

Return type: text, xid, text

gaussdb=# execute direct on (datanode1)'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=# execute direct on (datanode1)'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, the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role.

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

Description: Performs decoding on the decoding DN and updates the streaming replication slot.

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

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

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 format on the decoding DN and does not update the 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.

    Valid value: 0 and 1. The default value is 1.

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

    Specifies whether to ignore empty transaction information during decoding.

    Value range: 0 and 1. The default value is 0.

    • 0: The empty transaction information is not ignored during decoding.
    • 1: The empty transaction information is ignored during decoding.
  • include-timestamp

    Specifies whether decoded information contains the commit timestamp.

    Value range: 0 and 1. The default value is 0.

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

    Specifies whether to decode only local logs.

    Value range: 0 and 1. The default value is 1.

    • 0: Non-local logs and local logs are decoded.
    • 1: Only local logs are decoded.
  • force-binary

    Specifies whether to output the decoding result in binary format.

    Value range: 0 or 1. The default value is 0. 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: 0 and 1. The default value is 0.

    • 0: The BEGIN logical log of a transaction does not contain the username of the transaction.
    • 1: 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: 0 and 1. The default value is 1.

    • 0: If the parameter is set to 0, an error is reported and the logical decoding exits when the decoding detects that the user does not exist in blacklist exclude-users.
    • 1: If the parameter is set to 1, decoding continues when it detects that the 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 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, the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role.

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

Description: Performs decoding in binary format on the decoding DN and updates the 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 the gs_role_replication permission of the built-in role.

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 updates the streaming replication slot to a specified LSN on the decoding DN, 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 period or two periods cannot be used 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 stream 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, the REPLICATION permission, or inherit the gs_role_replication permission of the built-in role.
• 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 on the decoding DN 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.
• 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.

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 description:

• 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 end position of decoding is not specified.

• 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. For details, see pg_logical_slot_peek_changes.

Example:

- Perform DML operations on a CN. You can query the DML operations executed on the current DN.
gaussdb=# execute direct on (datanode1) '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 on the DN where parallel decoding is performed to locate the concurrent decoding performance bottleneck.

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

Example:

gaussdb=# execute direct on (datanode1) '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 latest decoded WAL time of an active replication slot on the DN where parallel decoding is performed.

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=# execute direct on (datanode1) '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 log 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 an active replication slot for parallel logical decoding on the DN where parallel decoding is performed, including 26 rows of indicators.

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

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

Parameter description:

• 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=# execute direct on (datanode1) '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 extreme 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') on the DN where parallel decoding is performed.

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=# execute direct on (datanode1) 'select * from gs_logical_parallel_decode_reset_status(''replication_slot'');';
 gs_logical_parallel_decode_reset_status
-----------------------------------------
 OK
(1 row)

gaussdb=# execute direct on (datanode1) '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: Returns the thread information of the active replication slot on the current DN where parallel decoding is performed.

Return type: int64, text, text, int

Example:

gaussdb=# execute direct on (decode_datanode1) '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, and thread_type indicates the thread type (including the sender, reader and decoder), 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: 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)