diff options
Diffstat (limited to 'doc/src/sgml/html/functions-admin.html')
| -rw-r--r-- | doc/src/sgml/html/functions-admin.html | 1527 |
1 files changed, 1527 insertions, 0 deletions
diff --git a/doc/src/sgml/html/functions-admin.html b/doc/src/sgml/html/functions-admin.html new file mode 100644 index 0000000..886df3a --- /dev/null +++ b/doc/src/sgml/html/functions-admin.html @@ -0,0 +1,1527 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.27. System Administration Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="functions-info.html" title="9.26. System Information Functions and Operators" /><link rel="next" href="functions-trigger.html" title="9.28. Trigger Functions" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.27. System Administration Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-info.html" title="9.26. System Information Functions and Operators">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-trigger.html" title="9.28. Trigger Functions">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-ADMIN"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.27. System Administration Functions</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-SET">9.27.1. Configuration Settings Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-SIGNAL">9.27.2. Server Signaling Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-BACKUP">9.27.3. Backup Control Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-RECOVERY-CONTROL">9.27.4. Recovery Control Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION">9.27.5. Snapshot Synchronization Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-REPLICATION">9.27.6. Replication Management Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT">9.27.7. Database Object Management Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-INDEX">9.27.8. Index Maintenance Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-GENFILE">9.27.9. Generic File Access Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADVISORY-LOCKS">9.27.10. Advisory Lock Functions</a></span></dt></dl></div><p> + The functions described in this section are used to control and + monitor a <span class="productname">PostgreSQL</span> installation. + </p><div class="sect2" id="FUNCTIONS-ADMIN-SET"><div class="titlepage"><div><div><h3 class="title">9.27.1. Configuration Settings Functions</h3></div></div></div><a id="id-1.5.8.33.3.2" class="indexterm"></a><a id="id-1.5.8.33.3.3" class="indexterm"></a><a id="id-1.5.8.33.3.4" class="indexterm"></a><p> + <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE" title="Table 9.83. Configuration Settings Functions">Table 9.83</a> shows the functions + available to query and alter run-time configuration parameters. + </p><div class="table" id="FUNCTIONS-ADMIN-SET-TABLE"><p class="title"><strong>Table 9.83. Configuration Settings Functions</strong></p><div class="table-contents"><table class="table" summary="Configuration Settings Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p> + <p> + Example(s) + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.3.6.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">current_setting</code> ( <em class="parameter"><code>setting_name</code></em> <code class="type">text</code> [<span class="optional">, <em class="parameter"><code>missing_ok</code></em> <code class="type">boolean</code> </span>] ) + → <code class="returnvalue">text</code> + </p> + <p> + Returns the current value of the + setting <em class="parameter"><code>setting_name</code></em>. If there is no such + setting, <code class="function">current_setting</code> throws an error + unless <em class="parameter"><code>missing_ok</code></em> is supplied and + is <code class="literal">true</code> (in which case NULL is returned). + This function corresponds to + the <acronym class="acronym">SQL</acronym> command <a class="xref" href="sql-show.html" title="SHOW"><span class="refentrytitle">SHOW</span></a>. + </p> + <p> + <code class="literal">current_setting('datestyle')</code> + → <code class="returnvalue">ISO, MDY</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.3.6.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">set_config</code> ( + <em class="parameter"><code>setting_name</code></em> <code class="type">text</code>, + <em class="parameter"><code>new_value</code></em> <code class="type">text</code>, + <em class="parameter"><code>is_local</code></em> <code class="type">boolean</code> ) + → <code class="returnvalue">text</code> + </p> + <p> + Sets the parameter <em class="parameter"><code>setting_name</code></em> + to <em class="parameter"><code>new_value</code></em>, and returns that value. + If <em class="parameter"><code>is_local</code></em> is <code class="literal">true</code>, 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 <code class="literal">false</code> instead. This function corresponds to + the SQL command <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></a>. + </p> + <p> + <code class="literal">set_config('log_statement_stats', 'off', false)</code> + → <code class="returnvalue">off</code> + </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-ADMIN-SIGNAL"><div class="titlepage"><div><div><h3 class="title">9.27.2. Server Signaling Functions</h3></div></div></div><a id="id-1.5.8.33.4.2" class="indexterm"></a><p> + The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE" title="Table 9.84. Server Signaling Functions">Table 9.84</a> 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 + <code class="command">GRANT</code>, with noted exceptions. + </p><p> + Each of these functions returns <code class="literal">true</code> if + successful and <code class="literal">false</code> otherwise. + </p><div class="table" id="FUNCTIONS-ADMIN-SIGNAL-TABLE"><p class="title"><strong>Table 9.84. Server Signaling Functions</strong></p><div class="table-contents"><table class="table" summary="Server Signaling Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.4.5.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">pg_cancel_backend</code> ( <em class="parameter"><code>pid</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p> + 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 <code class="literal">pg_signal_backend</code>, + however only superusers can cancel superuser backends. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.4.5.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">pg_reload_conf</code> () + → <code class="returnvalue">boolean</code> + </p> + <p> + Causes all processes of the <span class="productname">PostgreSQL</span> + server to reload their configuration files. (This is initiated by + sending a <span class="systemitem">SIGHUP</span> signal to the postmaster + process, which in turn sends <span class="systemitem">SIGHUP</span> to each + of its children.) + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.4.5.2.2.3.1.1.1" class="indexterm"></a> + <code class="function">pg_rotate_logfile</code> () + → <code class="returnvalue">boolean</code> + </p> + <p> + Signals the log-file manager to switch to a new output file + immediately. This works only when the built-in log collector is + running, since otherwise there is no log-file manager subprocess. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.4.5.2.2.4.1.1.1" class="indexterm"></a> + <code class="function">pg_terminate_backend</code> ( <em class="parameter"><code>pid</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p> + 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 <code class="literal">pg_signal_backend</code>, + however only superusers can terminate superuser backends. + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + <code class="function">pg_cancel_backend</code> and <code class="function">pg_terminate_backend</code> + send signals (<span class="systemitem">SIGINT</span> or <span class="systemitem">SIGTERM</span> + respectively) to backend processes identified by process ID. + The process ID of an active backend can be found from + the <code class="structfield">pid</code> column of the + <code class="structname">pg_stat_activity</code> view, or by listing the + <code class="command">postgres</code> processes on the server (using + <span class="application">ps</span> on Unix or the <span class="application">Task + Manager</span> on <span class="productname">Windows</span>). + The role of an active backend can be found from the + <code class="structfield">usename</code> column of the + <code class="structname">pg_stat_activity</code> view. + </p></div><div class="sect2" id="FUNCTIONS-ADMIN-BACKUP"><div class="titlepage"><div><div><h3 class="title">9.27.3. Backup Control Functions</h3></div></div></div><a id="id-1.5.8.33.5.2" class="indexterm"></a><p> + The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE" title="Table 9.85. Backup Control Functions">Table 9.85</a> assist in making on-line backups. + These functions cannot be executed during recovery (except + non-exclusive <code class="function">pg_start_backup</code>, + non-exclusive <code class="function">pg_stop_backup</code>, + <code class="function">pg_is_in_backup</code>, <code class="function">pg_backup_start_time</code> + and <code class="function">pg_wal_lsn_diff</code>). + </p><p> + For details about proper usage of these functions, see + <a class="xref" href="continuous-archiving.html" title="25.3. Continuous Archiving and Point-in-Time Recovery (PITR)">Section 25.3</a>. + </p><div class="table" id="FUNCTIONS-ADMIN-BACKUP-TABLE"><p class="title"><strong>Table 9.85. Backup Control Functions</strong></p><div class="table-contents"><table class="table" summary="Backup Control Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.5.5.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">pg_create_restore_point</code> ( <em class="parameter"><code>name</code></em> <code class="type">text</code> ) + → <code class="returnvalue">pg_lsn</code> + </p> + <p> + Creates a named marker record in the write-ahead log that can later be + used as a recovery target, and returns the corresponding write-ahead + log location. The given name can then be used with + <a class="xref" href="runtime-config-wal.html#GUC-RECOVERY-TARGET-NAME">recovery_target_name</a> 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. + </p> + <p> + This function is restricted to superusers by default, but other users + can be granted EXECUTE to run the function. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.5.5.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">pg_current_wal_flush_lsn</code> () + → <code class="returnvalue">pg_lsn</code> + </p> + <p> + Returns the current write-ahead log flush location (see notes below). + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.5.5.2.2.3.1.1.1" class="indexterm"></a> + <code class="function">pg_current_wal_insert_lsn</code> () + → <code class="returnvalue">pg_lsn</code> + </p> + <p> + Returns the current write-ahead log insert location (see notes below). + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.5.5.2.2.4.1.1.1" class="indexterm"></a> + <code class="function">pg_current_wal_lsn</code> () + → <code class="returnvalue">pg_lsn</code> + </p> + <p> + Returns the current write-ahead log write location (see notes below). + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.5.5.2.2.5.1.1.1" class="indexterm"></a> + <code class="function">pg_start_backup</code> ( + <em class="parameter"><code>label</code></em> <code class="type">text</code> + [<span class="optional">, <em class="parameter"><code>fast</code></em> <code class="type">boolean</code> + [<span class="optional">, <em class="parameter"><code>exclusive</code></em> <code class="type">boolean</code> + </span>]</span>] ) + → <code class="returnvalue">pg_lsn</code> + </p> + <p> + Prepares the server to begin an on-line backup. The only required + parameter is an arbitrary user-defined label for the backup. + (Typically this would be the name under which the backup dump file + will be stored.) + If the optional second parameter is given as <code class="literal">true</code>, + it specifies executing <code class="function">pg_start_backup</code> as quickly + as possible. This forces an immediate checkpoint which will cause a + spike in I/O operations, slowing any concurrently executing queries. + The optional third parameter specifies whether to perform an exclusive + or non-exclusive backup (default is exclusive). + </p> + <p> + When used in exclusive mode, this function writes a backup label file + (<code class="filename">backup_label</code>) and, if there are any links in + the <code class="filename">pg_tblspc/</code> directory, a tablespace map file + (<code class="filename">tablespace_map</code>) into the database cluster's data + directory, then performs a checkpoint, and then returns the backup's + starting write-ahead log location. (The user can ignore this + result value, but it is provided in case it is useful.) When used in + non-exclusive mode, the contents of these files are instead returned + by the <code class="function">pg_stop_backup</code> function, and should be + copied to the backup area by the user. + </p> + <p> + This function is restricted to superusers by default, but other users + can be granted EXECUTE to run the function. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.5.5.2.2.6.1.1.1" class="indexterm"></a> + <code class="function">pg_stop_backup</code> ( + <em class="parameter"><code>exclusive</code></em> <code class="type">boolean</code> + [<span class="optional">, <em class="parameter"><code>wait_for_archive</code></em> <code class="type">boolean</code> + </span>] ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code>, + <em class="parameter"><code>labelfile</code></em> <code class="type">text</code>, + <em class="parameter"><code>spcmapfile</code></em> <code class="type">text</code> ) + </p> + <p> + Finishes performing an exclusive or non-exclusive on-line backup. + The <em class="parameter"><code>exclusive</code></em> parameter must match the + previous <code class="function">pg_start_backup</code> call. + In an exclusive backup, <code class="function">pg_stop_backup</code> removes + the backup label file and, if it exists, the tablespace map file + created by <code class="function">pg_start_backup</code>. In a non-exclusive + backup, the desired contents of these files are returned as part of + the result of the function, and should be written to files in the + backup area (not in the data directory). + </p> + <p> + There is an optional second parameter of type <code class="type">boolean</code>. + If false, the function will return immediately after the backup is + completed, without waiting for WAL to be archived. This behavior is + only useful with backup software that independently monitors WAL + archiving. Otherwise, WAL required to make the backup consistent might + be missing and make the backup useless. By default or when this + parameter is true, <code class="function">pg_stop_backup</code> will wait for + WAL to be archived when archiving is enabled. (On a standby, this + means that it will wait only when <code class="varname">archive_mode</code> = + <code class="literal">always</code>. If write activity on the primary is low, + it may be useful to run <code class="function">pg_switch_wal</code> on the + primary in order to trigger an immediate segment switch.) + </p> + <p> + When executed on a primary, this function also creates a backup + history file in the write-ahead log archive area. The history file + includes the label given to <code class="function">pg_start_backup</code>, the + starting and ending write-ahead log locations for the backup, and the + starting and ending times of the backup. After recording the ending + location, the current write-ahead log insertion point is automatically + advanced to the next write-ahead log file, so that the ending + write-ahead log file can be archived immediately to complete the + backup. + </p> + <p> + The result of the function is a single record. + The <em class="parameter"><code>lsn</code></em> column holds the backup's ending + write-ahead log location (which again can be ignored). The second and + third columns are <code class="literal">NULL</code> when ending an exclusive + backup; after a non-exclusive backup they hold the desired contents of + the label and tablespace map files. + </p> + <p> + This function is restricted to superusers by default, but other users + can be granted EXECUTE to run the function. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">pg_stop_backup</code> () + → <code class="returnvalue">pg_lsn</code> + </p> + <p> + Finishes performing an exclusive on-line backup. This simplified + version is equivalent to <code class="literal">pg_stop_backup(true, + true)</code>, except that it only returns the <code class="type">pg_lsn</code> + result. + </p> + <p> + This function is restricted to superusers by default, but other users + can be granted EXECUTE to run the function. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.5.5.2.2.8.1.1.1" class="indexterm"></a> + <code class="function">pg_is_in_backup</code> () + → <code class="returnvalue">boolean</code> + </p> + <p> + Returns true if an on-line exclusive backup is in progress. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.5.5.2.2.9.1.1.1" class="indexterm"></a> + <code class="function">pg_backup_start_time</code> () + → <code class="returnvalue">timestamp with time zone</code> + </p> + <p> + Returns the start time of the current on-line exclusive backup if one + is in progress, otherwise <code class="literal">NULL</code>. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.5.5.2.2.10.1.1.1" class="indexterm"></a> + <code class="function">pg_switch_wal</code> () + → <code class="returnvalue">pg_lsn</code> + </p> + <p> + Forces the server to switch to a new write-ahead log file, which + allows the current file to be archived (assuming you are using + continuous archiving). The result is the ending write-ahead log + location plus 1 within the just-completed write-ahead log file. If + there has been no write-ahead log activity since the last write-ahead + log switch, <code class="function">pg_switch_wal</code> does nothing and + returns the start location of the write-ahead log file currently in + use. + </p> + <p> + This function is restricted to superusers by default, but other users + can be granted EXECUTE to run the function. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.5.5.2.2.11.1.1.1" class="indexterm"></a> + <code class="function">pg_walfile_name</code> ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> ) + → <code class="returnvalue">text</code> + </p> + <p> + Converts a write-ahead log location to the name of the WAL file + holding that location. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.5.5.2.2.12.1.1.1" class="indexterm"></a> + <code class="function">pg_walfile_name_offset</code> ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> ) + → <code class="returnvalue">record</code> + ( <em class="parameter"><code>file_name</code></em> <code class="type">text</code>, + <em class="parameter"><code>file_offset</code></em> <code class="type">integer</code> ) + </p> + <p> + Converts a write-ahead log location to a WAL file name and byte offset + within that file. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.5.5.2.2.13.1.1.1" class="indexterm"></a> + <code class="function">pg_wal_lsn_diff</code> ( <em class="parameter"><code>lsn1</code></em> <code class="type">pg_lsn</code>, <em class="parameter"><code>lsn2</code></em> <code class="type">pg_lsn</code> ) + → <code class="returnvalue">numeric</code> + </p> + <p> + Calculates the difference in bytes (<em class="parameter"><code>lsn1</code></em> - <em class="parameter"><code>lsn2</code></em>) between two write-ahead log + locations. This can be used + with <code class="structname">pg_stat_replication</code> or some of the + functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE" title="Table 9.85. Backup Control Functions">Table 9.85</a> to + get the replication lag. + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + <code class="function">pg_current_wal_lsn</code> displays the current write-ahead + log write location in the same format used by the above functions. + Similarly, <code class="function">pg_current_wal_insert_lsn</code> displays the + current write-ahead log insertion location + and <code class="function">pg_current_wal_flush_lsn</code> displays the current + write-ahead log flush location. The insertion location is + the <span class="quote">“<span class="quote">logical</span>”</span> end of the write-ahead log at any instant, + while the write location is the end of what has actually been written out + from the server's internal buffers, and the flush location is the last + location known to be written to durable storage. The write location is the + end of what can be examined from outside the server, and is usually what + you want if you are interested in archiving partially-complete write-ahead + log files. The insertion and flush locations are made available primarily + for server debugging purposes. These are all read-only operations and do + not require superuser permissions. + </p><p> + You can use <code class="function">pg_walfile_name_offset</code> to extract the + corresponding write-ahead log file name and byte offset from + a <code class="type">pg_lsn</code> value. For example: +</p><pre class="programlisting"> +postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); + file_name | file_offset +--------------------------+------------- + 00000001000000000000000D | 4039624 +(1 row) +</pre><p> + Similarly, <code class="function">pg_walfile_name</code> extracts just the write-ahead log file name. + When the given write-ahead log location is exactly at a write-ahead log file boundary, both + these functions return the name of the preceding write-ahead log file. + This is usually the desired behavior for managing write-ahead log archiving + behavior, since the preceding file is the last one that currently + needs to be archived. + </p></div><div class="sect2" id="FUNCTIONS-RECOVERY-CONTROL"><div class="titlepage"><div><div><h3 class="title">9.27.4. Recovery Control Functions</h3></div></div></div><p> + The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE" title="Table 9.86. Recovery Information Functions">Table 9.86</a> provide information + about the current status of a standby server. + These functions may be executed both during recovery and in normal running. + </p><div class="table" id="FUNCTIONS-RECOVERY-INFO-TABLE"><p class="title"><strong>Table 9.86. Recovery Information Functions</strong></p><div class="table-contents"><table class="table" summary="Recovery Information Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.6.3.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">pg_is_in_recovery</code> () + → <code class="returnvalue">boolean</code> + </p> + <p> + Returns true if recovery is still in progress. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.6.3.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">pg_last_wal_receive_lsn</code> () + → <code class="returnvalue">pg_lsn</code> + </p> + <p> + Returns the last write-ahead log location that has been received and + synced to disk by streaming replication. While streaming replication + is in progress this will increase monotonically. If recovery has + completed then this will remain static at the location 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 <code class="literal">NULL</code>. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.6.3.2.2.3.1.1.1" class="indexterm"></a> + <code class="function">pg_last_wal_replay_lsn</code> () + → <code class="returnvalue">pg_lsn</code> + </p> + <p> + Returns the last write-ahead log location that has been replayed + during recovery. If recovery is still in progress this will increase + monotonically. If recovery has completed then this will remain + static at the location of the last WAL record applied during recovery. + When the server has been started normally without recovery, the + function returns <code class="literal">NULL</code>. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.6.3.2.2.4.1.1.1" class="indexterm"></a> + <code class="function">pg_last_xact_replay_timestamp</code> () + → <code class="returnvalue">timestamp with time zone</code> + </p> + <p> + Returns the time stamp of the 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. If no transactions + have been replayed during recovery, the function + returns <code class="literal">NULL</code>. Otherwise, if recovery is still in + progress this will increase monotonically. If recovery has completed + then this will remain static at the time of the last transaction + applied during recovery. When the server has been started normally + without recovery, the function returns <code class="literal">NULL</code>. + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE" title="Table 9.87. Recovery Control Functions">Table 9.87</a> control the progress of recovery. + These functions may be executed only during recovery. + </p><div class="table" id="FUNCTIONS-RECOVERY-CONTROL-TABLE"><p class="title"><strong>Table 9.87. Recovery Control Functions</strong></p><div class="table-contents"><table class="table" summary="Recovery Control Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.6.5.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">pg_is_wal_replay_paused</code> () + → <code class="returnvalue">boolean</code> + </p> + <p> + Returns true if recovery is paused. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.6.5.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">pg_promote</code> ( <em class="parameter"><code>wait</code></em> <code class="type">boolean</code> <code class="literal">DEFAULT</code> <code class="literal">true</code>, <em class="parameter"><code>wait_seconds</code></em> <code class="type">integer</code> <code class="literal">DEFAULT</code> <code class="literal">60</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p> + Promotes a standby server to primary status. + With <em class="parameter"><code>wait</code></em> set to <code class="literal">true</code> (the + default), the function waits until promotion is completed + or <em class="parameter"><code>wait_seconds</code></em> seconds have passed, and + returns <code class="literal">true</code> if promotion is successful + and <code class="literal">false</code> otherwise. + If <em class="parameter"><code>wait</code></em> is set to <code class="literal">false</code>, the + function returns <code class="literal">true</code> immediately after sending a + <code class="literal">SIGUSR1</code> signal to the postmaster to trigger + promotion. + </p> + <p> + This function is restricted to superusers by default, but other users + can be granted EXECUTE to run the function. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.6.5.2.2.3.1.1.1" class="indexterm"></a> + <code class="function">pg_wal_replay_pause</code> () + → <code class="returnvalue">void</code> + </p> + <p> + Pauses recovery. While recovery is paused, no further database + changes are applied. If hot standby is active, all new queries will + see the same consistent snapshot of the database, and no further query + conflicts will be generated until recovery is resumed. + </p> + <p> + This function is restricted to superusers by default, but other users + can be granted EXECUTE to run the function. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.6.5.2.2.4.1.1.1" class="indexterm"></a> + <code class="function">pg_wal_replay_resume</code> () + → <code class="returnvalue">void</code> + </p> + <p> + Restarts recovery if it was paused. + </p> + <p> + This function is restricted to superusers by default, but other users + can be granted EXECUTE to run the function. + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + <code class="function">pg_wal_replay_pause</code> and + <code class="function">pg_wal_replay_resume</code> cannot be executed while + a promotion is ongoing. If a promotion is triggered while recovery + is paused, the paused state ends and promotion continues. + </p><p> + If streaming replication is disabled, the paused state may continue + indefinitely without a problem. If streaming replication is in + progress then 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. + </p></div><div class="sect2" id="FUNCTIONS-SNAPSHOT-SYNCHRONIZATION"><div class="titlepage"><div><div><h3 class="title">9.27.5. Snapshot Synchronization Functions</h3></div></div></div><p> + <span class="productname">PostgreSQL</span> allows database sessions to synchronize their + snapshots. A <em class="firstterm">snapshot</em> determines which data is visible to the + transaction that is using the snapshot. Synchronized snapshots are + necessary when two or more sessions need to see identical content in the + database. If two sessions just start their transactions independently, + there is always a possibility that some third transaction commits + between the executions of the two <code class="command">START TRANSACTION</code> commands, + so that one session sees the effects of that transaction and the other + does not. + </p><p> + To solve this problem, <span class="productname">PostgreSQL</span> allows a transaction to + <em class="firstterm">export</em> the snapshot it is using. As long as the exporting + transaction remains open, other transactions can <em class="firstterm">import</em> its + snapshot, and thereby be guaranteed that they see exactly the same view + of the database that the first transaction sees. But note that any + database changes made by any one of these transactions remain invisible + to the other transactions, as is usual for changes made by uncommitted + transactions. So the transactions are synchronized with respect to + pre-existing data, but act normally for changes they make themselves. + </p><p> + Snapshots are exported with the <code class="function">pg_export_snapshot</code> function, + shown in <a class="xref" href="functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION-TABLE" title="Table 9.88. Snapshot Synchronization Functions">Table 9.88</a>, and + imported with the <a class="xref" href="sql-set-transaction.html" title="SET TRANSACTION"><span class="refentrytitle">SET TRANSACTION</span></a> command. + </p><div class="table" id="FUNCTIONS-SNAPSHOT-SYNCHRONIZATION-TABLE"><p class="title"><strong>Table 9.88. Snapshot Synchronization Functions</strong></p><div class="table-contents"><table class="table" summary="Snapshot Synchronization Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.7.5.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">pg_export_snapshot</code> () + → <code class="returnvalue">text</code> + </p> + <p> + Saves the transaction's current snapshot and returns + a <code class="type">text</code> string identifying the snapshot. This string must + be passed (outside the database) to clients that want to import the + snapshot. The snapshot is available for import only until the end of + the transaction that exported it. + </p> + <p> + A transaction can export more than one snapshot, if needed. Note that + doing so is only useful in <code class="literal">READ COMMITTED</code> + transactions, since in <code class="literal">REPEATABLE READ</code> and higher + isolation levels, transactions use the same snapshot throughout their + lifetime. Once a transaction has exported any snapshots, it cannot be + prepared with <a class="xref" href="sql-prepare-transaction.html" title="PREPARE TRANSACTION"><span class="refentrytitle">PREPARE TRANSACTION</span></a>. + </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-REPLICATION"><div class="titlepage"><div><div><h3 class="title">9.27.6. Replication Management Functions</h3></div></div></div><p> + The functions shown + in <a class="xref" href="functions-admin.html#FUNCTIONS-REPLICATION-TABLE" title="Table 9.89. Replication Management Functions">Table 9.89</a> are for + controlling and interacting with replication features. + See <a class="xref" href="warm-standby.html#STREAMING-REPLICATION" title="26.2.5. Streaming Replication">Section 26.2.5</a>, + <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-SLOTS" title="26.2.6. Replication Slots">Section 26.2.6</a>, and + <a class="xref" href="replication-origins.html" title="Chapter 49. Replication Progress Tracking">Chapter 49</a> + for information about the underlying features. + Use of functions for replication origin is restricted to superusers. + Use of functions for replication slots is restricted to superusers + and users having <code class="literal">REPLICATION</code> privilege. + </p><p> + Many of these functions have equivalent commands in the replication + protocol; see <a class="xref" href="protocol-replication.html" title="52.4. Streaming Replication Protocol">Section 52.4</a>. + </p><p> + The functions described in + <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-BACKUP" title="9.27.3. Backup Control Functions">Section 9.27.3</a>, + <a class="xref" href="functions-admin.html#FUNCTIONS-RECOVERY-CONTROL" title="9.27.4. Recovery Control Functions">Section 9.27.4</a>, and + <a class="xref" href="functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION" title="9.27.5. Snapshot Synchronization Functions">Section 9.27.5</a> + are also relevant for replication. + </p><div class="table" id="FUNCTIONS-REPLICATION-TABLE"><p class="title"><strong>Table 9.89. Replication Management Functions</strong></p><div class="table-contents"><table class="table" summary="Replication Management Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">pg_create_physical_replication_slot</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code> [<span class="optional">, <em class="parameter"><code>immediately_reserve</code></em> <code class="type">boolean</code>, <em class="parameter"><code>temporary</code></em> <code class="type">boolean</code> </span>] ) + → <code class="returnvalue">record</code> + ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, + <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> ) + </p> + <p> + Creates a new physical replication slot named + <em class="parameter"><code>slot_name</code></em>. The optional second parameter, + when <code class="literal">true</code>, specifies that the <acronym class="acronym">LSN</acronym> for this + replication slot be reserved immediately; otherwise + the <acronym class="acronym">LSN</acronym> is reserved on first connection from a streaming + replication client. Streaming changes from a physical slot is only + possible with the streaming-replication protocol — + see <a class="xref" href="protocol-replication.html" title="52.4. Streaming Replication Protocol">Section 52.4</a>. The optional third + parameter, <em class="parameter"><code>temporary</code></em>, when set to true, specifies that + the slot should not be permanently stored to disk and is only meant + for use by the current session. Temporary slots are also + released upon any error. This function corresponds + to the replication protocol command <code class="literal">CREATE_REPLICATION_SLOT + ... PHYSICAL</code>. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">pg_drop_replication_slot</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code> ) + → <code class="returnvalue">void</code> + </p> + <p> + Drops the physical or logical replication slot + named <em class="parameter"><code>slot_name</code></em>. Same as replication protocol + command <code class="literal">DROP_REPLICATION_SLOT</code>. For logical slots, this must + be called while connected to the same database the slot was created on. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.3.1.1.1" class="indexterm"></a> + <code class="function">pg_create_logical_replication_slot</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>plugin</code></em> <code class="type">name</code> [<span class="optional">, <em class="parameter"><code>temporary</code></em> <code class="type">boolean</code> </span>] ) + → <code class="returnvalue">record</code> + ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, + <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> ) + </p> + <p> + Creates a new logical (decoding) replication slot named + <em class="parameter"><code>slot_name</code></em> using the output plugin + <em class="parameter"><code>plugin</code></em>. The optional third + parameter, <em class="parameter"><code>temporary</code></em>, when set to true, specifies that + the slot should not be permanently stored to disk and is only meant + for use by the current session. Temporary slots are also + released upon any error. A call to this function has the same + effect as the replication protocol command + <code class="literal">CREATE_REPLICATION_SLOT ... LOGICAL</code>. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.4.1.1.1" class="indexterm"></a> + <code class="function">pg_copy_physical_replication_slot</code> ( <em class="parameter"><code>src_slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>dst_slot_name</code></em> <code class="type">name</code> [<span class="optional">, <em class="parameter"><code>temporary</code></em> <code class="type">boolean</code> </span>] ) + → <code class="returnvalue">record</code> + ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, + <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> ) + </p> + <p> + Copies an existing physical replication slot named <em class="parameter"><code>src_slot_name</code></em> + to a physical replication slot named <em class="parameter"><code>dst_slot_name</code></em>. + The copied physical slot starts to reserve WAL from the same <acronym class="acronym">LSN</acronym> as the + source slot. + <em class="parameter"><code>temporary</code></em> is optional. If <em class="parameter"><code>temporary</code></em> + is omitted, the same value as the source slot is used. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.5.1.1.1" class="indexterm"></a> + <code class="function">pg_copy_logical_replication_slot</code> ( <em class="parameter"><code>src_slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>dst_slot_name</code></em> <code class="type">name</code> [<span class="optional">, <em class="parameter"><code>temporary</code></em> <code class="type">boolean</code> [<span class="optional">, <em class="parameter"><code>plugin</code></em> <code class="type">name</code> </span>]</span>] ) + → <code class="returnvalue">record</code> + ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, + <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> ) + </p> + <p> + Copies an existing logical replication slot + named <em class="parameter"><code>src_slot_name</code></em> to a logical replication + slot named <em class="parameter"><code>dst_slot_name</code></em>, optionally changing + the output plugin and persistence. The copied logical slot starts + from the same <acronym class="acronym">LSN</acronym> as the source logical slot. Both + <em class="parameter"><code>temporary</code></em> and <em class="parameter"><code>plugin</code></em> are + optional; if they are omitted, the values of the source slot are used. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.6.1.1.1" class="indexterm"></a> + <code class="function">pg_logical_slot_get_changes</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>upto_lsn</code></em> <code class="type">pg_lsn</code>, <em class="parameter"><code>upto_nchanges</code></em> <code class="type">integer</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>options</code></em> <code class="type">text[]</code> ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code>, + <em class="parameter"><code>xid</code></em> <code class="type">xid</code>, + <em class="parameter"><code>data</code></em> <code class="type">text</code> ) + </p> + <p> + Returns changes in the slot <em class="parameter"><code>slot_name</code></em>, starting + from the point from which changes have been consumed last. If + <em class="parameter"><code>upto_lsn</code></em> + and <em class="parameter"><code>upto_nchanges</code></em> are NULL, + logical decoding will continue until end of WAL. If + <em class="parameter"><code>upto_lsn</code></em> is non-NULL, decoding will include only + those transactions which commit prior to the specified LSN. If + <em class="parameter"><code>upto_nchanges</code></em> is non-NULL, decoding will + stop when the number of rows produced by decoding exceeds + the specified value. Note, however, that the actual number of + rows returned may be larger, since this limit is only checked after + adding the rows produced when decoding each new transaction commit. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.7.1.1.1" class="indexterm"></a> + <code class="function">pg_logical_slot_peek_changes</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>upto_lsn</code></em> <code class="type">pg_lsn</code>, <em class="parameter"><code>upto_nchanges</code></em> <code class="type">integer</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>options</code></em> <code class="type">text[]</code> ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code>, + <em class="parameter"><code>xid</code></em> <code class="type">xid</code>, + <em class="parameter"><code>data</code></em> <code class="type">text</code> ) + </p> + <p> + Behaves just like + the <code class="function">pg_logical_slot_get_changes()</code> function, + except that changes are not consumed; that is, they will be returned + again on future calls. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.8.1.1.1" class="indexterm"></a> + <code class="function">pg_logical_slot_get_binary_changes</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>upto_lsn</code></em> <code class="type">pg_lsn</code>, <em class="parameter"><code>upto_nchanges</code></em> <code class="type">integer</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>options</code></em> <code class="type">text[]</code> ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code>, + <em class="parameter"><code>xid</code></em> <code class="type">xid</code>, + <em class="parameter"><code>data</code></em> <code class="type">bytea</code> ) + </p> + <p> + Behaves just like + the <code class="function">pg_logical_slot_get_changes()</code> function, + except that changes are returned as <code class="type">bytea</code>. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.9.1.1.1" class="indexterm"></a> + <code class="function">pg_logical_slot_peek_binary_changes</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>upto_lsn</code></em> <code class="type">pg_lsn</code>, <em class="parameter"><code>upto_nchanges</code></em> <code class="type">integer</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>options</code></em> <code class="type">text[]</code> ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code>, + <em class="parameter"><code>xid</code></em> <code class="type">xid</code>, + <em class="parameter"><code>data</code></em> <code class="type">bytea</code> ) + </p> + <p> + Behaves just like + the <code class="function">pg_logical_slot_peek_changes()</code> function, + except that changes are returned as <code class="type">bytea</code>. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.10.1.1.1" class="indexterm"></a> + <code class="function">pg_replication_slot_advance</code> ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, <em class="parameter"><code>upto_lsn</code></em> <code class="type">pg_lsn</code> ) + → <code class="returnvalue">record</code> + ( <em class="parameter"><code>slot_name</code></em> <code class="type">name</code>, + <em class="parameter"><code>end_lsn</code></em> <code class="type">pg_lsn</code> ) + </p> + <p> + Advances the current confirmed position of a replication slot named + <em class="parameter"><code>slot_name</code></em>. The slot will not be moved backwards, + and it will not be moved beyond the current insert location. Returns + the name of the slot and the actual position that it was advanced to. + The updated slot position information is written out at the next + checkpoint if any advancing is done. So in the event of a crash, the + slot may return to an earlier position. + </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-CREATE" class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.11.1.1.1" class="indexterm"></a> + <code class="function">pg_replication_origin_create</code> ( <em class="parameter"><code>node_name</code></em> <code class="type">text</code> ) + → <code class="returnvalue">oid</code> + </p> + <p> + Creates a replication origin with the given external + name, and returns the internal ID assigned to it. + </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-DROP" class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.12.1.1.1" class="indexterm"></a> + <code class="function">pg_replication_origin_drop</code> ( <em class="parameter"><code>node_name</code></em> <code class="type">text</code> ) + → <code class="returnvalue">void</code> + </p> + <p> + Deletes a previously-created replication origin, including any + associated replay progress. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.13.1.1.1" class="indexterm"></a> + <code class="function">pg_replication_origin_oid</code> ( <em class="parameter"><code>node_name</code></em> <code class="type">text</code> ) + → <code class="returnvalue">oid</code> + </p> + <p> + Looks up a replication origin by name and returns the internal ID. If + no such replication origin is found an error is thrown. + </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-SESSION-SETUP" class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.14.1.1.1" class="indexterm"></a> + <code class="function">pg_replication_origin_session_setup</code> ( <em class="parameter"><code>node_name</code></em> <code class="type">text</code> ) + → <code class="returnvalue">void</code> + </p> + <p> + Marks the current session as replaying from the given + origin, allowing replay progress to be tracked. + Can only be used if no origin is currently selected. + Use <code class="function">pg_replication_origin_session_reset</code> to undo. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.15.1.1.1" class="indexterm"></a> + <code class="function">pg_replication_origin_session_reset</code> () + → <code class="returnvalue">void</code> + </p> + <p> + Cancels the effects + of <code class="function">pg_replication_origin_session_setup()</code>. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.16.1.1.1" class="indexterm"></a> + <code class="function">pg_replication_origin_session_is_setup</code> () + → <code class="returnvalue">boolean</code> + </p> + <p> + Returns true if a replication origin has been selected in the + current session. + </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-SESSION-PROGRESS" class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.17.1.1.1" class="indexterm"></a> + <code class="function">pg_replication_origin_session_progress</code> ( <em class="parameter"><code>flush</code></em> <code class="type">boolean</code> ) + → <code class="returnvalue">pg_lsn</code> + </p> + <p> + Returns the replay location for the replication origin selected in + the current session. The parameter <em class="parameter"><code>flush</code></em> + determines whether the corresponding local transaction will be + guaranteed to have been flushed to disk or not. + </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-XACT-SETUP" class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.18.1.1.1" class="indexterm"></a> + <code class="function">pg_replication_origin_xact_setup</code> ( <em class="parameter"><code>origin_lsn</code></em> <code class="type">pg_lsn</code>, <em class="parameter"><code>origin_timestamp</code></em> <code class="type">timestamp with time zone</code> ) + → <code class="returnvalue">void</code> + </p> + <p> + Marks the current transaction as replaying a transaction that has + committed at the given <acronym class="acronym">LSN</acronym> and timestamp. Can + only be called when a replication origin has been selected + using <code class="function">pg_replication_origin_session_setup</code>. + </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-XACT-RESET" class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.19.1.1.1" class="indexterm"></a> + <code class="function">pg_replication_origin_xact_reset</code> () + → <code class="returnvalue">void</code> + </p> + <p> + Cancels the effects of + <code class="function">pg_replication_origin_xact_setup()</code>. + </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-ADVANCE" class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.20.1.1.1" class="indexterm"></a> + <code class="function">pg_replication_origin_advance</code> ( <em class="parameter"><code>node_name</code></em> <code class="type">text</code>, <em class="parameter"><code>lsn</code></em> <code class="type">pg_lsn</code> ) + → <code class="returnvalue">void</code> + </p> + <p> + Sets replication progress for the given node to the given + location. This is primarily useful for setting up the initial + location, or setting a new location after configuration changes and + similar. Be aware that careless use of this function can lead to + inconsistently replicated data. + </p></td></tr><tr><td id="PG-REPLICATION-ORIGIN-PROGRESS" class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.21.1.1.1" class="indexterm"></a> + <code class="function">pg_replication_origin_progress</code> ( <em class="parameter"><code>node_name</code></em> <code class="type">text</code>, <em class="parameter"><code>flush</code></em> <code class="type">boolean</code> ) + → <code class="returnvalue">pg_lsn</code> + </p> + <p> + Returns the replay location for the given replication origin. The + parameter <em class="parameter"><code>flush</code></em> determines whether the + corresponding local transaction will be guaranteed to have been + flushed to disk or not. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.8.5.2.2.22.1.1.1" class="indexterm"></a> + <code class="function">pg_logical_emit_message</code> ( <em class="parameter"><code>transactional</code></em> <code class="type">boolean</code>, <em class="parameter"><code>prefix</code></em> <code class="type">text</code>, <em class="parameter"><code>content</code></em> <code class="type">text</code> ) + → <code class="returnvalue">pg_lsn</code> + </p> + <p class="func_signature"> + <code class="function">pg_logical_emit_message</code> ( <em class="parameter"><code>transactional</code></em> <code class="type">boolean</code>, <em class="parameter"><code>prefix</code></em> <code class="type">text</code>, <em class="parameter"><code>content</code></em> <code class="type">bytea</code> ) + → <code class="returnvalue">pg_lsn</code> + </p> + <p> + Emits a logical decoding message. This can be used to pass generic + messages to logical decoding plugins through + WAL. The <em class="parameter"><code>transactional</code></em> parameter specifies if + the message should be part of the current transaction, or if it should + be written immediately and decoded as soon as the logical decoder + reads the record. The <em class="parameter"><code>prefix</code></em> parameter is a + textual prefix that can be used by logical decoding plugins to easily + recognize messages that are interesting for them. + The <em class="parameter"><code>content</code></em> parameter is the content of the + message, given either in text or binary form. + </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-ADMIN-DBOBJECT"><div class="titlepage"><div><div><h3 class="title">9.27.7. Database Object Management Functions</h3></div></div></div><p> + The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-DBSIZE" title="Table 9.90. Database Object Size Functions">Table 9.90</a> calculate + the disk space usage of database objects, or assist in presentation + of usage results. + All these functions return sizes measured in bytes. If an OID that does + not represent an existing object is passed to one of these + functions, <code class="literal">NULL</code> is returned. + </p><div class="table" id="FUNCTIONS-ADMIN-DBSIZE"><p class="title"><strong>Table 9.90. Database Object Size Functions</strong></p><div class="table-contents"><table class="table" summary="Database Object Size Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.3.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">pg_column_size</code> ( <code class="type">"any"</code> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Shows the number of bytes used to store any individual data value. If + applied directly to a table column value, this reflects any + compression that was done. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.3.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">pg_database_size</code> ( <code class="type">name</code> ) + → <code class="returnvalue">bigint</code> + </p> + <p class="func_signature"> + <code class="function">pg_database_size</code> ( <code class="type">oid</code> ) + → <code class="returnvalue">bigint</code> + </p> + <p> + Computes the total disk space used by the database with the specified + name or OID. To use this function, you must + have <code class="literal">CONNECT</code> privilege on the specified database + (which is granted by default) or be a member of + the <code class="literal">pg_read_all_stats</code> role. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.3.2.2.3.1.1.1" class="indexterm"></a> + <code class="function">pg_indexes_size</code> ( <code class="type">regclass</code> ) + → <code class="returnvalue">bigint</code> + </p> + <p> + Computes the total disk space used by indexes attached to the + specified table. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.3.2.2.4.1.1.1" class="indexterm"></a> + <code class="function">pg_relation_size</code> ( <em class="parameter"><code>relation</code></em> <code class="type">regclass</code> [<span class="optional">, <em class="parameter"><code>fork</code></em> <code class="type">text</code> </span>] ) + → <code class="returnvalue">bigint</code> + </p> + <p> + Computes the disk space used by one <span class="quote">“<span class="quote">fork</span>”</span> of the + specified relation. (Note that for most purposes it is more + convenient to use the higher-level + functions <code class="function">pg_total_relation_size</code> + or <code class="function">pg_table_size</code>, which sum the sizes of all + forks.) With one argument, this returns the size of the main data + fork of the relation. The second argument can be provided to specify + which fork to examine: + </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem"><p> + <code class="literal">main</code> returns the size of the main + data fork of the relation. + </p></li><li class="listitem"><p> + <code class="literal">fsm</code> returns the size of the Free Space Map + (see <a class="xref" href="storage-fsm.html" title="69.3. Free Space Map">Section 69.3</a>) associated with the relation. + </p></li><li class="listitem"><p> + <code class="literal">vm</code> returns the size of the Visibility Map + (see <a class="xref" href="storage-vm.html" title="69.4. Visibility Map">Section 69.4</a>) associated with the relation. + </p></li><li class="listitem"><p> + <code class="literal">init</code> returns the size of the initialization + fork, if any, associated with the relation. + </p></li></ul></div><p> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.3.2.2.5.1.1.1" class="indexterm"></a> + <code class="function">pg_size_bytes</code> ( <code class="type">text</code> ) + → <code class="returnvalue">bigint</code> + </p> + <p> + Converts a size in human-readable format (as returned + by <code class="function">pg_size_pretty</code>) into bytes. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.3.2.2.6.1.1.1" class="indexterm"></a> + <code class="function">pg_size_pretty</code> ( <code class="type">bigint</code> ) + → <code class="returnvalue">text</code> + </p> + <p class="func_signature"> + <code class="function">pg_size_pretty</code> ( <code class="type">numeric</code> ) + → <code class="returnvalue">text</code> + </p> + <p> + Converts a size in bytes into a more easily human-readable format with + size units (bytes, kB, MB, GB or TB as appropriate). Note that the + units are powers of 2 rather than powers of 10, so 1kB is 1024 bytes, + 1MB is 1024<sup>2</sup> = 1048576 bytes, and so on. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.3.2.2.7.1.1.1" class="indexterm"></a> + <code class="function">pg_table_size</code> ( <code class="type">regclass</code> ) + → <code class="returnvalue">bigint</code> + </p> + <p> + Computes the disk space used by the specified table, excluding indexes + (but including its TOAST table if any, free space map, and visibility + map). + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.3.2.2.8.1.1.1" class="indexterm"></a> + <code class="function">pg_tablespace_size</code> ( <code class="type">name</code> ) + → <code class="returnvalue">bigint</code> + </p> + <p class="func_signature"> + <code class="function">pg_tablespace_size</code> ( <code class="type">oid</code> ) + → <code class="returnvalue">bigint</code> + </p> + <p> + Computes the total disk space used in the tablespace with the + specified name or OID. To use this function, you must + have <code class="literal">CREATE</code> privilege on the specified tablespace + or be a member of the <code class="literal">pg_read_all_stats</code> role, + unless it is the default tablespace for the current database. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.3.2.2.9.1.1.1" class="indexterm"></a> + <code class="function">pg_total_relation_size</code> ( <code class="type">regclass</code> ) + → <code class="returnvalue">bigint</code> + </p> + <p> + Computes the total disk space used by the specified table, including + all indexes and <acronym class="acronym">TOAST</acronym> data. The result is + equivalent to <code class="function">pg_table_size</code> + <code class="literal">+</code> <code class="function">pg_indexes_size</code>. + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + The functions above that operate on tables or indexes accept a + <code class="type">regclass</code> argument, which is simply the OID of the table or index + in the <code class="structname">pg_class</code> system catalog. You do not have to look up + the OID by hand, however, since the <code class="type">regclass</code> data type's input + converter will do the work for you. Just write the table name enclosed in + single quotes so that it looks like a literal constant. For compatibility + with the handling of ordinary <acronym class="acronym">SQL</acronym> names, the string + will be converted to lower case unless it contains double quotes around + the table name. + </p><p> + The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-DBLOCATION" title="Table 9.91. Database Object Location Functions">Table 9.91</a> assist + in identifying the specific disk files associated with database objects. + </p><div class="table" id="FUNCTIONS-ADMIN-DBLOCATION"><p class="title"><strong>Table 9.91. Database Object Location Functions</strong></p><div class="table-contents"><table class="table" summary="Database Object Location Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.6.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">pg_relation_filenode</code> ( <em class="parameter"><code>relation</code></em> <code class="type">regclass</code> ) + → <code class="returnvalue">oid</code> + </p> + <p> + Returns the <span class="quote">“<span class="quote">filenode</span>”</span> number currently assigned to the + specified relation. The filenode is the base component of the file + name(s) used for the relation (see + <a class="xref" href="storage-file-layout.html" title="69.1. Database File Layout">Section 69.1</a> for more information). + For most relations the result is the same as + <code class="structname">pg_class</code>.<code class="structfield">relfilenode</code>, + but for certain system catalogs <code class="structfield">relfilenode</code> + is zero and this function must be used to get the correct value. The + function returns NULL if passed a relation that does not have storage, + such as a view. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.6.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">pg_relation_filepath</code> ( <em class="parameter"><code>relation</code></em> <code class="type">regclass</code> ) + → <code class="returnvalue">text</code> + </p> + <p> + Returns the entire file path name (relative to the database cluster's + data directory, <code class="varname">PGDATA</code>) of the relation. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.6.2.2.3.1.1.1" class="indexterm"></a> + <code class="function">pg_filenode_relation</code> ( <em class="parameter"><code>tablespace</code></em> <code class="type">oid</code>, <em class="parameter"><code>filenode</code></em> <code class="type">oid</code> ) + → <code class="returnvalue">regclass</code> + </p> + <p> + Returns a relation's OID given the tablespace OID and filenode it is + stored under. This is essentially the inverse mapping of + <code class="function">pg_relation_filepath</code>. For a relation in the + database's default tablespace, the tablespace can be specified as zero. + Returns <code class="literal">NULL</code> if no relation in the current database + is associated with the given values. + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-COLLATION" title="Table 9.92. Collation Management Functions">Table 9.92</a> lists functions used to manage + collations. + </p><div class="table" id="FUNCTIONS-ADMIN-COLLATION"><p class="title"><strong>Table 9.92. Collation Management Functions</strong></p><div class="table-contents"><table class="table" summary="Collation Management Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.8.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">pg_collation_actual_version</code> ( <code class="type">oid</code> ) + → <code class="returnvalue">text</code> + </p> + <p> + Returns the actual version of the collation object as it is currently + installed in the operating system. If this is different from the + value in + <code class="structname">pg_collation</code>.<code class="structfield">collversion</code>, + then objects depending on the collation might need to be rebuilt. See + also <a class="xref" href="sql-altercollation.html" title="ALTER COLLATION"><span class="refentrytitle">ALTER COLLATION</span></a>. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.8.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">pg_import_system_collations</code> ( <em class="parameter"><code>schema</code></em> <code class="type">regnamespace</code> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Adds collations to the system + catalog <code class="structname">pg_collation</code> based on all the locales + it finds in the operating system. This is + what <code class="command">initdb</code> uses; see + <a class="xref" href="collation.html#COLLATION-MANAGING" title="23.2.2. Managing Collations">Section 23.2.2</a> for more details. If additional + locales are installed into the operating system later on, this + function can be run again to add collations for the new locales. + Locales that match existing entries + in <code class="structname">pg_collation</code> will be skipped. (But + collation objects based on locales that are no longer present in the + operating system are not removed by this function.) + The <em class="parameter"><code>schema</code></em> parameter would typically + be <code class="literal">pg_catalog</code>, but that is not a requirement; the + collations could be installed into some other schema as well. The + function returns the number of new collation objects it created. + Use of this function is restricted to superusers. + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + <a class="xref" href="functions-admin.html#FUNCTIONS-INFO-PARTITION" title="Table 9.93. Partitioning Information Functions">Table 9.93</a> lists functions that provide + information about the structure of partitioned tables. + </p><div class="table" id="FUNCTIONS-INFO-PARTITION"><p class="title"><strong>Table 9.93. Partitioning Information Functions</strong></p><div class="table-contents"><table class="table" summary="Partitioning Information Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.10.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">pg_partition_tree</code> ( <code class="type">regclass</code> ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>relid</code></em> <code class="type">regclass</code>, + <em class="parameter"><code>parentrelid</code></em> <code class="type">regclass</code>, + <em class="parameter"><code>isleaf</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>level</code></em> <code class="type">integer</code> ) + </p> + <p> + Lists the tables or indexes in the partition tree of the + given partitioned table or partitioned index, with one row for each + partition. Information provided includes the OID of the partition, + the OID of its immediate parent, a boolean value telling if the + partition is a leaf, and an integer telling its level in the hierarchy. + The level value is 0 for the input table or index, 1 for its + immediate child partitions, 2 for their partitions, and so on. + Returns no rows if the relation does not exist or is not a partition + or partitioned table. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.10.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">pg_partition_ancestors</code> ( <code class="type">regclass</code> ) + → <code class="returnvalue">setof regclass</code> + </p> + <p> + 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. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.9.10.2.2.3.1.1.1" class="indexterm"></a> + <code class="function">pg_partition_root</code> ( <code class="type">regclass</code> ) + → <code class="returnvalue">regclass</code> + </p> + <p> + Returns the top-most parent of the partition tree to which the given + relation belongs. Returns <code class="literal">NULL</code> if the relation + does not exist or is not a partition or partitioned table. + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + For example, to check the total size of the data contained in a + partitioned table <code class="structname">measurement</code>, one could use the + following query: +</p><pre class="programlisting"> +SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size + FROM pg_partition_tree('measurement'); +</pre><p> + </p></div><div class="sect2" id="FUNCTIONS-ADMIN-INDEX"><div class="titlepage"><div><div><h3 class="title">9.27.8. Index Maintenance Functions</h3></div></div></div><p> + <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-INDEX-TABLE" title="Table 9.94. Index Maintenance Functions">Table 9.94</a> shows the functions + available for index maintenance tasks. (Note that these maintenance + tasks are normally done automatically by autovacuum; use of these + functions is only required in special cases.) + These functions cannot be executed during recovery. + Use of these functions is restricted to superusers and the owner + of the given index. + </p><div class="table" id="FUNCTIONS-ADMIN-INDEX-TABLE"><p class="title"><strong>Table 9.94. Index Maintenance Functions</strong></p><div class="table-contents"><table class="table" summary="Index Maintenance Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.10.3.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">brin_summarize_new_values</code> ( <em class="parameter"><code>index</code></em> <code class="type">regclass</code> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Scans the specified BRIN index to find page ranges in the base table + that are not currently summarized by the index; for any such range it + creates a new summary index tuple by scanning those table pages. + Returns the number of new page range summaries that were inserted + into the index. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.10.3.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">brin_summarize_range</code> ( <em class="parameter"><code>index</code></em> <code class="type">regclass</code>, <em class="parameter"><code>blockNumber</code></em> <code class="type">bigint</code> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Summarizes the page range covering the given block, if not already + summarized. This is + like <code class="function">brin_summarize_new_values</code> except that it + only processes the page range that covers the given table block number. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.10.3.2.2.3.1.1.1" class="indexterm"></a> + <code class="function">brin_desummarize_range</code> ( <em class="parameter"><code>index</code></em> <code class="type">regclass</code>, <em class="parameter"><code>blockNumber</code></em> <code class="type">bigint</code> ) + → <code class="returnvalue">void</code> + </p> + <p> + Removes the BRIN index tuple that summarizes the page range covering + the given table block, if there is one. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.10.3.2.2.4.1.1.1" class="indexterm"></a> + <code class="function">gin_clean_pending_list</code> ( <em class="parameter"><code>index</code></em> <code class="type">regclass</code> ) + → <code class="returnvalue">bigint</code> + </p> + <p> + Cleans up the <span class="quote">“<span class="quote">pending</span>”</span> list of the specified GIN index + by moving entries in it, in bulk, to the main GIN data structure. + Returns the number of pages removed from the pending list. + If the argument is a GIN index built with + the <code class="literal">fastupdate</code> option disabled, no cleanup happens + and the result is zero, because the index doesn't have a pending list. + See <a class="xref" href="gin-implementation.html#GIN-FAST-UPDATE" title="66.4.1. GIN Fast Update Technique">Section 66.4.1</a> and <a class="xref" href="gin-tips.html" title="66.5. GIN Tips and Tricks">Section 66.5</a> + for details about the pending list and <code class="literal">fastupdate</code> + option. + </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-ADMIN-GENFILE"><div class="titlepage"><div><div><h3 class="title">9.27.9. Generic File Access Functions</h3></div></div></div><p> + The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-GENFILE-TABLE" title="Table 9.95. Generic File Access Functions">Table 9.95</a> provide native access to + files on the machine hosting the server. Only files within the + database cluster directory and the <code class="varname">log_directory</code> can be + accessed, unless the user is a superuser or is granted the role + <code class="literal">pg_read_server_files</code>. Use a relative path for files in + the cluster directory, and a path matching the <code class="varname">log_directory</code> + configuration setting for log files. + </p><p> + Note that granting users the EXECUTE privilege on + <code class="function">pg_read_file()</code>, or related functions, allows them the + ability to read any file on the server that the database server process can + read; these functions bypass all in-database privilege checks. This means + that, for example, a user with such access is able to read the contents of + the <code class="structname">pg_authid</code> table where authentication + information is stored, as well as read any table data in the database. + Therefore, granting access to these functions should be carefully + considered. + </p><p> + Some of these functions take an optional <em class="parameter"><code>missing_ok</code></em> + parameter, which specifies the behavior when the file or directory does + not exist. If <code class="literal">true</code>, the function + returns <code class="literal">NULL</code> or an empty result set, as appropriate. + If <code class="literal">false</code>, an error is raised. The default + is <code class="literal">false</code>. + </p><div class="table" id="FUNCTIONS-ADMIN-GENFILE-TABLE"><p class="title"><strong>Table 9.95. Generic File Access Functions</strong></p><div class="table-contents"><table class="table" summary="Generic File Access Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.11.5.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">pg_ls_dir</code> ( <em class="parameter"><code>dirname</code></em> <code class="type">text</code> [<span class="optional">, <em class="parameter"><code>missing_ok</code></em> <code class="type">boolean</code>, <em class="parameter"><code>include_dot_dirs</code></em> <code class="type">boolean</code> </span>] ) + → <code class="returnvalue">setof text</code> + </p> + <p> + Returns the names of all files (and directories and other special + files) in the specified + directory. The <em class="parameter"><code>include_dot_dirs</code></em> parameter + indicates whether <span class="quote">“<span class="quote">.</span>”</span> and <span class="quote">“<span class="quote">..</span>”</span> are to be + included in the result set; the default is to exclude them. Including + them can be useful when <em class="parameter"><code>missing_ok</code></em> + is <code class="literal">true</code>, to distinguish an empty directory from a + non-existent directory. + </p> + <p> + This function is restricted to superusers by default, but other users + can be granted EXECUTE to run the function. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.11.5.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">pg_ls_logdir</code> () + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>name</code></em> <code class="type">text</code>, + <em class="parameter"><code>size</code></em> <code class="type">bigint</code>, + <em class="parameter"><code>modification</code></em> <code class="type">timestamp with time zone</code> ) + </p> + <p> + Returns the name, size, and last modification time (mtime) of each + ordinary file in the server's log directory. Filenames beginning with + a dot, directories, and other special files are excluded. + </p> + <p> + This function is restricted to superusers and members of + the <code class="literal">pg_monitor</code> role by default, but other users can + be granted EXECUTE to run the function. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.11.5.2.2.3.1.1.1" class="indexterm"></a> + <code class="function">pg_ls_waldir</code> () + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>name</code></em> <code class="type">text</code>, + <em class="parameter"><code>size</code></em> <code class="type">bigint</code>, + <em class="parameter"><code>modification</code></em> <code class="type">timestamp with time zone</code> ) + </p> + <p> + Returns the name, size, and last modification time (mtime) of each + ordinary file in the server's write-ahead log (WAL) directory. + Filenames beginning with a dot, directories, and other special files + are excluded. + </p> + <p> + This function is restricted to superusers and members of + the <code class="literal">pg_monitor</code> role by default, but other users can + be granted EXECUTE to run the function. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.11.5.2.2.4.1.1.1" class="indexterm"></a> + <code class="function">pg_ls_archive_statusdir</code> () + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>name</code></em> <code class="type">text</code>, + <em class="parameter"><code>size</code></em> <code class="type">bigint</code>, + <em class="parameter"><code>modification</code></em> <code class="type">timestamp with time zone</code> ) + </p> + <p> + Returns the name, size, and last modification time (mtime) of each + ordinary file in the server's WAL archive status directory + (<code class="filename">pg_wal/archive_status</code>). Filenames beginning + with a dot, directories, and other special files are excluded. + </p> + <p> + This function is restricted to superusers and members of + the <code class="literal">pg_monitor</code> role by default, but other users can + be granted EXECUTE to run the function. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + + <a id="id-1.5.8.33.11.5.2.2.5.1.1.1" class="indexterm"></a> + <code class="function">pg_ls_tmpdir</code> ( [<span class="optional"> <em class="parameter"><code>tablespace</code></em> <code class="type">oid</code> </span>] ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>name</code></em> <code class="type">text</code>, + <em class="parameter"><code>size</code></em> <code class="type">bigint</code>, + <em class="parameter"><code>modification</code></em> <code class="type">timestamp with time zone</code> ) + </p> + <p> + Returns the name, size, and last modification time (mtime) of each + ordinary file in the temporary file directory for the + specified <em class="parameter"><code>tablespace</code></em>. + If <em class="parameter"><code>tablespace</code></em> is not provided, + the <code class="literal">pg_default</code> tablespace is examined. Filenames + beginning with a dot, directories, and other special files are + excluded. + </p> + <p> + This function is restricted to superusers and members of + the <code class="literal">pg_monitor</code> role by default, but other users can + be granted EXECUTE to run the function. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.11.5.2.2.6.1.1.1" class="indexterm"></a> + <code class="function">pg_read_file</code> ( <em class="parameter"><code>filename</code></em> <code class="type">text</code> [<span class="optional">, <em class="parameter"><code>offset</code></em> <code class="type">bigint</code>, <em class="parameter"><code>length</code></em> <code class="type">bigint</code> [<span class="optional">, <em class="parameter"><code>missing_ok</code></em> <code class="type">boolean</code> </span>]</span>] ) + → <code class="returnvalue">text</code> + </p> + <p> + Returns all or part of a text file, starting at the + given byte <em class="parameter"><code>offset</code></em>, returning at + most <em class="parameter"><code>length</code></em> bytes (less if the end of file is + reached first). If <em class="parameter"><code>offset</code></em> is negative, it is + relative to the end of the file. If <em class="parameter"><code>offset</code></em> + and <em class="parameter"><code>length</code></em> are omitted, the entire file is + returned. The bytes read from the file are interpreted as a string in + the database's encoding; an error is thrown if they are not valid in + that encoding. + </p> + <p> + This function is restricted to superusers by default, but other users + can be granted EXECUTE to run the function. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.11.5.2.2.7.1.1.1" class="indexterm"></a> + <code class="function">pg_read_binary_file</code> ( <em class="parameter"><code>filename</code></em> <code class="type">text</code> [<span class="optional">, <em class="parameter"><code>offset</code></em> <code class="type">bigint</code>, <em class="parameter"><code>length</code></em> <code class="type">bigint</code> [<span class="optional">, <em class="parameter"><code>missing_ok</code></em> <code class="type">boolean</code> </span>]</span>] ) + → <code class="returnvalue">bytea</code> + </p> + <p> + Returns all or part of a file. This function is identical to + <code class="function">pg_read_file</code> except that it can read arbitrary + binary data, returning the result as <code class="type">bytea</code> + not <code class="type">text</code>; accordingly, no encoding checks are performed. + </p> + <p> + This function is restricted to superusers by default, but other users + can be granted EXECUTE to run the function. + </p> + <p> + In combination with the <code class="function">convert_from</code> function, + this function can be used to read a text file in a specified encoding + and convert to the database's encoding: +</p><pre class="programlisting"> +SELECT convert_from(pg_read_binary_file('file_in_utf8.txt'), 'UTF8'); +</pre><p> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.11.5.2.2.8.1.1.1" class="indexterm"></a> + <code class="function">pg_stat_file</code> ( <em class="parameter"><code>filename</code></em> <code class="type">text</code> [<span class="optional">, <em class="parameter"><code>missing_ok</code></em> <code class="type">boolean</code> </span>] ) + → <code class="returnvalue">record</code> + ( <em class="parameter"><code>size</code></em> <code class="type">bigint</code>, + <em class="parameter"><code>access</code></em> <code class="type">timestamp with time zone</code>, + <em class="parameter"><code>modification</code></em> <code class="type">timestamp with time zone</code>, + <em class="parameter"><code>change</code></em> <code class="type">timestamp with time zone</code>, + <em class="parameter"><code>creation</code></em> <code class="type">timestamp with time zone</code>, + <em class="parameter"><code>isdir</code></em> <code class="type">boolean</code> ) + </p> + <p> + Returns a record containing the file's size, last access time stamp, + last modification time stamp, last file status change time stamp (Unix + platforms only), file creation time stamp (Windows only), and a flag + indicating if it is a directory. + </p> + <p> + This function is restricted to superusers by default, but other users + can be granted EXECUTE to run the function. + </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-ADVISORY-LOCKS"><div class="titlepage"><div><div><h3 class="title">9.27.10. Advisory Lock Functions</h3></div></div></div><p> + The functions shown in <a class="xref" href="functions-admin.html#FUNCTIONS-ADVISORY-LOCKS-TABLE" title="Table 9.96. Advisory Lock Functions">Table 9.96</a> + manage advisory locks. For details about proper use of these functions, + see <a class="xref" href="explicit-locking.html#ADVISORY-LOCKS" title="13.3.5. Advisory Locks">Section 13.3.5</a>. + </p><p> + All these functions are intended to be used to lock application-defined + resources, which can be identified either by a single 64-bit key value or + two 32-bit key values (note that these two key spaces do not overlap). + If another session already holds a conflicting lock on the same resource + identifier, the functions will either wait until the resource becomes + available, or return a <code class="literal">false</code> result, as appropriate for + the function. + Locks can be either shared or exclusive: a shared lock does not conflict + with other shared locks on the same resource, only with exclusive locks. + Locks can be taken at session level (so that they are held until released + or the session ends) or at transaction level (so that they are held until + the current transaction ends; there is no provision for manual release). + Multiple session-level lock requests stack, so that if the same resource + identifier is locked three times there must then be three unlock requests + to release the resource in advance of session end. + </p><div class="table" id="FUNCTIONS-ADVISORY-LOCKS-TABLE"><p class="title"><strong>Table 9.96. Advisory Lock Functions</strong></p><div class="table-contents"><table class="table" summary="Advisory Lock Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.12.4.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">pg_advisory_lock</code> ( <em class="parameter"><code>key</code></em> <code class="type">bigint</code> ) + → <code class="returnvalue">void</code> + </p> + <p class="func_signature"> + <code class="function">pg_advisory_lock</code> ( <em class="parameter"><code>key1</code></em> <code class="type">integer</code>, <em class="parameter"><code>key2</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">void</code> + </p> + <p> + Obtains an exclusive session-level advisory lock, waiting if necessary. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.12.4.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">pg_advisory_lock_shared</code> ( <em class="parameter"><code>key</code></em> <code class="type">bigint</code> ) + → <code class="returnvalue">void</code> + </p> + <p class="func_signature"> + <code class="function">pg_advisory_lock_shared</code> ( <em class="parameter"><code>key1</code></em> <code class="type">integer</code>, <em class="parameter"><code>key2</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">void</code> + </p> + <p> + Obtains a shared session-level advisory lock, waiting if necessary. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.12.4.2.2.3.1.1.1" class="indexterm"></a> + <code class="function">pg_advisory_unlock</code> ( <em class="parameter"><code>key</code></em> <code class="type">bigint</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p class="func_signature"> + <code class="function">pg_advisory_unlock</code> ( <em class="parameter"><code>key1</code></em> <code class="type">integer</code>, <em class="parameter"><code>key2</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p> + Releases a previously-acquired exclusive session-level advisory lock. + Returns <code class="literal">true</code> if the lock is successfully released. + If the lock was not held, <code class="literal">false</code> is returned, and in + addition, an SQL warning will be reported by the server. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.12.4.2.2.4.1.1.1" class="indexterm"></a> + <code class="function">pg_advisory_unlock_all</code> () + → <code class="returnvalue">void</code> + </p> + <p> + Releases all session-level advisory locks held by the current session. + (This function is implicitly invoked at session end, even if the + client disconnects ungracefully.) + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.12.4.2.2.5.1.1.1" class="indexterm"></a> + <code class="function">pg_advisory_unlock_shared</code> ( <em class="parameter"><code>key</code></em> <code class="type">bigint</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p class="func_signature"> + <code class="function">pg_advisory_unlock_shared</code> ( <em class="parameter"><code>key1</code></em> <code class="type">integer</code>, <em class="parameter"><code>key2</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p> + Releases a previously-acquired shared session-level advisory lock. + Returns <code class="literal">true</code> if the lock is successfully released. + If the lock was not held, <code class="literal">false</code> is returned, and in + addition, an SQL warning will be reported by the server. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.12.4.2.2.6.1.1.1" class="indexterm"></a> + <code class="function">pg_advisory_xact_lock</code> ( <em class="parameter"><code>key</code></em> <code class="type">bigint</code> ) + → <code class="returnvalue">void</code> + </p> + <p class="func_signature"> + <code class="function">pg_advisory_xact_lock</code> ( <em class="parameter"><code>key1</code></em> <code class="type">integer</code>, <em class="parameter"><code>key2</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">void</code> + </p> + <p> + Obtains an exclusive transaction-level advisory lock, waiting if + necessary. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.12.4.2.2.7.1.1.1" class="indexterm"></a> + <code class="function">pg_advisory_xact_lock_shared</code> ( <em class="parameter"><code>key</code></em> <code class="type">bigint</code> ) + → <code class="returnvalue">void</code> + </p> + <p class="func_signature"> + <code class="function">pg_advisory_xact_lock_shared</code> ( <em class="parameter"><code>key1</code></em> <code class="type">integer</code>, <em class="parameter"><code>key2</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">void</code> + </p> + <p> + Obtains a shared transaction-level advisory lock, waiting if + necessary. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.12.4.2.2.8.1.1.1" class="indexterm"></a> + <code class="function">pg_try_advisory_lock</code> ( <em class="parameter"><code>key</code></em> <code class="type">bigint</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p class="func_signature"> + <code class="function">pg_try_advisory_lock</code> ( <em class="parameter"><code>key1</code></em> <code class="type">integer</code>, <em class="parameter"><code>key2</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p> + Obtains an exclusive session-level advisory lock if available. + This will either obtain the lock immediately and + return <code class="literal">true</code>, or return <code class="literal">false</code> + without waiting if the lock cannot be acquired immediately. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.12.4.2.2.9.1.1.1" class="indexterm"></a> + <code class="function">pg_try_advisory_lock_shared</code> ( <em class="parameter"><code>key</code></em> <code class="type">bigint</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p class="func_signature"> + <code class="function">pg_try_advisory_lock_shared</code> ( <em class="parameter"><code>key1</code></em> <code class="type">integer</code>, <em class="parameter"><code>key2</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p> + Obtains a shared session-level advisory lock if available. + This will either obtain the lock immediately and + return <code class="literal">true</code>, or return <code class="literal">false</code> + without waiting if the lock cannot be acquired immediately. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.12.4.2.2.10.1.1.1" class="indexterm"></a> + <code class="function">pg_try_advisory_xact_lock</code> ( <em class="parameter"><code>key</code></em> <code class="type">bigint</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p class="func_signature"> + <code class="function">pg_try_advisory_xact_lock</code> ( <em class="parameter"><code>key1</code></em> <code class="type">integer</code>, <em class="parameter"><code>key2</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p> + Obtains an exclusive transaction-level advisory lock if available. + This will either obtain the lock immediately and + return <code class="literal">true</code>, or return <code class="literal">false</code> + without waiting if the lock cannot be acquired immediately. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.33.12.4.2.2.11.1.1.1" class="indexterm"></a> + <code class="function">pg_try_advisory_xact_lock_shared</code> ( <em class="parameter"><code>key</code></em> <code class="type">bigint</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p class="func_signature"> + <code class="function">pg_try_advisory_xact_lock_shared</code> ( <em class="parameter"><code>key1</code></em> <code class="type">integer</code>, <em class="parameter"><code>key2</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">boolean</code> + </p> + <p> + Obtains a shared transaction-level advisory lock if available. + This will either obtain the lock immediately and + return <code class="literal">true</code>, or return <code class="literal">false</code> + without waiting if the lock cannot be acquired immediately. + </p></td></tr></tbody></table></div></div><br class="table-break" /></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-info.html" title="9.26. System Information Functions and Operators">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-trigger.html" title="9.28. Trigger Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.26. System Information Functions and Operators </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.28. Trigger Functions</td></tr></table></div></body></html>
\ No newline at end of file |