Manage and monitor the system with built-in functions - PolarDB

These functions control and monitor a PostgreSQL installation. They cover configuration management, server signaling, backup and recovery, replication, database object management, file access, and advisory locking.

Configuration settings functions

The following functions query and alter run-time configuration parameters.

Function	Description	Example
`current_setting(setting_name text [, missing_ok boolean])` → `text`	Returns the current value of `setting_name`. Throws an error if no such setting exists, unless `missing_ok` is `true` (in which case `NULL` is returned). Equivalent to the SQL command SHOW.	`current_setting('datestyle')` → `ISO, MDY`
`set_config(setting_name text, new_value text, is_local boolean)` → `text`	Sets `setting_name` to `new_value` and returns that value. If `is_local` is `true`, the new value applies only for the current transaction. Pass `false` to apply it for the rest of the current session. Equivalent to the SQL command SET.	`set_config('log_statement_stats', 'off', false)` → `off`

Server signaling functions

The following functions send control signals to other server processes. They return true if the signal was sent successfully, false otherwise.

By default, these functions are restricted to superusers. Access can be granted to other roles with GRANT, with the exceptions noted below.

Function	Description
`pg_cancel_backend(pid integer)` → `boolean`	Cancels the current query of the session identified by the process ID. Also allowed if the calling role is a member of the target role or has been granted `pg_signal_backend`. Only superusers can cancel superuser backends.
`pg_reload_conf()` → `boolean`	Causes all PostgreSQL server processes to reload their configuration files by sending `SIGHUP` to the postmaster, which then sends `SIGHUP` to each child. Check the `pg_file_settings` and `pg_hba_file_rules` views for errors before reloading.
`pg_rotate_logfile()` → `boolean`	Signals the log-file manager to switch to a new output file immediately. Only works when the built-in log collector is running.
`pg_terminate_backend(pid integer)` → `boolean`	Terminates the session identified by the process ID. Also allowed if the calling role is a member of the target role or has been granted `pg_signal_backend`. Only superusers can terminate superuser backends.

pg_cancel_backend sends SIGINT; pg_terminate_backend sends SIGTERM.

To find the process ID and role of an active backend, query the pg_stat_activity view:

SELECT pid, usename, query
FROM pg_stat_activity
WHERE state = 'active';

On Unix, you can also list active postgres processes with ps. On Windows, use Task Manager.

Backup control functions

The following functions support online backups. Most cannot be called during recovery — the exceptions are non-exclusive pg_start_backup, non-exclusive pg_stop_backup, pg_is_in_backup, pg_backup_start_time, and pg_wal_lsn_diff.

Function	Description
`pg_create_restore_point(name text)` → `pg_lsn`	Creates a named marker in the write-ahead log (WAL) that can later be used as a recovery target with `recovery_target_name`. Avoid creating multiple restore points with the same name — recovery stops at the first match. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
`pg_current_wal_flush_lsn()` → `pg_lsn`	Returns the current WAL flush location. See the notes below.
`pg_current_wal_insert_lsn()` → `pg_lsn`	Returns the current WAL insert location. See the notes below.
`pg_current_wal_lsn()` → `pg_lsn`	Returns the current WAL write location. See the notes below.
`pg_start_backup(label text [, fast boolean [, exclusive boolean]])` → `pg_lsn`	Prepares the server to begin an online backup. The `label` is an arbitrary name used to identify the backup. Setting `fast` to `true` forces an immediate checkpoint, which causes an I/O spike and may slow concurrent queries. The default backup mode is exclusive. In exclusive mode, the function writes `backup_label` (and `tablespace_map` if `pg_tblspc/` contains links) to the data directory. In non-exclusive mode, these file contents are returned by `pg_stop_backup` instead. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
`pg_stop_backup(exclusive boolean [, wait_for_archive boolean])` → `setof record(lsn pg_lsn, labelfile text, spcmapfile text)`	Finishes an exclusive or non-exclusive online backup. The `exclusive` parameter must match the earlier `pg_start_backup` call. In exclusive mode, removes the `backup_label` and `tablespace_map` files. In non-exclusive mode, returns their contents for you to write to the backup area. If `wait_for_archive` is `false`, the function returns immediately without waiting for WAL to be archived — use only if your backup software independently monitors WAL archiving. By default (`true`), waits for WAL archiving when archiving is enabled. On a standby, this means it will wait only when `archive_mode = always`; if write activity on the primary is low, it may be useful to run `pg_switch_wal` on the primary to trigger an immediate segment switch. On the primary, also creates a backup history file in the WAL archive area. The `lsn` column holds the backup's ending WAL location. The `labelfile` and `spcmapfile` columns are `NULL` when ending an exclusive backup; after a non-exclusive backup they hold the desired contents of the label and tablespace map files. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
`pg_stop_backup()` → `pg_lsn`	Simplified version equivalent to `pg_stop_backup(true, true)`. Returns only the `pg_lsn` result. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
`pg_is_in_backup()` → `boolean`	Returns `true` if an exclusive online backup is in progress.
`pg_backup_start_time()` → `timestamp with time zone`	Returns the start time of the current exclusive online backup, or `NULL` if none is in progress.
`pg_switch_wal()` → `pg_lsn`	Forces the server to switch to a new WAL file, allowing the current file to be archived (requires continuous archiving). Returns the ending WAL location plus 1 within the just-completed file. If no WAL activity has occurred since the last switch, does nothing and returns the start of the current file. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
`pg_walfile_name(lsn pg_lsn)` → `text`	Converts a WAL location to the name of the WAL file holding that location.
`pg_walfile_name_offset(lsn pg_lsn)` → `record(file_name text, file_offset integer)`	Converts a WAL location to a WAL file name and byte offset within that file.
`pg_wal_lsn_diff(lsn1 pg_lsn, lsn2 pg_lsn)` → `numeric`	Calculates the difference in bytes (`lsn1 - lsn2`) between two WAL locations. Useful with `pg_stat_replication` to compute replication lag.

WAL location types

The three pg_current_wal_* functions report different points in the WAL pipeline:

Write location (pg_current_wal_lsn): the end of what has been written out from the server's internal buffers. Use this when archiving partially-complete WAL files.
Insert location (pg_current_wal_insert_lsn): the logical end of the WAL at any instant. Primarily for debugging.
Flush location (pg_current_wal_flush_lsn): the last location confirmed written to durable storage. Primarily for debugging.

All three are read-only and do not require superuser permissions.

When the given WAL location falls exactly on a file boundary, both pg_walfile_name and pg_walfile_name_offset return the name of the preceding file — the correct behavior for WAL archiving management, since that file is the last one that needs to be archived.

Use pg_walfile_name_offset to extract the file name and byte offset from a pg_lsn value. For example, after stopping a backup:

SELECT * FROM pg_walfile_name_offset(pg_stop_backup());

Recovery control functions

Recovery information functions

The following functions report the current status of a standby server. They can be called during both recovery and normal operation.

Function	Description
`pg_is_in_recovery()` → `boolean`	Returns `true` if recovery is still in progress.
`pg_last_wal_receive_lsn()` → `pg_lsn`	Returns the last WAL location received and synced to disk by streaming replication. Increases monotonically while streaming is active. Remains static once recovery completes. Returns `NULL` if streaming replication is disabled or has not yet started.
`pg_last_wal_replay_lsn()` → `pg_lsn`	Returns the last WAL location replayed during recovery. Increases monotonically while recovery is in progress. Remains static once recovery completes. Returns `NULL` when the server starts normally without recovery.
`pg_last_xact_replay_timestamp()` → `timestamp with time zone`	Returns the timestamp of the last transaction replayed during recovery — the time the commit or abort WAL record was generated on the primary. Returns `NULL` if no transactions have been replayed, or when the server starts normally without recovery. Remains static once recovery completes.

Recovery control functions

The following functions control the recovery process. They can only be called during recovery.

Function	Description
`pg_is_wal_replay_paused()` → `boolean`	Returns `true` if a recovery pause has been requested.
`pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60)` → `boolean`	Promotes a standby server to primary. With `wait` set to `true` (default), waits until promotion completes or `wait_seconds` seconds have passed, returning `true` on success and `false` on timeout. With `wait` set to `false`, returns `true` immediately after sending `SIGUSR1` to the postmaster. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
`pg_wal_replay_pause()` → `void`	Requests a pause of recovery. Recovery does not stop immediately — call `pg_get_wal_replay_pause_state()` to confirm the actual pause state. While paused, no database changes are applied; if hot standby is active, all new queries see the same consistent snapshot and no further query conflicts are generated. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
`pg_wal_replay_resume()` → `void`	Restarts recovery if it was paused. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.

pg_wal_replay_pause and pg_wal_replay_resume cannot be called while a promotion is in progress. If promotion is triggered while recovery is paused, the pause ends and promotion continues.

If streaming replication is disabled, recovery can remain paused indefinitely without issue. If streaming replication is active during a pause, WAL records continue to arrive and will eventually fill available disk space — the risk depends on the pause duration, WAL generation rate, and available disk.

Snapshot synchronization functions

PostgreSQL lets database sessions synchronize their snapshots. A *snapshot* determines which data is visible to a transaction. Synchronized snapshots are necessary when two or more sessions need to see identical database content.

Without synchronization, a third transaction could commit between two sessions' START TRANSACTION commands, causing the sessions to see different data. To solve this, one session can export its snapshot; other sessions import it to see exactly the same view of the database.

Database changes made by any participant remain invisible to the others, as with any uncommitted transaction. The transactions are synchronized with respect to pre-existing data only.

Snapshots are exported with pg_export_snapshot and imported with the SET TRANSACTION command.

Function Description

pg_export_snapshot() → text Saves the transaction's current snapshot and returns a text identifier. Pass this identifier to other sessions (outside the database) so they can import it with SET TRANSACTION SNAPSHOT. The snapshot is available for import only until the exporting transaction ends. A transaction can export more than one snapshot, but this is only meaningful in READ COMMITTED transactions — in REPEATABLE READ and higher isolation levels, the same snapshot is used throughout. A transaction that has exported a snapshot cannot use PREPARE TRANSACTION.

Function	Description
`pg_export_snapshot()` → `text`	Saves the transaction's current snapshot and returns a text identifier. Pass this identifier to other sessions (outside the database) so they can import it with `SET TRANSACTION SNAPSHOT`. The snapshot is available for import only until the exporting transaction ends. A transaction can export more than one snapshot, but this is only meaningful in `READ COMMITTED` transactions — in `REPEATABLE READ` and higher isolation levels, the same snapshot is used throughout. A transaction that has exported a snapshot cannot use PREPARE TRANSACTION.

Replication management functions

The following functions control and interact with replication features.

Functions for replication origins are restricted to superusers by default.
Functions for replication slots are restricted to superusers and roles with the REPLICATION privilege.

Many of these functions have equivalent commands in the replication protocol.

Function	Description
`pg_create_physical_replication_slot(slot_name name [, immediately_reserve boolean, temporary boolean])` → `record(slot_name name, lsn pg_lsn)`	Creates a physical replication slot. If `immediately_reserve` is `true`, the LSN is reserved immediately; otherwise it is reserved on first connection from a streaming replication client. If `temporary` is `true`, the slot is not stored to disk and is released on any error. Corresponds to `CREATE_REPLICATION_SLOT ... PHYSICAL`.
`pg_drop_replication_slot(slot_name name)` → `void`	Drops a physical or logical replication slot. For logical slots, call this while connected to the same database where the slot was created. Corresponds to `DROP_REPLICATION_SLOT`.
`pg_create_logical_replication_slot(slot_name name, plugin name [, temporary boolean])` → `record(slot_name name, lsn pg_lsn)`	Creates a logical (decoding) replication slot using the specified output plugin. If `temporary` is `true`, the slot is not stored to disk and is released on any error. The optional fourth parameter, `twophase`, when `true`, enables decoding of prepared transactions. Corresponds to `CREATE_REPLICATION_SLOT ... LOGICAL`.
`pg_copy_physical_replication_slot(src_slot_name name, dst_slot_name name [, temporary boolean])` → `record(slot_name name, lsn pg_lsn)`	Copies a physical replication slot. The copy starts reserving WAL from the same LSN as the source. If `temporary` is omitted, the source slot's value is used.
`pg_copy_logical_replication_slot(src_slot_name name, dst_slot_name name [, temporary boolean [, plugin name]])` → `record(slot_name name, lsn pg_lsn)`	Copies a logical replication slot, optionally changing the output plugin and persistence. The copy starts from the same LSN as the source. Omitted parameters default to the source slot's values.
`pg_logical_slot_get_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges integer, VARIADIC options text[])` → `setof record(lsn pg_lsn, xid xid, data text)`	Returns changes in the slot starting from the last consumed point, consuming them. If both `upto_lsn` and `upto_nchanges` are `NULL`, decoding continues to the end of WAL. If `upto_lsn` is non-`NULL`, only transactions committing before that LSN are included. If `upto_nchanges` is non-`NULL`, decoding stops when the row count exceeds the value (actual rows returned may be slightly larger, as the limit is checked after each transaction).
`pg_logical_slot_peek_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges integer, VARIADIC options text[])` → `setof record(lsn pg_lsn, xid xid, data text)`	Same as `pg_logical_slot_get_changes`, except changes are not consumed — they are returned again on subsequent calls.
`pg_logical_slot_get_binary_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges integer, VARIADIC options text[])` → `setof record(lsn pg_lsn, xid xid, data bytea)`	Same as `pg_logical_slot_get_changes`, but returns changes as `bytea`.
`pg_logical_slot_peek_binary_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges integer, VARIADIC options text[])` → `setof record(lsn pg_lsn, xid xid, data bytea)`	Same as `pg_logical_slot_peek_changes`, but returns changes as `bytea`.
`pg_replication_slot_advance(slot_name name, upto_lsn pg_lsn)` → `record(slot_name name, end_lsn pg_lsn)`	Advances the confirmed position of a replication slot. The slot cannot be moved backward or beyond the current insert location. The updated position is written at the next checkpoint; on a crash, the slot may revert to an earlier position.
`pg_replication_origin_create(node_name text)` → `oid`	Creates a replication origin with the given external name and returns its internal ID.
`pg_replication_origin_drop(node_name text)` → `void`	Deletes a replication origin, including any associated replay progress.
`pg_replication_origin_oid(node_name text)` → `oid`	Looks up a replication origin by name and returns its internal ID, or `NULL` if not found.
`pg_replication_origin_session_setup(node_name text)` → `void`	Marks the current session as replaying from the given origin. Only allowed when no origin is currently selected. Use `pg_replication_origin_session_reset` to undo.
`pg_replication_origin_session_reset()` → `void`	Cancels the effect of `pg_replication_origin_session_setup`.
`pg_replication_origin_session_is_setup()` → `boolean`	Returns `true` if a replication origin is selected in the current session.
`pg_replication_origin_session_progress(flush boolean)` → `pg_lsn`	Returns the replay location for the replication origin selected in the current session. The `flush` parameter controls whether the corresponding local transaction is guaranteed to have been flushed to disk.
`pg_replication_origin_xact_setup(origin_lsn pg_lsn, origin_timestamp timestamp with time zone)` → `void`	Marks the current transaction as replaying a transaction committed at the given LSN and timestamp. Requires a replication origin to be selected via `pg_replication_origin_session_setup`.
`pg_replication_origin_xact_reset()` → `void`	Cancels the effect of `pg_replication_origin_xact_setup`.
`pg_replication_origin_advance(node_name text, lsn pg_lsn)` → `void`	Sets the replication progress for the given node to the given LSN. Primarily for setting up an initial location or adjusting after configuration changes. Careless use can lead to inconsistently replicated data.
`pg_replication_origin_progress(node_name text, flush boolean)` → `pg_lsn`	Returns the replay location for the given replication origin.
`pg_logical_emit_message(transactional boolean, prefix text, content text)` → `pg_lsn`	Emits a logical decoding message through WAL. If `transactional` is `true`, the message is part of the current transaction; if `false`, it is written immediately and decoded as soon as the logical decoder reads the record. The `prefix` identifies the message to logical decoding plugins. The `content` can be text or binary (`bytea`).
`pg_logical_emit_message(transactional boolean, prefix text, content bytea)` → `pg_lsn`	Binary variant of `pg_logical_emit_message`.

Database object management functions

The following functions calculate disk space usage for database objects or help interpret usage results. All bigint results are in bytes. Passing an OID that does not correspond to an existing object returns NULL.

Function	Description
`pg_column_size("any")` → `integer`	Returns the number of bytes used to store an individual data value. When applied to a table column value, reflects any compression applied.
`pg_database_size(name)` → `bigint` / `pg_database_size(oid)` → `bigint`	Returns the total disk space used by the database with the given name or OID. Requires `CONNECT` privilege on the database (granted by default) or membership in the `pg_read_all_stats` role.
`pg_indexes_size(regclass)` → `bigint`	Returns the total disk space used by all indexes attached to the given table.
`pg_relation_size(relation regclass [, fork text])` → `bigint`	Returns the disk space used by one fork of the given relation. With one argument, returns the main data fork. The `fork` parameter accepts: `main` (main data fork), `fsm` (Free Space Map), `vm` (Visibility Map), or `init` (initialization fork). For most use cases, `pg_total_relation_size` or `pg_table_size` is more convenient.
`pg_size_bytes(text)` → `bigint`	Converts a human-readable size string (as returned by `pg_size_pretty`) into bytes.
`pg_size_pretty(bigint)` → `text` / `pg_size_pretty(numeric)` → `text`	Converts a size in bytes to a human-readable format with appropriate units (bytes, kB, MB, GB, or TB). Units are powers of 2: 1 kB = 1,024 bytes, 1 MB = 1,048,576 bytes.
`pg_table_size(regclass)` → `bigint`	Returns the disk space used by the given table, excluding indexes but including its TOAST table (if any), free space map, and visibility map.
`pg_tablespace_size(name)` → `bigint` / `pg_tablespace_size(oid)` → `bigint`	Returns the total disk space used in the given tablespace. Requires `CREATE` privilege on the tablespace or membership in the `pg_read_all_stats` role, unless it is the current database's default tablespace.
`pg_total_relation_size(regclass)` → `bigint`	Returns the total disk space used by the given table, including all indexes and TOAST data. Equivalent to `pg_table_size + pg_indexes_size`.

Functions that accept a regclass argument take the OID of a table or index from the pg_class system catalog. The regclass type's input converter resolves table and index names automatically — no manual OID lookup required.

Database object location functions

The following functions identify the disk files associated with database objects.

Function	Description
`pg_relation_filenode(relation regclass)` → `oid`	Returns the filenode number assigned to the given relation. For most relations, this matches `pg_class.relfilenode`. For certain system catalogs where `relfilenode` is zero, use this function to get the correct value. Returns `NULL` for relations with no storage (such as views).
`pg_relation_filepath(relation regclass)` → `text`	Returns the full file path of the relation, relative to the database cluster's data directory (`PGDATA`).
`pg_filenode_relation(tablespace oid, filenode oid)` → `regclass`	Returns the OID of the relation stored at the given tablespace OID and filenode — the inverse of `pg_relation_filepath`. Specify the tablespace as zero for the database's default tablespace. Returns `NULL` if no matching relation is found in the current database.

Collation management functions

The following functions manage collations.

Function Description

pg_collation_actual_version(oid) → text Returns the version of the collation object as currently installed in the operating system. If this differs from pg_collation.collversion, objects that depend on the collation may need to be rebuilt.

pg_import_system_collations(schema regnamespace) → integer Adds collations to the pg_collation catalog based on locales found in the operating system — the same operation performed by initdb. Run again after installing new OS locales to pick them up. Skips locales already in pg_collation; does not remove collations for locales no longer present in the OS. The schema parameter is typically pg_catalog, but collations can be installed into any schema. Returns the count of new collation objects created. Restricted to superusers.

Function	Description
`pg_collation_actual_version(oid)` → `text`	Returns the version of the collation object as currently installed in the operating system. If this differs from `pg_collation.collversion`, objects that depend on the collation may need to be rebuilt.
`pg_import_system_collations(schema regnamespace)` → `integer`	Adds collations to the `pg_collation` catalog based on locales found in the operating system — the same operation performed by `initdb`. Run again after installing new OS locales to pick them up. Skips locales already in `pg_collation`; does not remove collations for locales no longer present in the OS. The `schema` parameter is typically `pg_catalog`, but collations can be installed into any schema. Returns the count of new collation objects created. Restricted to superusers.

Partitioning information functions

The following functions report on the structure of partitioned tables.

Function	Description
`pg_partition_tree(regclass)` → `setof record(relid regclass, parentrelid regclass, isleaf boolean, level integer)`	Lists all partitions in the partition tree of the given partitioned table or index, one row per partition. The `level` field is 0 for the root, 1 for immediate children, 2 for their children, and so on. Returns no rows if the relation does not exist or is not a partition or partitioned table.
`pg_partition_ancestors(regclass)` → `setof regclass`	Lists the ancestor relations of the given partition, including the relation itself. Returns no rows if the relation does not exist or is not a partition or partitioned table.
`pg_partition_root(regclass)` → `regclass`	Returns the top-most parent of the partition tree containing the given relation. Returns `NULL` if the relation does not exist or is not a partition or partitioned table.

To get the total size of data across all partitions, use pg_partition_tree with pg_relation_size:

SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
FROM pg_partition_tree('measurement');

Index maintenance functions

The following functions handle index maintenance tasks. Autovacuum normally performs these tasks automatically — use these functions only in special cases. They cannot be called during recovery and are restricted to superusers and the owner of the given index.

Function	Description
`brin_summarize_new_values(index regclass)` → `integer`	Scans the given BRIN (Block Range Index) index for page ranges not yet summarized, creates summary tuples for those ranges, and returns the count of new summaries inserted.
`brin_summarize_range(index regclass, blockNumber bigint)` → `integer`	Summarizes the page range covering the given block number, if not already summarized. Similar to `brin_summarize_new_values` but limited to the single page range covering the specified block.
`brin_desummarize_range(index regclass, blockNumber bigint)` → `void`	Removes the BRIN index tuple summarizing the page range covering the given table block, if one exists.
`gin_clean_pending_list(index regclass)` → `bigint`	Cleans the pending list of the given GIN (Generalized Inverted Index) index by moving entries in bulk to the main GIN data structure. Returns the number of pages removed. Returns zero if the index was built with the `fastupdate` option disabled, since no pending list exists.

Generic file access functions

The following functions provide direct access to files on the server host. Access is limited to files within the database cluster directory and the log_directory, unless the caller is a superuser or has been granted the pg_read_server_files role.

Warning

Granting EXECUTE on pg_read_file or related functions lets users read any file accessible to the database server process, bypassing all in-database privilege checks. This includes pg_authid (which stores authentication information) and any table data. Grant access to these functions carefully.

Some functions accept an optional missing_ok parameter. When true, the function returns NULL or an empty result set if the file or directory does not exist. When false (the default), an error is raised.

Function	Description
`pg_ls_dir(dirname text [, missing_ok boolean, include_dot_dirs boolean])` → `setof text`	Returns the names of all files, directories, and special files in the given directory. By default, `.` and `..` entries are excluded. Set `include_dot_dirs` to `true` to include them — useful when `missing_ok` is `true` to distinguish an empty directory from a nonexistent one. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
`pg_ls_logdir()` → `setof record(name text, size bigint, modification timestamp with time zone)`	Returns the name, size, and last modification time of each ordinary file in the server's log directory. Excludes dot-prefixed filenames, directories, and special files. Restricted to superusers and members of the `pg_monitor` role by default, but other users can be granted EXECUTE to run the function.
`pg_ls_waldir()` → `setof record(name text, size bigint, modification timestamp with time zone)`	Returns the name, size, and last modification time of each ordinary file in the server's WAL directory. Excludes dot-prefixed filenames, directories, and special files. Restricted to superusers and members of the `pg_monitor` role by default, but other users can be granted EXECUTE to run the function.
`pg_ls_archive_statusdir()` → `setof record(name text, size bigint, modification timestamp with time zone)`	Returns the name, size, and last modification time of each ordinary file in the WAL archive status directory (`pg_wal/archive_status`). Excludes dot-prefixed filenames, directories, and special files. Restricted to superusers and members of the `pg_monitor` role by default, but other users can be granted EXECUTE to run the function.
`pg_ls_tmpdir([tablespace oid])` → `setof record(name text, size bigint, modification timestamp with time zone)`	Returns the name, size, and last modification time of each ordinary file in the temporary file directory for the given tablespace. Defaults to the `pg_default` tablespace if no tablespace is specified. Excludes dot-prefixed filenames, directories, and special files. Restricted to superusers and members of the `pg_monitor` role by default, but other users can be granted EXECUTE to run the function.
`pg_read_file(filename text [, offset bigint, length bigint [, missing_ok boolean]])` → `text`	Returns all or part of a text file. A negative `offset` is relative to the end of the file. Omit `offset` and `length` to return the entire file. Bytes are interpreted in the database's encoding; an error is raised if they are not valid in that encoding. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
`pg_read_binary_file(filename text [, offset bigint, length bigint [, missing_ok boolean]])` → `bytea`	Same as `pg_read_file`, but reads arbitrary binary data and returns `bytea` with no encoding checks. To read a text file in a specific encoding and convert to the database's encoding, combine with `convert_from`: `SELECT convert_from(pg_read_binary_file('file_in_utf8.txt'), 'UTF8');` Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
`pg_stat_file(filename text [, missing_ok boolean])` → `record(size bigint, access timestamp with time zone, modification timestamp with time zone, change timestamp with time zone, creation timestamp with time zone, isdir boolean)`	Returns a record with the file's size, last access timestamp, last modification timestamp, last status change timestamp (Unix only), creation timestamp (Windows only), and a flag indicating whether it is a directory. Restricted to superusers by default, but other users can be granted EXECUTE to run the function.

Advisory lock functions

Advisory locks let applications define their own locking semantics on top of the database. Each lock is identified by either a single 64-bit key or two 32-bit keys — these two key spaces do not overlap.

Shared vs. exclusive: A shared lock does not conflict with other shared locks on the same resource, only with exclusive locks.
Session-level vs. transaction-level: Session-level locks are held until explicitly released or the session ends. Transaction-level locks are released automatically when the current transaction ends. Multiple session-level lock requests on the same resource stack — lock it three times and you must unlock it three times before the session ends.
Blocking vs. non-blocking: Functions prefixed with pg_try_ return false immediately if the lock cannot be acquired, instead of waiting.

If another session holds a conflicting lock, blocking functions wait until the resource becomes available.

Function	Description
`pg_advisory_lock(key bigint)` → `void` / `pg_advisory_lock(key1 integer, key2 integer)` → `void`	Acquires an exclusive session-level advisory lock, waiting if necessary.
`pg_advisory_lock_shared(key bigint)` → `void` / `pg_advisory_lock_shared(key1 integer, key2 integer)` → `void`	Acquires a shared session-level advisory lock, waiting if necessary.
`pg_advisory_unlock(key bigint)` → `boolean` / `pg_advisory_unlock(key1 integer, key2 integer)` → `boolean`	Releases an exclusive session-level advisory lock. Returns `true` if released, `false` if the lock was not held (plus an SQL warning).
`pg_advisory_unlock_all()` → `void`	Releases all session-level advisory locks held by the current session. Called implicitly at session end, even if the client disconnects ungracefully.
`pg_advisory_unlock_shared(key bigint)` → `boolean` / `pg_advisory_unlock_shared(key1 integer, key2 integer)` → `boolean`	Releases a shared session-level advisory lock. Returns `true` if released, `false` if the lock was not held (plus an SQL warning).
`pg_advisory_xact_lock(key bigint)` → `void` / `pg_advisory_xact_lock(key1 integer, key2 integer)` → `void`	Acquires an exclusive transaction-level advisory lock, waiting if necessary.
`pg_advisory_xact_lock_shared(key bigint)` → `void` / `pg_advisory_xact_lock_shared(key1 integer, key2 integer)` → `void`	Acquires a shared transaction-level advisory lock, waiting if necessary.
`pg_try_advisory_lock(key bigint)` → `boolean` / `pg_try_advisory_lock(key1 integer, key2 integer)` → `boolean`	Acquires an exclusive session-level advisory lock if immediately available. Returns `true` if acquired, `false` otherwise (no waiting).
`pg_try_advisory_lock_shared(key bigint)` → `boolean` / `pg_try_advisory_lock_shared(key1 integer, key2 integer)` → `boolean`	Acquires a shared session-level advisory lock if immediately available. Returns `true` if acquired, `false` otherwise (no waiting).
`pg_try_advisory_xact_lock(key bigint)` → `boolean` / `pg_try_advisory_xact_lock(key1 integer, key2 integer)` → `boolean`	Acquires an exclusive transaction-level advisory lock if immediately available. Returns `true` if acquired, `false` otherwise (no waiting).
`pg_try_advisory_xact_lock_shared(key bigint)` → `boolean` / `pg_try_advisory_xact_lock_shared(key1 integer, key2 integer)` → `boolean`	Acquires a shared transaction-level advisory lock if immediately available. Returns `true` if acquired, `false` otherwise (no waiting).