• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. Functions and Operators
  6. System Administration Functions

System Administration Functions

Configuration Settings Functions

Configuration setting functions are used for querying and modifying configuration parameters during running.

  • current_setting(setting_name)

    Description: Specifies the current setting.

    Return type: text

    Note: current_setting obtains the current setting of setting_name by query. It is equivalent to the SHOW statement. Example:

    SELECT current_setting('datestyle');
    
     current_setting
    -----------------
     ISO, MDY
    (1 row)
  • set_config(setting_name, new_value, is_local)

    Description: Sets the parameter and returns a new value.

    Return type: text

    set_config sets the parameter setting_name to new_value. If is_local is true, the new value will only apply to the current transaction. If you want the new value to apply for the current session, use false instead. The function corresponds to the SET statement. Example:

    SELECT set_config('log_statement_stats', 'off', false);
    
     set_config
    ------------
     off
    (1 row)

Universal File Access Functions

Universal file access functions provide local access interfaces for files on the device where the database server is located. Only the files in the database cluster directory and the log_directory can be accessed. Use a relative path for files in the cluster directory, and a path matching the log_directory configuration setting for log files. Only system administrators can use these functions.

  • pg_ls_dir(dirname text)

    Description: Lists files in the directory.

    Return type: setof text

    pg_ls_dir returns all the names in the specified directory, except the special entries "." and "..".

    Example:

    SELECT pg_ls_dir('./');
          pg_ls_dir       
    ----------------------
     .postgresql.conf.swp
     postgresql.conf
     pg_tblspc
     PG_VERSION
     pg_ident.conf
     core
     server.crt
     pg_serial
     pg_twophase
     postgresql.conf.lock
     pg_stat_tmp
     pg_notify
     pg_subtrans
     pg_ctl.lock
     pg_xlog
     pg_clog
     base
     pg_snapshots
     postmaster.opts
     postmaster.pid
     server.key.rand
     server.key.cipher
     pg_multixact
     pg_errorinfo
     server.key
     pg_hba.conf
     pg_replslot
     .pg_hba.conf.swp
     cacert.pem
     pg_hba.conf.lock
     global
     gaussdb.state
    (32 rows)
  • pg_read_binary_file(filename text [, offset bigint, length bigint,missing_ok boolean])

    Description: Returns the contents of a binary file.

    Return type: bytea

    Note: The convert_from function and this function can be used together to read a file in a specified encoding.

    SELECT convert_from(pg_read_binary_file('filename'), 'UTF8');
  • pg_stat_file(filename text)

    Description: Returns status information about a file.

    Return type: record

    Note: pg_stat_file returns a record containing the file size, last access time stamp, last modification time stamp, last file status change time stamp, and a boolean indicating if it is a directory. Method usage:

    SELECT * FROM pg_stat_file('filename');
    SELECT (pg_stat_file('filename')).modification;

    Example:

     
    SELECT * FROM pg_stat_file('postmaster.pid');
     
     size |         access         |      modification      |         change         
    | creation | isdir 
    ------+------------------------+------------------------+------------------------
    +----------+-------
      117 | 2017-06-05 11:06:34+08 | 2017-06-01 17:18:08+08 | 2017-06-01 17:18:08+08 
    |          | f
    (1 row)
    
    SELECT (pg_stat_file('postmaster.pid')).modification;
          modification      
    ------------------------
     2017-06-01 17:18:08+08
    (1 row)
    

Server Signal Functions

Server signaling functions send control signals to other server processes. Only system administrators can use these functions.

  • pg_cancel_backend(pid int)

    Description: Cancels the current query of a backend.

    Return type: Boolean

    Note: pg_cancel_backend sends a query cancellation (SIGINT) signal to the backend process identified by pid. The PID of an active backend process can be found in the pid column of the pg_stat_activity view, or can be found by listing the database process using ps on the server.

  • pg_reload_conf()

    Description: Causes all server processes to reload their configuration files.

    Return type: Boolean

    Note: pg_reload_conf sends a SIGHUP signal to the server. As a result, all server processes re-load their configuration files.

  • pg_rotate_logfile()

    Description: Rotates the log files of the server.

    Return type: Boolean

    Note: pg_rotate_logfile sends a signal to the log file manager, notifying the manager to immediately switch to a new output file. This function works only when redirect_stderr is used for log output. Otherwise, no log file manager sub-process exists.

  • pg_terminate_backend(pid int)

    Description: Terminates a backend process.

    Return type: Boolean

    Note: Each of these functions returns true if successful and false otherwise.

    Example:

    SELECT pid from pg_stat_activity;
           pid       
    -----------------
     140657876268816
    (1 rows)
    
    SELECT pg_terminate_backend(140657876268816);
     pg_terminate_backend 
    ----------------------
     t
    (1 row)

Backup Control Functions

Backup control functions help with online backup.

  • pg_create_restore_point(name text)

    Description: Creates a named point for performing restore. (restricted to administrators)

    Return type: text

    Note: pg_create_restore_point creates a named transaction log record that can be used as recovery target, and returns the corresponding transaction log location. The given name can then be used with recovery_target_name to specify the point up to which recovery will proceed. Avoid creating multiple restore points with the same name, since recovery will stop at the first one whose name matches the recovery target.

  • pg_current_xlog_location()

    Description: Obtains the write position of the current transaction log.

    Return type: text

    Note: pg_current_xlog_location displays the write position of the current transaction log in the same format as those of the previous functions. Read-only operations do not require rights of the system administrator.

  • pg_current_xlog_insert_location()

    Description: Obtains the insert position of the current transaction log.

    Return type: text

    Note: pg_current_xlog_insert_location displays the insert position of the current transaction log. The insertion point is the "logical" end of the transaction log at any instant, while the write location is the end of what has been written out from the server's internal buffers. The write position is the end that can be detected externally from the server. This operation can be performed to archive only some of completed transaction log files. The insert position is used for commissioning the server. Read-only operations do not require rights of the system administrator.

  • pg_start_backup(label text [, fast boolean ])

    Description: Starts executing online backup (restricted to administrator or replication roles).

    Return type: text

    Note: pg_start_backup receives a user-defined backup label (usually the name of the position where the backup dump file is stored). This function writes a backup label file to the data directory of the database cluster and then returns the starting position of backed up transaction logs in text mode.

    SELECT pg_start_backup('label_goes_here');
     pg_start_backup
    -----------------
     0/3000020
    (1 row)
  • pg_stop_backup()

    Description: Completes online backup (restricted to administrator or replication roles).

    Return type: text

    Note: pg_stop_backup deletes the label file created by pg_start_backup and creates a backup history file in the transaction log archive area. The history file includes the label given to pg_start_backup, the starting and ending transaction log locations for the backup, and the starting and ending times of the backup. The return value is the backup's ending transaction log location. After the ending position is calculated, the insert position of the current transaction log automatically goes ahead to the next transaction log file. This way, the ended transaction log file can be immediately archived so that backup is complete.

  • pg_switch_xlog()

    Description: Switches to a new transaction log file. (restricted to administrators)

    Return type: text

    Note: pg_switch_xlog moves to the next transaction log file so that the current log file can be archived (if continuous archive is used). The returned value is the ending transaction log location + 1 within the just-completed transaction log file. If there has been no transaction log activity since the last transaction log switch, pg_switch_xlog does nothing and returns the start location of the transaction log file currently in use.

  • pg_xlogfile_name(location text)

    Description: Converts the position string in a transaction log to a file name.

    Return type: text

    Note: pg_xlogfile_name extracts just the transaction log file name. If the given transaction log position is the transaction log file border, a transaction log file name is returned for both the two functions. This is usually the desired behavior for managing transaction log archiving behavior, since the preceding file is the last one that currently needs to be archived.

  • pg_xlogfile_name_offset(location text)

    Description: Converts the position string in a transaction log to a file name and returns the byte offset in the file.

    Return type: text, integer

    Note: pg_xlogfile_name_offset can extract transaction log file names and byte offsets from the returned results of the preceding functions. Example:

    SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
    NOTICE:  pg_stop_backup cleanup done, waiting for required WAL segments to be archived
    NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
            file_name         | file_offset 
    --------------------------+-------------
    000000010000000000000003  |         272
    (1 row)
  • pg_xlog_location_diff(location text, location text)

    Description: pg_xlog_location_diff calculates the difference in bytes between two transaction log locations.

    Return type: numeric

Recovery Control Functions

Recovery control functions provide information about the status of the standby. These functions may be executed both during recovery and in normal running.

  • pg_is_in_recovery()

    Description: Returns true if recovery is still in progress.

    Return type: Boolean

  • pg_last_xlog_receive_location()

    Description: Gets last transaction log location received and synced to disk by streaming replication. While streaming replication is in progress this will increase monotonically. If recovery has completed this will remain static at the value of the last WAL record received and synced to disk during recovery. If streaming replication is disabled or if it has not yet started, the function returns NULL.

    Return type: text

  • pg_last_xlog_replay_location()

    Description: Gets last transaction log location replayed during recovery. If recovery is still in progress this will increase monotonically. If recovery has completed then this value will remain static at the value of the last WAL record applied during that recovery. When the server has been started normally without recovery the function returns NULL.

    Return type: text

  • pg_last_xact_replay_timestamp()

    Description: Gets time stamp of last transaction replayed during recovery. This is the time at which the commit or abort WAL record for that transaction was generated on the primary node. If no transactions have been replayed during recovery, this function returns NULL. Otherwise, if recovery is still in progress this will increase monotonically. If recovery has completed then this value will remain static at the value of the last WAL record applied during that recovery. If the server normally starts without manual intervention, this function returns NULL.

    Return type: timestamp with time zone

Recovery control functions control recovery processes. These functions may be executed only during recovery.

  • pg_is_xlog_replay_paused()

    Description: Returns true if recovery is paused.

    Return type: Boolean

  • pg_xlog_replay_pause()

    Description: Pauses recovery immediately.

    Return type: void

  • pg_xlog_replay_resume()

    Description: Restarts recovery if it was paused.

    Return type: void

While recovery is paused no further database changes are applied. If in hot standby, all new queries will see the same consistent snapshot of the database, and no further query conflicts will be generated until recovery is resumed.

If streaming replication is disabled, the paused state may continue indefinitely without problem. While streaming replication is in progress WAL records will continue to be received, which will eventually fill available disk space, depending upon the duration of the pause, the rate of WAL generation and available disk space.

Snapshot Synchronization Functions

The snapshot synchronization functions save the current snapshot and return its identifier.

pg_export_snapshot()

Description: Saves the current snapshot and returns its identifier.

Return type: text

Note: The function pg_export_snapshot saves the current snapshot and returns a text string identifying the snapshot. This string must be passed to clients that want to import the snapshot. Snapshot can be imported when you run the set transaction snapshot snapshot_id; command, but it is only available when the transaction has set the SERIALIZABLE or REPEATABLE READ isolation level. DWS does not support these two isolation levels currently. The output of the function cannot be used as the input of set transaction snapshot.

Database Object Size Functions

Database object size functions calculate the actual disk space used by database objects.

  • pg_column_size(any)

    Description: Number of bytes used to store a particular value (possibly compressed)

    Return type: int

    Note: pg_column_size displays the space for storing an independent data value.

    SELECT pg_column_size(1);
     pg_column_size 
    ----------------
                  4
    (1 row)
  • pg_database_size(oid)

    Description: Specifies the disk space used by the database with the specified OID.

    Return type: bigint

  • pg_database_size(name)

    Description: Specifies the disk space used by the database with the specified name.

    Return type: bigint

    Note: pg_database_size receives the OID or name of a database and returns the disk space used by the corresponding object.

    Example:

    SELECT pg_database_size('postgres');
     pg_database_size 
    ------------------
             51590112
    (1 row)
  • pg_relation_size(oid)

    Description: Specifies the disk space used by the table with a specified OID or index.

    Return type: bigint

  • get_db_source_datasize()

    Description: Estimates the total size of uncompressed data capacity in the non-compressed state of the current database.

    Return type: bigint

    Remarks:

    (1) Analysis must be performed before this function is called.

    (2) Calculate the total data capacity in the non-compressed state by estimating the compression rate of the column-store tables.

    Example:

    analyze;
    ANALYZE
    select get_db_source_datasize();
     get_db_source_datasize
    ------------------------
                35384925667
    (1 row)
  • pg_relation_size(text)

    Description: Specifies the disk space used by the table with a specified name or index. The table name can be modified with a schema name.

    Return type: bigint

  • pg_relation_size(relation regclass, fork text)

    Description: Indicates the disk space used by the specified bifurcating tree ('main', 'fsm, or 'vm') of a certain table or index.

    Return type: bigint

  • pg_relation_size(relation regclass)

    Description: Abbreviation of pg_relation_size(..., 'main')

    Return type: bigint

    Note: pg_relation_size receives the OID or name of a table, index, or compressed table, and returns the size.

  • pg_partition_size(oid,oid)

    Description: Specifies the disk space used by the partition with a specified OID. The first oid is the OID of the table and the second oid is the OID of the partition.

    Return type: bigint

  • pg_partition_size(text, text)

    Description: Specifies the disk space used by the partition with a specified name. The first text is the table name and the second text is the partition name.

    Return type: bigint

  • pg_partition_indexes_size(oid,oid)

    Description: Specifies the disk space used by the index of the partition with a specified OID. The first oid is the OID of the table and the second oid is the OID of the partition.

    Return type: bigint

  • pg_partition_indexes_size(text,text)

    Description: Specifies the disk space used by the index of the partition with a specified name. The first text is the table name and the second text is the partition name.

    Return type: bigint

  • pg_indexes_size(regclass)

    Description: Specifies the total disk space used by the index appended to the specified table.

    Return type: bigint

  • pg_size_pretty(bigint)

    Description: Converts the calculated byte size into a size readable to human beings.

    Return type: text

  • pg_size_pretty(numeric)

    Description: Converts the calculated byte size indicated by a numeral into a size readable to users.

    Return type: text

    Note: pg_size_pretty formats the result of other functions into a human-readable format. KB/MB/GB/TB can be used.

  • pg_table_size(regclass)

    Description: Disk space used by the specified table, excluding indexes (but including TOAST, free space mapping, and visibility mapping)

    Return type: bigint

  • pg_total_relation_size(oid)

    Description: Specifies the disk space used by the table with a specified OID, including the index and the compressed data.

    Return type: bigint

  • pg_total_relation_size(regclass)

    Description: Specifies the total disk space used by the specified table, including all indexes and TOAST data.

    Return type: bigint

  • pg_total_relation_size(text)

    Description: Specifies the disk space used by the table with a specified name, including the index and the compressed data. The table name can be modified with a schema name.

    Return type: bigint

    Note: pg_total_relation_size receives the OID or name of a table or a compressed table, and returns the size of the data, related index, and the compressed table.

Database Object Position Functions

  • pg_relation_filenode(relation regclass)

    Description: ID of a file node with the specified relationship

    Return type: OID

    Description: pg_relation_filenode receives the OID or name of a table, index, sequence, or compressed table, and returns the filenode number allocated to it. The filenode is the basic component of the file name used by the relationship. For most tables, the result is the same as pg_class.relfilenode. For the specified system directory, relfilenode is 0 and this function must be used to obtain the correct value. If a relationship that is not stored is transmitted, such as a view, this function returns NULL.

  • pg_relation_filepath(relation regclass)

    Description: Name of a file path with the specified relationship

    Return type: text

    Description: pg_relation_filepath is similar to pg_relation_filenode, the difference is that pg_relation_filepath returns the whole file path name for the relationship (relative to the data directory PGDATA of the database cluster).

Advisory Lock Functions

Advisory lock functions manage advisory locks. Those functions are only for internal use currently.

  • pg_advisory_lock(key bigint)

    Description: Obtains exclusive session level advisory lock.

    Return type: void

    Note: pg_advisory_lock locks resources defined by an application. The resources can be identified using a 64-bit or two unoverlapped 32-bit key values. If another session locks the resources, the function blocks the resources until they can be used. The lock is exclusive. Multiple locking requests are pushed into the stack. Therefore, if the same resource is locked three times, it must be unlocked three times so that it is released to another session.

  • pg_advisory_lock(key1 int, key2 int)

    Description: Obtains exclusive session level advisory lock.

    Return type: void

  • pg_advisory_lock_shared(key bigint)

    Description: Obtains shared session level advisory lock.

    Return type: void

  • pg_advisory_lock_shared(key1 int, key2 int)

    Description: Obtains shared session level advisory lock.

    Return type: void

    Note: pg_advisory_lock_shared works in the same way as pg_advisory_lock, except the lock can be shared with other sessions requesting shared locks. Only would-be exclusive lockers are locked out.

  • pg_advisory_unlock(key bigint)

    Description: Releases an exclusive session level advisory lock.

    Return type: Boolean

  • pg_advisory_unlock(key1 int, key2 int)

    Description: Releases an exclusive session level advisory lock.

    Return type: Boolean

    Note: pg_advisory_unlock releases the obtained exclusive advisory lock. If the release is successful, the function returns true. If the lock was not held, it will return false. In addition, an SQL warning will be reported by the server.

  • pg_advisory_unlock_shared(key bigint)

    Description: Releases a shared session level advisory lock.

    Return type: Boolean

  • pg_advisory_unlock_shared(key1 int, key2 int)

    Description: Releases a shared session level advisory lock.

    Return type: Boolean

    Note: pg_advisory_unlock_shared works in the same way as pg_advisory_unlock, except it releases a shared session level advisory lock.

  • pg_advisory_unlock_all()

    Description: Releases all advisory locks owned by the current session.

    Return type: void

    Note: pg_advisory_unlock_all releases all advisory locks owned by the current session. The function is implicitly invoked when the session ends even if the client is abnormally disconnected.

  • pg_advisory_xact_lock(key bigint)

    Description: Obtains exclusive transaction level advisory lock.

    Return type: void

  • pg_advisory_xact_lock(key1 int, key2 int)

    Description: Obtains exclusive transaction level advisory lock.

    Return type: void

    Note: pg_advisory_xact_lock works in the same way as pg_advisory_lock, except the lock is automatically released at the end of the current transaction and cannot be released explicitly.

  • pg_advisory_xact_lock_shared(key bigint)

    Description: Obtains shared transaction level advisory lock.

    Return type: void

  • pg_advisory_xact_lock_shared(key1 int, key2 int)

    Description: Obtains shared transaction level advisory lock.

    Return type: void

    Note: pg_advisory_xact_lock_shared works in the same way as pg_advisory_lock_shared, except the lock is automatically released at the end of the current transaction and cannot be released explicitly.

  • pg_try_advisory_lock(key bigint)

    Description: Obtains exclusive session level advisory lock if available.

    Return type: Boolean

    Note: pg_try_advisory_lock is similar to pg_advisory_lock. The only difference between them is that pg_try_advisory_lock does not block the resource until the resource is released. pg_try_advisory_lock either immediately obtains the lock and returns true or returns false, which indicates the lock cannot be performed currently.

  • pg_try_advisory_lock(key1 int, key2 int)

    Description: Obtains exclusive session level advisory lock if available.

    Return type: Boolean

  • pg_try_advisory_lock_shared(key bigint)

    Description: Obtains shared session level advisory lock if available.

    Return type: Boolean

  • pg_try_advisory_lock_shared(key1 int, key2 int)

    Description: Obtains shared session level advisory lock if available.

    Return type: Boolean

    Note: pg_try_advisory_lock_shared is similar to pg_try_advisory_lock. The only difference between them is that pg_try_advisory_lock_shared attempts to obtain a shared lock instead of an exclusive lock.

  • pg_try_advisory_xact_lock(key bigint)

    Description: Obtains exclusive transaction level advisory lock if available.

    Return type: Boolean

  • pg_try_advisory_xact_lock(key1 int, key2 int)

    Description: Obtains exclusive transaction level advisory lock if available.

    Return type: Boolean

    Note: pg_try_advisory_xact_lock works in the same way as pg_try_advisory_lock, except the lock, if acquired, is automatically released at the end of the current transaction and cannot be released explicitly.

  • pg_try_advisory_xact_lock_shared(key bigint)

    Description: Obtains shared transaction level advisory lock if available.

    Return type: Boolean

  • pg_try_advisory_xact_lock_shared(key1 int, key2 int)

    Description: Obtains shared transaction level advisory lock if available.

    Return type: Boolean

    Note: pg_try_advisory_xact_lock_shared works in the same way as pg_try_advisory_lock_shared, except the lock, if acquired, is automatically released at the end of the current transaction and cannot be released explicitly.

New Functions

  • pgxc_pool_check()

    Description: Checks whether the connection data buffered in the pool is consistent with the pgxc_node.

    Return type: Boolean

  • pgxc_pool_reload()

    Description: Updates the connection information buffered in the pool.

    Return type: Boolean

  • pgxc_lock_for_backup()

    Description: Locks a cluster for backup.

    Return type: Boolean

  • pg_pool_validate(bool)

    Description: Clears invalid backend threads on CN. (Those backend threads hold an invalid pooler connection to a standby DN.)

    Return type: record

  • pg_nodes_memory()

    Description: Queries memory usage of all nodes.

    Return type: record

  • table_skewness(text)

    Description: Queries the percentage of table data among all nodes.

    Indicates that the type of the name of the to-be-queried table is text.

    Return type: record

  • plan_seed

    Description: Obtains the seed value of the previously queried statement (internal use).

    Return type: int

  • pg_stat_get_env

    Description: Obtains the environmental variable information about the current node.

    Return type: record

  • pg_stat_get_thread

    Description: Provides information about the status of all threads under the current node.

    Return type: record

  • pgxc_get_os_threads

    Description: Provides information about the threads under all normal nodes in a cluster.

    Return type: record

  • pg_stat_get_sql_count

    Description: Provides the counts of the SELECT, UPDATE, INSERT, DELETE, and MERGE INTO SQL statements executed on the current node.

    Return type: record

  • pgxc_get_sql_count

    Description: Provides the counts of the SELECT, UPDATE, INSERT, DELETE, and MERGE INTO SQL statements executed on all the nodes in the cluster.

    Return type: record

  • pgxc_get_node_env

    Description: Provides the environmental variable information about all nodes in a cluster.

    Return type: record

  • gs_switch_relfilenode

    Description: Exchanges meta information of two tables or partitions. (This is only used for the redistribution tool. An error message is displayed when used by users).

    Return type: int

  • copy_error_log_create()

    Description: Creates the error table (public.pgxc_copy_error_log) required for creating the COPY FROM error tolerance mechanism.

    Return type: Boolean

    NOTE:
    • The function creates the public.pgxc_copy_error_log table (columns defined as relname varchar, begintime timestamptz, filename varchar, rownum int8, rawrecord text, and detail text), creates the B-tree index on the relname column, and controls the permissions on the error table using REVOKE ALL on public.pgxc_copy_error_log FROM public. (The permissions are the same as those on the COPY statement.)
    • Same as the error table and the COPY statement, the function requires sysadmin or higher permissions.
    • If the public.pgxc_copy_error_log table or the copy_error_log_relname_idx index already exists before the function creates it, the function will report an error and roll back.
  • pg_stat_get_data_senders()

    Description: Provides detailed information about the data-copy sending thread active at the moment.

    Return type: record

  • pv_compute_pool_workload()

    Description: Provides the current load information about computing Node Groups on cloud.

    Return type: record

NOTE:

pgxc_lock_for_backup locks the cluster before using gs_dump or gs_dumpall to back up the cluster. After a cluster is locked, operations changing the system structure are not allowed. This function does not affect the DML statement.