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 plugin.

  Value range: a string, supporting mppdb_decoding

• output_order

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

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

  • 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 a non-zero value. This replication slot is called a CSN-based replication slot.

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 the gs_role_replication permission of the built-in role. Currently, this function can be called only on the primary node.

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

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. Currently, this function can be called only on the primary node.

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

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 the gs_role_replication permission of the built-in role.

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_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. This function can be called on the primary or standby node. If this function is called on the standby node, the corresponding logical replication slot number on the primary node is updated.

If this function is executed on the standby node, a WAL sender of the primary node is occupied when the replication slot number on the primary node 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 node is updated normally. If this function is executed continuously in a short period of time, the primary node 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.)

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

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 the gs_role_replication permission of the built-in role. Currently, this function can be called only on the primary node.

Description: Directly goes to the streaming replication slot for a specified upto_lsn, without outputting any decoded result.

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.

• 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 or no CSN is input, 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 the input LSN is smaller than the position recorded in the current streaming replication slot, the function directly returns the decoded result. If the input LSN is greater than the LSN of the current physical log or no LSN is input, the latest 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.) (If this parameter is set to a null value, the target LSN indicating the end position of decoding is not specified.)

Return type: name, text

Note: A return result contains the slot name and LSN or CSN that is actually used for decoding. 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. This function can be called on the primary or standby node. When this function is called on the standby node, the corresponding logical replication slot number on the primary node is updated.

This function can be executed on the standby node to synchronously update the corresponding logical replication slot number on the primary node. If this function is executed on the standby node, a WAL sender of the primary node is occupied when the replication slot number on the primary node 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 node is updated normally. If this function is executed continuously in a short period of time, the primary node fails to update the slot number and no error is reported.

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 the API is called, only when wal_level is set to logical, the generated log files can be parsed. If the used Xlog file is not of the logical level, the decoded content does not have the corresponding value and type, and 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-duty is enabled, only the initial database user can call the function. When separation-of-duty 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'. (If this parameter is set to a null value, the target LSN indicating 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'. (If this parameter is set to a null value, the target LSN indicating 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:

gaussdb=# CREATE TABLE t1(a int, b int);
CREATE TABLE
gaussdb=# SELECT pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 0/5ECBCD48
(1 row)

gaussdb=# INSERT INTO t1 VALUES(1,1);
INSERT 0 1
gaussdb=# UPDATE t1 SET b = 2 WHEREa = 1;
UPDATE 1
gaussdb=# DELETE FROM t1;
DELETE 1
gaussdb=# SELECT * FROM pg_logical_get_area_changes('0/5ECBCD48', NULL, NULL, 'sql_decoding', NULL);
  location  |  xid  |                                       data
------------+-------+----------------------------------------------------------------------------------
 0/5ECBCD78 | 70718 | insert into public.t1 values (1, 1);
 0/5ECBCEA0 | 70718 | COMMIT 70718 (at 2023-11-01 10:58:51.448885+08) 39319
 0/5ECBCED0 | 70719 | delete from public.t1 where a = 1 and b = 1;insert into public.t1 values (1, 2);
 0/5ECBD028 | 70719 | COMMIT 70719 (at 2023-11-01 10:58:56.487796+08) 39320
 0/5ECBD210 | 70720 | delete from public.t1 where a = 1 and b = 2;
 0/5ECBD338 | 70720 | COMMIT 70720 (at 2023-11-01 10:58:58.856661+08) 39321
(6 rows)

-- For a table with generated columns:
gaussdb=# CREATE TABLE t2(a int, b int generated always as (a + 1) stored);
CREATE TABLE
gaussdb=# SELECT pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 0/5F62CFE8
(1 row)

gaussdb=# INSERT INTO t2(a) VALUES(1);
INSERT 0 1
gaussdb=# UPDATE t2 SET a = 2 WHERE a = 1;
UPDATE 1
gaussdb=# DELETE FROM t2;
DELETE 1
gaussdb=# SELECT * FROM pg_logical_get_area_changes('0/5F62CFE8', NULL, NULL, 'sql_decoding', NULL, 'skip-generated-columns', 'on');
  location  |  xid  |                                data
------------+-------+---------------------------------------------------------------------
 0/5F62D0C8 | 71293 | insert into public.t2 values (1);
 0/5F62D1F0 | 71293 | COMMIT 71293 (at 2023-11-01 11:11:49.452044+08) 39516
 0/5F62D220 | 71294 | delete from public.t2 where a = 1;insert into public.t2 values (2);
 0/5F62D378 | 71294 | COMMIT 71294 (at 2023-11-01 11:11:54.327701+08) 39517
 0/5F62D408 | 71295 | delete from public.t2 where a = 2;
 0/5F62D530 | 71295 | COMMIT 71295 (at 2023-11-01 11:11:58.362057+08) 39518
(6 rows)

Description: Obtains the replication slot list.

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

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)

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.

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 latest decoded WAL time of an active replication slot that performs parallel decoding.

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 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 that performs 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 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=# 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').

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: 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 performance sampling for an active logical replication slot that performs parallel 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.

• 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

Example:

gaussdb=# SELECT * FROM gs_logical_decode_start_observe('replication_slot',20,5);
 gs_logical_decode_start_observe 
---------------------------------
 OK
(1 row)

gaussdb=# SELECT * FROM gs_logical_decode_start_observe('replication_slot',20,5);
 gs_logical_decode_start_observe 
---------------------------------
 observe has started!
(1 row)

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.

Do not enable the observe function for a replication slot that is being observed. The error information is as follows:

1. If slot_name is empty, the following error is reported: "ERROR: inputString should not be NULL is displayed".

2. If slot_name is not empty but does not exist, no error is reported but "invalid slot name" is displayed.

3. If the value of window is less than 2, "window has to be >= 2" is displayed.

4. If the value of window is greater than 1024, "window has to be <= 1024" is displayed.

5. If the value of interval is less than 1s, "sample interval has to be >= 1s" is displayed.

6. If the value of interval is greater than 60s, "sample interval has to be <= 60s" is displayed.

7. If slot_name is not empty but the observe function has been enabled for the replication slot corresponding to slot_name, no error is reported and the message "observe has started!" is displayed.

Description: Stops logical replication performance sampling.

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

Example:

gaussdb=# select * from gs_logical_decode_stop_observe('replication_slot');
 gs_logical_decode_stop_observe 
--------------------------------
 OK
(1 row)

gaussdb=# select * from gs_logical_decode_stop_observe('replication_slot');
 gs_logical_decode_stop_observe 
--------------------------------
 observe not started!
(1 row)

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.

Do not disable the observe function for a replication slot that is not observed. The error information is as follows:

1. If slot_name is empty, the following error is reported: "ERROR: inputString should not be NULL is displayed".

2. If slot_name is not empty but does not exist, no error is reported but "invalid slot name" is displayed.

3. If slot_name is not empty but the observe function has been disabled for the replication slot corresponding to slot_name, no error is reported and the message "observe not started!" is displayed.

Description: Displays the original logical replication performance sampling data.

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: SETOF record

Example:

gaussdb=# select * from gs_logical_decode_observe_data('replication_slot');
 slot_name |          sample_time          | reader_lsn | wal_read_total_time | wal_wait_total_time | parser_total_time | decoder_total_time | sender_total_time | net_send_total_time | net_send_total_bytes | transaction_count | big_
transaction_count | sent_transaction_count | spill_transaction_count | spill_disk_bytes 
-----------+-------------------------------+------------+---------------------+---------------------+-------------------+--------------------+-------------------+---------------------+----------------------+-------------------+-----
------------------+------------------------+-------------------------+------------------
 repl      | 2023-01-12 20:09:40.416798+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:09:45.416849+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:09:50.417006+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:09:55.417057+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:00.417115+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:05.417165+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:10.417217+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:15.417271+08 |   49447976 |           776846657 |           776846244 |                65 | {46,11,11,6}       |                56 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:20.417342+08 |   49448264 |           836085882 |           836085442 |                67 | {46,11,11,8}       |                58 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:25.417433+08 |   49448264 |           836085882 |           836085442 |                67 | {46,11,11,8}       |                58 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:30.417526+08 |   49448264 |           836085882 |           836085442 |                67 | {46,11,11,8}       |                58 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:35.417532+08 |   49448264 |           836085882 |           836085442 |                67 | {46,11,11,8}       |                58 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:40.417644+08 |   49448264 |           836085882 |           836085442 |                67 | {46,11,11,8}       |                58 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
 repl      | 2023-01-12 20:10:45.417763+08 |   49448264 |           836085882 |           836085442 |                67 | {46,11,11,8}       |                58 |                   0 |                    0 |                 0 |     
                0 |                      0 |                       0 |                0
(14 rows

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

Description: Displays logical replication performance data.

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: SETOF record

Example:

gaussdb=# select * from gs_logical_decode_observe('replication_slot');
    slot_name     |          sample_time          | logical_decode_rate | wal_read_rate | parser_rate  |  decoder_rate  |  sender_rate  | net_send_rate 
------------------+-------------------------------+---------------------+---------------+--------------+----------------+---------------+---------------
 replication_slot | 2023-01-12 20:16:50.42448+08  | 0.000               | 0.000         | 0.000        | 0.000          | 0.000         | 0.000
 replication_slot | 2023-01-12 20:16:55.424537+08 | 0.000               | 0.000         | 0.000        | 0.000          | 0.000         | 0.000
 replication_slot | 2023-01-12 20:17:00.424641+08 | 0.000               | 0.000         | 0.000        | 0.000          | 0.000         | 0.000
 replication_slot | 2023-01-12 20:17:05.424645+08 | 0.000               | 0.000         | 0.000        | 0.000          | 0.000         | 0.000
 replication_slot | 2023-01-12 20:17:10.424795+08 | 0.000               | 0.000         | 0.000        | 0.000          | 0.000         | 0.000
 replication_slot | 2023-01-12 20:17:15.424848+08 | 0.000               | 0.000         | 0.000        | 0.000          | 0.000         | 0.000
 replication_slot | 2023-01-12 20:17:20.424849+08 | 57.600              | 699029.126    | 96000000.000 | 1152000000.000 | 144000000.000 | 0.000
 replication_slot | 2023-01-12 20:17:25.424959+08 | 0.000               | 699029.126    | 96000000.000 | 1152000000.000 | 144000000.000 | 0.000
 replication_slot | 2023-01-12 20:17:30.42496+08  | 0.000               | 699029.126    | 96000000.000 | 1152000000.000 | 144000000.000 | 0.000
 replication_slot | 2023-01-12 20:17:35.425059+08 | 0.000               | 699029.126    | 96000000.000 | 1152000000.000 | 144000000.000 | 0.000

Note: If the value of slot_name does not exist, the function does not report an error and the return value is an empty record. If the denominator is 0, the latest collected valid data is returned. If the denominator is not 0 and the numerator is 0, 0 is returned.

Formula:

logical_decode_rate = (reader_lsn1 - reader_lsn2) / (sample_time1 - sample_time2)

wal_read_rate = (reader_lsn1 - reader_lsn2) / (wal_read_total_time1 - wal_read_total_time2) - (wal_wait_total_time1 - wal_wait_total_time2)

parser_rate = (reader_lsn1 - reader_lsn2) / (parser_total_time1 - parser_total_time2)

decoder_rate= (reader_lsn1 - reader_lsn2) / avg(decoder_total_time1[i]- decoder_total_time2[i])

sender_rate = (reader_lsn1 - reader_lsn2) / (sender_total_time1 - sender_total_time2) - (net_send_total_time1 - net_send_total_time2)

sender_rate = (net_sent_bytes1 - net_sent_bytes2) / (net_send_total_time1 - net_send_total_time2) - (net_wait_total_time1 - net_wait_total_time2)

Description: Queries the monitoring status of a specified logical decoding task.

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

Example:

gaussdb=# select * from gs_logical_decode_observe_status('replication_slot');
 gs_logical_decode_observe_status 
----------------------------------
 START
(1 row)

gaussdb=# select * from gs_logical_decode_observe_status('replication_slot');
 gs_logical_decode_observe_status 
----------------------------------
 invalid slot name
(1 row)

gaussdb=# select * from gs_logical_decode_stop_observe('replication_slot');
 gs_logical_decode_stop_observe 
--------------------------------
 OK
(1 row)

gaussdb=# select * from gs_logical_decode_observe_status('replication_slot');
 gs_logical_decode_observe_status 
----------------------------------
 STOP
(1 row)

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 for 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, 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 decoders 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 description:

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

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

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

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

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

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

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

• 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.