Functions Admin – ProtonBase 文档

System Administration Functions

Configuration Settings Functions
Server Signaling Functions
Database Object Size Functions

The functions described in this section are used to control and monitor a ProtonBase installation.

Configuration Settings Functions

Table 8.67 shows the functions available to query and alter run-time configuration parameters.

Table Configuration Settings Functions

Function Description Example(s)

Function Description Example(s)
`current_setting` ( `setting_name` `text` [, `missing_ok` `boolean` ] ) → `text` Returns the current value of the setting `setting_name`. If there is no such setting, `current_setting` throws an error unless `missing_ok` is supplied and is `true` (in which case NULL is returned). This function corresponds to the SQL command SHOW. `current_setting('datestyle')` → `ISO, MDY`
`set_config` ( `setting_name` `text`, `new_value` `text`, `is_local` `boolean` ) → `text` Sets the parameter `setting_name` to `new_value`, and returns that value. If `is_local` is `true`, the new value will only apply during the current transaction. If you want the new value to apply for the rest of the current session, use `false` instead. This function corresponds to the SQL command SET. `set_config('log_statement_stats', 'off', false)` → `off`

current_setting ( setting_name text [, missing_ok boolean ] ) → text

Returns the current value of the setting setting_name. If there is no such setting, current_setting throws an error unless missing_ok is supplied and is true (in which case NULL is returned). This function corresponds to the SQL command SHOW.

current_setting('datestyle') → ISO, MDY

set_config ( setting_name text, new_value text, is_local boolean ) → text

Sets the parameter setting_name to new_value, and returns that value. If is_local is true, the new value will only apply during the current transaction. If you want the new value to apply for the rest of the current session, use false instead. This function corresponds to the SQL command SET.

set_config('log_statement_stats', 'off', false) → off

Server Signaling Functions

The functions shown in Table 8.68 send control signals to other server processes. Use of these functions is restricted to superusers by default but access may be granted to others using GRANT, with noted exceptions.

Each of these functions returns true if the signal was successfully sent and false if sending the signal failed.

Table Server Signaling Functions

Function Description

Function Description
`pg_cancel_backend` ( `pid` `integer` ) → `boolean` Cancels the current query of the session whose backend process has the specified process ID. This is also allowed if the calling role is a member of the role whose backend is being canceled or the calling role has been granted `pg_signal_backend`, however only superusers can cancel superuser backends.
`pg_terminate_backend` ( `pid` `integer`, `timeout` `bigint` `DEFAULT` `0` ) → `boolean` Terminates the session whose backend process has the specified process ID. This is also allowed if the calling role is a member of the role whose backend is being terminated or the calling role has been granted `pg_signal_backend`, however only superusers can terminate superuser backends. If `timeout` is not specified or zero, this function returns `true` whether the process actually terminates or not, indicating only that the sending of the signal was successful. If the `timeout` is specified (in milliseconds) and greater than zero, the function waits until the process is actually terminated or until the given time has passed. If the process is terminated, the function returns `true`. On timeout, a warning is emitted and `false` is returned.

pg_cancel_backend ( pid integer ) → boolean

Cancels the current query of the session whose backend process has the specified process ID. This is also allowed if the calling role is a member of the role whose backend is being canceled or the calling role has been granted pg_signal_backend, however only superusers can cancel superuser backends.

pg_terminate_backend ( pid integer, timeout bigint DEFAULT 0 ) → boolean

Terminates the session whose backend process has the specified process ID. This is also allowed if the calling role is a member of the role whose backend is being terminated or the calling role has been granted pg_signal_backend, however only superusers can terminate superuser backends.

If timeout is not specified or zero, this function returns true whether the process actually terminates or not, indicating only that the sending of the signal was successful. If the timeout is specified (in milliseconds) and greater than zero, the function waits until the process is actually terminated or until the given time has passed. If the process is terminated, the function returns true. On timeout, a warning is emitted and false is returned.

pg_cancel_backend and pg_terminate_backend send signals (SIGINT or SIGTERM respectively) to backend processes identified by process ID. The process ID of an active backend can be found from the pid column of the pg_stat_activity view, or by listing the postgres processes on the server (using ps on Unix or the Task Manager on Windows). The role of an active backend can be found from the usename column of the pg_stat_activity view.

Database Object Size Functions

The functions shown in Table 8.69 calculate the on-disk size of database objects.

Table Database Object Size Functions

Function Description Example(s)

Function Description Example(s)
`pg_database_size` ( `name` `text` ) → `bigint` `pg_database_size` ( `oid` ) → `bigint` Returns the total disk space used by the database with the specified name or OID. To use this function, you must have `CONNECT` privilege on the specified database (which is granted by default) or be a member of the `pg_read_all_stats` role. `pg_database_size('postgres')` → `73879963`
`pg_table_size` ( `regclass` ) → `bigint` Returns the disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map). The table can be specified by name or by OID. `pg_table_size('mytable')` → `3626240`
`pg_relation_size` ( `relation` `regclass` [, `fork` `text` ] ) → `bigint` Returns the on-disk size in bytes of one fork of the specified relation. The relation can be a table or an index, and is specified by name or by OID. The fork can be specified by name (`'main'`, `'fsm'`, `'vm'`, or `'init'`), or omitted to get the size of the main data fork. `pg_relation_size('mytable')` → `3626240` `pg_relation_size('mytable', 'main')` → `3626240`

pg_database_size ( name text ) → bigint

pg_database_size ( oid ) → bigint

Returns the total disk space used by the database with the specified name or OID. To use this function, you must have CONNECT privilege on the specified database (which is granted by default) or be a member of the pg_read_all_stats role.

pg_database_size('postgres') → 73879963

pg_table_size ( regclass ) → bigint

Returns the disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map). The table can be specified by name or by OID.

pg_table_size('mytable') → 3626240

pg_relation_size ( relation regclass [, fork text ] ) → bigint

Returns the on-disk size in bytes of one fork of the specified relation. The relation can be a table or an index, and is specified by name or by OID. The fork can be specified by name ('main', 'fsm', 'vm', or 'init'), or omitted to get the size of the main data fork.

pg_relation_size('mytable') → 3626240

pg_relation_size('mytable', 'main') → 3626240

The functions shown in this table operate on database objects. To get the size of a table including indexes and TOAST data, use pg_total_relation_size. To format the result in a human-readable format, use pg_size_pretty.