Help Center> Data Warehouse Service (DWS)> 8.3.0.x> Functions and Operators> Residual File Management Functions> Functions for Scanning Residual Files
Updated on 2024-05-31 GMT+08:00
View PDF

Functions for Scanning Residual Files

Only cluster 8.3.0 and later versions support the function of scanning residual files. All functions described in this section can be invoked only by the sysadmin role.

pg_scan_residualfiles()

Description: Scans all residual file records in the database connected to the current node. This function is an instance-level function and is related to the current database. It can run on any instance.

Return type: record

The following information is displayed:

Table 1 Return fields

Field

Type

Description

residualfile

text

Path of the residual file

Example:

1
2
3
4
5
6
 
SELECT * FROM pg_scan_residualfiles();
                               pgscrf                               
--------------------------------------------------------------------
 pg_residualfiles/pgscrf_meta_1663_16323_20231027143716005244
 pg_residualfiles/pgscrf_meta_2147484281_16323_20231027143716005713
(2 rows)
  • This function scans the current database for any residual files. It only checks the default and user-defined tablespaces.
  • This function is a heavy-load operation and cannot be used concurrently on a single node. You should avoid using this function when the service is overloaded or the resource is scarce.

pgxc_scan_residualfiles(query_flag)

Description: Unified CN execution function of pg_scan_residualfiles() This function is a cluster-level function and is related to the current database. It runs on CNs.

Parameter description: query_flag. The parameter type is int, indicating the execution range. 1 indicates the CN, 2 indicates the primary DN, and 4 indicates the standby DN. The query union set can be obtained through the OR operation. For example, 1|2=3 indicates the CN and primary DN, and 1|2|4=7 indicates the CN, primary DN, and standby DN. The default value is 7.

Return type: record

The command output is as follows.

Table 2 Return fields

Field

Type

Description

nodename

name

Node name

instance_id

text

Instance name

residualfile

text

Path of the residual file

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
SELECT * FROM pgxc_scan_residualfiles();
  node_name   | instance_id |                               pgscrf                               
--------------+-------------+--------------------------------------------------------------------
 dn_6001_6002 | dn_6001     | pg_residualfiles/pgscrf_meta_1663_16323_20231027144103839354
 dn_6001_6002 | dn_6001     | pg_residualfiles/pgscrf_meta_2147484281_16323_20231027144103839826
 cn_5001      | cn_5001     | pg_residualfiles/pgscrf_meta_1663_16323_20231027144103946217
 dn_6007_6008 | dn_6008     | pg_residualfiles/pgscrf_meta_1663_16323_20231027144104171311
(4 rows)

SELECT * FROM pgxc_scan_residualfiles(1);
 node_name | instance_id |                            pgscrf                            
-----------+-------------+--------------------------------------------------------------
 cn_5001   | cn_5001     | pg_residualfiles/pgscrf_meta_1663_16323_20231027144421861628
(1 row)

SELECT * FROM pgxc_scan_residualfiles(2);
  node_name   | instance_id |                               pgscrf                               
--------------+-------------+--------------------------------------------------------------------
 dn_6001_6002 | dn_6001     | pg_residualfiles/pgscrf_meta_1663_16323_20231027144424210395
 dn_6001_6002 | dn_6001     | pg_residualfiles/pgscrf_meta_2147484281_16323_20231027144424210855
(2 rows)

SELECT * FROM pgxc_scan_residualfiles(4);
  node_name   | instance_id |                            pgscrf                            
--------------+-------------+--------------------------------------------------------------
 dn_6007_6008 | dn_6008     | pg_residualfiles/pgscrf_meta_1663_16323_20231027144427492060
(1 row)
  • This function scans the current database for any residual files. It only checks the default and user-defined tablespaces.
  • This function is a heavy-load operation and cannot be used concurrently on a single node. You should avoid using this function when the service is overloaded or the resource is scarce.

pg_get_scan_residualfiles()

Description: Obtains all residual file records scanned on the current node. This function is an instance-level function and is irrelevant to the current database. It can run on any instance.

Return type: record

The command output is as follows.

Table 3 Return fields

Field

Type

Description

handled

bool

Indicates whether the request has been handled.

dbname

text

Database name

residualfile

text

Path of the residual file

size

bigint

File size, in bytes.

inode

bigint

Inode in the stat information of the residual file.

atime

timestamptz

Access time in the stat information of the residual file.

mtime

timestamptz

Modifie time in the stat information of the residual file.

ctime

timestamptz

Change time in the stat information of the residual file.

filepath

text

It corresponds to the pgscrf_meta file.

notes

text

Notes

Example:

1
2
3
4
5
6
7
8
 
SELECT * FROM pg_get_scan_residualfiles();
 handled |  dbname  |                            residualfile                             | size  |   inode    |         atime          |         mtime          |         ctime          |                     filepath                      | notes 
---------+----------+---------------------------------------------------------------------+-------+------------+------------------------+------------------------+------------------------+---------------------------------------------------+-------
 f       | postgres | base/16323/2147494191                                               | 81920 |  805444689 | 2023-10-27 14:21:49+08 | 2023-10-27 14:23:08+08 | 2023-10-27 14:23:08+08 | pgscrf_meta_1663_16323_20231027144103839354       | 
 f       | postgres | base/16323/2147494191_fsm                                           |     0 |  805444690 | 2023-10-27 14:21:49+08 | 2023-10-27 14:21:49+08 | 2023-10-27 14:21:49+08 | pgscrf_meta_1663_16323_20231027144103839354       | 
 f       | postgres | base/16323/2147494191_vm                                            |     0 |  805444691 | 2023-10-27 14:21:49+08 | 2023-10-27 14:21:49+08 | 2023-10-27 14:21:49+08 | pgscrf_meta_1663_16323_20231027144103839354       | 
 f       | postgres | pg_tblspc/2147484281/PG_9.2_201611171_dn_6001_6002/16323/2147894282 |     0 | 7247789391 | 2023-10-27 14:37:03+08 | 2023-10-27 14:37:03+08 | 2023-10-27 14:37:03+08 | pgscrf_meta_2147484281_16323_20231027144103839826 | 
(4 rows)

pgxc_get_scan_residualfiles(query_flag)

Description: Unified CN execution function of pg_get_scan_residualfiles() This function is a cluster-level function and is irrelevant to the current database. It runs on CNs.

Parameter description: query_flag. The parameter type is int, indicating the execution range. 1 indicates the CN, 2 indicates the primary DN, and 4 indicates the standby DN. The query union set can be obtained through the OR operation. For example, 1|2=3 indicates the CN and primary DN, and 1|2|4=7 indicates the CN, primary DN, and standby DN. The default value is 7.

Return type: record

The command output is as follows.

Table 4 Return fields

Field

Type

Description

nodename

name

Node name

instance_id

text

Instance name

handled

bool

Indicates whether the request has been handled.

dbname

text

Database name

residualfile

text

Path of the residual file

size

bigint

File size, in bytes.

inode

bigint

Inode in the stat information of the residual file.

atime

timestamptz

Access time in the stat information of the residual file.

mtime

timestamptz

Modifie time in the stat information of the residual file.

ctime

timestamptz

Change time in the stat information of the residual file.

filepath

text

It corresponds to the pgscrf_meta file.

notes

text

Notes

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT * FROM pgxc_get_scan_residualfiles();
  node_name   | instance_id | handled |  dbname  |                            residualfile                             | size  |   inode    |         atime          |         mtime          |         ctime          |                     filepath                      | notes 
--------------+-------------+---------+----------+---------------------------------------------------------------------+-------+------------+------------------------+------------------------+------------------------+---------------------------------------------------+-------
 dn_6001_6002 | dn_6001     | f       | postgres | base/16323/2147494191                                               | 81920 |  805444689 | 2023-10-27 14:21:49+08 | 2023-10-27 14:23:08+08 | 2023-10-27 14:23:08+08 | pgscrf_meta_1663_16323_20231027144103839354       | 
 dn_6001_6002 | dn_6001     | f       | postgres | base/16323/2147494191_fsm                                           |     0 |  805444690 | 2023-10-27 14:21:49+08 | 2023-10-27 14:21:49+08 | 2023-10-27 14:21:49+08 | pgscrf_meta_1663_16323_20231027144103839354       | 
 dn_6001_6002 | dn_6001     | f       | postgres | base/16323/2147494191_vm                                            |     0 |  805444691 | 2023-10-27 14:21:49+08 | 2023-10-27 14:21:49+08 | 2023-10-27 14:21:49+08 | pgscrf_meta_1663_16323_20231027144103839354       | 
 dn_6001_6002 | dn_6001     | f       | postgres | pg_tblspc/2147484281/PG_9.2_201611171_dn_6001_6002/16323/2147894282 |     0 | 7247789391 | 2023-10-27 14:37:03+08 | 2023-10-27 14:37:03+08 | 2023-10-27 14:37:03+08 | pgscrf_meta_2147484281_16323_20231027144103839826 | 
 cn_5001      | cn_5001     | f       | postgres | base/16323/2147784070                                               | 40960 |  537070896 | 2023-10-27 14:38:59+08 | 2023-10-27 14:39:17+08 | 2023-10-27 14:39:17+08 | pgscrf_meta_1663_16323_20231027144103946217       | 
 dn_6007_6008 | dn_6008     | f       | postgres | base/16323/2147784073                                               | 24576 | 7784666105 | 2023-10-27 14:40:21+08 | 2023-10-27 14:40:34+08 | 2023-10-27 14:40:34+08 | pgscrf_meta_1663_16323_20231027144104171311       | 
(6 rows)

pg_archive_scan_residualfiles()

Description: Archives all residual file records scanned on the database connected to the current node. This function is an instance-level function and is related to the current database. It can run on any instance.

Return type: record

The command output is as follows.

Table 5 Return fields

Field

Type

Description

archive

text

Archive path of residual files.

count

bigint

Number of archived residual files.

size

bigint

Total size of archived residual files, in bytes.

Example:

1
2
3
4
5
 
SELECT * FROM pg_archive_scan_residualfiles();
                           archive                            | count | size  
--------------------------------------------------------------+-------+-------
 pg_residualfiles/archive/pgscrf_archive_20231027144613791801 |     4 | 81920
(1 row)
  • This function can archive only the residual files recorded in the current login database. During archiving, the residual files are verified. The verification results are as follows:
    • Verification passed: After the verification is passed, the residual files are archived and marked as handled.
    • Verification failed: If the verification fails, the residual files are not archived and marked as handled.
    • Verification delayed: If the verification is delayed, it indicates that verification is not possible at the moment, and archiving is skipped. The verification delay is related to the transaction completion status and the standby node's redo progress.
  • The actual archive directory and the corresponding tablespace are in the same file system. Deleting the tablespace also deletes the archived residual files.
  • This function cannot be used after DDL delay is enabled.
  • This function is a heavy-load operation and cannot be used concurrently on a single node. You should avoid using this function when the service is overloaded or the resource is scarce.

pgxc_archive_scan_residualfiles(query_flag)

Description: Unified CN execution function of pg_archive_scan_residualfiles() This function is a cluster-level function and is related to the current database. It runs on CNs.

Parameter description: query_flag. The parameter type is int. It indicates the execution scope. 1 indicates the CN, 2 indicates the primary DN, and 4 indicates the standby DN. The query union set can be obtained through the OR operation. For example, 1|2=3 indicates the CN and primary DN, and 1|2|4=7 indicates the CN, primary DN, and standby DN. The default value is 7.

Return type: record

The command output is as follows.

Table 6 Return fields

Field

Type

Description

nodename

name

Node name

instance_id

text

Instance name

archive

text

Archive path of residual files.

count

bigint

Number of archived residual files.

size

bigint

Total size of archived residual files, in bytes.

Example:

1
2
3
4
5
6
 
SELECT * FROM pgxc_archive_scan_residualfiles();
  node_name   | instance_id |                           archive                            | count | size  
--------------+-------------+--------------------------------------------------------------+-------+-------
 cn_5001      | cn_5001     | pg_residualfiles/archive/pgscrf_archive_20231027145050896440 |     1 | 40960
 dn_6007_6008 | dn_6008     | pg_residualfiles/archive/pgscrf_archive_20231027145051018138 |     1 | 24576
(2 rows)
  • This function can archive only the residual files recorded in the current login database. During archiving, the residual files are verified. The verification results are as follows:
    • Verification passed: After the verification is passed, the residual files are archived and marked as handled.
    • Verification failed: If the verification fails, the residual files are not archived and marked as handled.
    • Verification delayed: If the verification is delayed, it indicates that verification is not possible at the moment, and archiving is skipped. The verification delay is related to the transaction completion status and the standby node's redo progress.
  • The actual archive directory and the corresponding tablespace are in the same file system. Deleting the tablespace also deletes the archived residual files.
  • This function cannot be used after DDL delay is enabled.
  • This function is a heavy-load operation and cannot be used concurrently on a single node. You should avoid using this function when the service is overloaded or the resource is scarce.

pg_rm_scan_residualfiles_archive()

Description: Deletes all archived residual file records on the current node. This function is an instance-level function and is irrelevant to the current database. It can run on any instance.

Return type: record

The command output is as follows.

Table 7 Return fields

Field

Type

Description

count

bigint

Number of residual files that have been deleted from the archive.

size

bigint

Total size of residual files that have been deleted from the archive, in bytes.

Example:

1
2
3
4
5
 
SELECT * FROM pg_rm_scan_residualfiles_archive();
 count | size  
-------+-------
     4 | 81920
(1 row)

pgxc_rm_scan_residualfiles_archive(query_flag)

Description: Unified CN execution function of pg_rm_scan_residualfiles_archive() This function is a cluster-level function and is irrelevant to the current database. It runs on CNs.

Parameter description: query_flag. The parameter type is int. It indicates the execution scope. 1 indicates the CN, 2 indicates the primary DN, and 4 indicates the standby DN. The query union set can be obtained through the OR operation. For example, 1|2=3 indicates the CN and primary DN, and 1|2|4=7 indicates the CN, primary DN, and standby DN. The default value is 7.

Return type: record

The command output is as follows.

Table 8 Return fields

Field

Type

Description

nodename

name

Node name

instance_id

text

Instance name

count

bigint

Number of residual files that have been deleted from the archive.

size

bigint

Total size of residual files that have been deleted from the archive, in bytes.

Example:

1
2
3
4
5
6
7
 
SELECT * FROM pgxc_rm_scan_residualfiles_archive();
  node_name   | instance_id | count | size  
--------------+-------------+-------+-------
 dn_6001_6002 | dn_6001     |     4 | 81920
 cn_5001      | cn_5001     |     1 | 40960
 dn_6007_6008 | dn_6008     |     1 | 24576
(3 rows